Why isn't the Parameterized INSERT query working?

[MRM256]

Hi Everyone,

I took an entire JSON table and modified it to return only eight values; the IP Address, City, Region, Country Name, In_EU, Latitude, Longitude, and Org. Using two separate functions.

My problem is I can’t get my VB.NET function to store this to the application’s local database. I have tried two different approaches:

Approach 1:

Public Function Insert_JSON_for_Attacker(ByVal StrCnn As String,
ByRef DT_Attacker As DataTable) _
As Boolean
'Purpose: Stores Attacker JSON data into Database
'Parameters: strCnn As String, DT_Attacker As DataTable
'Returns: True if Insert successful; False otherwise
Dim strSQL As String,
strAttackerID As String,
lngCountryID As Long,
lngRegionID As Long,
strCity As String,
bInEu As Boolean,
sngLat As Single,
sngLong As Single,
strOrg As String,
cmd As SqlCommand

strAttackerID = DT_Attacker.Rows(0).Item("AttackerID").ToString
lngCountryID = CLng(DT_Attacker.Rows(0).Item("CountryID").ToString)
lngRegionID = CLng(DT_Attacker.Rows(0).Item("RegionID").ToString)
strCity = DT_Attacker.Rows(0).Item("City").ToString
bInEu = CBool(DT_Attacker.Rows(0).Item("In_EU").ToString)
sngLat = CSng(DT_Attacker.Rows(0).Item("Latitude").ToString)
sngLong = CSng(DT_Attacker.Rows(0).Item("Longitude").ToString)
strOrg = DT_Attacker.Rows(0).Item("Org_Name").ToString

Using cnn As New SqlConnection(StrCnn)
Try
'Get all Information from
'selected Server's Database table
strSQL = "INSERT INTO [tblAtackers] ([AttackerID], " &
"[CountryID], [RegionID], [City], [In_EU], " &
"[Latitude], [Longitude], [Org_Name]) " &
"VALUES (" & strAttackerID & ", " &
lngCountryID & ", " & lngRegionID & ", " &
strCity & ", " & bInEu & ", " &
sngLat & ", " & ", " & strOrg & ");"
cnn.Open()
cmd = New SqlCommand(strSQL, cnn)
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
Catch ex As Exception
'Log error
Dim el As New Log.ErrorLogger
el.WriteToErrorLog(ex.Message, ex.StackTrace, "Error")
Return False
End Try
End Using
Return True
End Function


When I tried to insert data this way I get the error: Incorrect syntax near '.103'. This tells me exactly squat. What is it? How do I correct it? There is no explanation for either.

Approach 2:

Public Function Insert_JSON_for_Attacker(ByVal StrCnn As String,
ByRef DT_Attacker As DataTable) _
As Boolean
'Purpose: Stores Attacker JSON data into Database
'Parameters: strCnn As String, DT_Attacker As DataTable
'Returns: True if Insert successful; False otherwise
Dim strSQL As String
Using cnn As New SqlConnection(StrCnn)
Try
strSQL = "INSERT INTO [tblAttackers] ([AttackerID], " &
"[CountryID], [RegionID], [City], [In_EU], " &
"[Latitude], [Longitude], [Org_Name]) " &
"VALUES (@AttackerID, " &
"@CountryID, @RegionID, @City, @In_Eu, " &
"@Latitude, @Longitude, @Org_Name);"
cnn.Open()
Dim cmd As SqlCommand = New SqlCommand(strSQL, cnn)
With cmd.Parameters
.AddWithValue("@AttackerID",
DT_Attacker.Rows(0).Item("AttackerID"))
.AddWithValue("@CountryID",
DT_Attacker.Rows(0).Item("CountryID"))
.AddWithValue("@RegionID",
DT_Attacker.Rows(0).Item("RegionID"))
.AddWithValue("@City",
DT_Attacker.Rows(0).Item("City"))
.AddWithValue("@In_EU",
DT_Attacker.Rows(0).Item("In_EU"))
.AddWithValue("@Latitude",
DT_Attacker.Rows(0).Item("Latitude"))
.AddWithValue("@Longitude",
DT_Attacker.Rows(0).Item("Longitude"))
.AddWithValue("@Org_Name",
DT_Attacker.Rows(0).Item("Org_Name"))
End With
cmd.ExecuteNonQuery()
cnn.Close()

Catch ex As Exception
'Log error
Dim el As New Log.ErrorLogger
el.WriteToErrorLog(ex.Message, ex.StackTrace,
"Error in Insert_JSON_for_Attacker")
Return False
End Try
End Using
Return True
End Function


This function uses Parameters in place of the variables I used in Approach 1. It does not throw any exceptions, but it doesn’t write any data into the database table either.

Here is my initial WAG(Wild Ass Guess) on how the parameterized version works.

We have the base SQL statement: INSERT INTO [tblAttackers] ([AttackerID], [CountryID], [RegionID], [City], [In_EU], [Latitude], [Longitude], [Org_Name]) VALUES (@AttackerID, @CountryID, @RegionID, @City, @In_Eu, @Latitude, @Longitude, @Org_Name);"

My guess is the cmd.Parameters method modifies the initial SQL into:


INSERT INTO [tblAttackers] ([AttackerID], [CountryID], [RegionID], [City], [In_EU], [Latitude], [Longitude], [Org_Name]) VALUES ’81.159.103.100’, 230, 3889, ‘Fraserburgh’, 1, 57.657, -2.0448, ‘British Telecommunications PLC’);"


When I look at the table data I just see:


One short coming I can see is there is no way to see what the cmd.Parameters method did to your base query before it was executed.

As you can see there is no information underneath the column names, because the INSERT failed.

Why?

Thanks,

One short coming I can see is there is no way to see what the cmd.Parameters method did to your base query before it was executed.

You can see there is no information underneath the column names, because the INSERT failed.



Why?

Thanks,








MRM256

Continue reading...