CODEWITHSUNDEEP
sqlvb.net
htm11h
htm11h

Reputation: 1779

Preventing escaping apostrophes with parameter query not working

I am trying to prevent from having to escape apostrophes in my string variables by using a parameterized query with a SqlConnection, but it is not working. any help would be appreciated.

UPDATED: this is current code...

 'Populate Connection Object
 Dim oCnn As New SqlConnection(strConnection)

 'Define our sql query
 Dim sSQL As String = "INSERT INTO [" & foreignTable & "] (data_text) VALUES (@data_text) ; "

 'Populate Command Object
  Dim oCmd As New SqlCommand(sSQL, oCnn)

  'Add up the parameter, associated it with its value

   oCmd.Parameters.AddWithValue("@data_text", data_text)

  'Opening Connection for our DB operation  
  oCnn.Open()

  Try
      Dim results As Integer = oCmd.ExecuteScalar
  Catch ex As Exception
      LabelImport.Text &= "<font color=red>ROOT Import ERROR: " & ex.ToString & ", From Database: " & dbName & ", Text String: " & data_text & "</font><br />"
      Throw
  End Try

  oCnn.Close()
  oCmd.Parameters.Clear()

Thanks for any help.

Upvotes: 0

Views: 952

Answers (2)

Michał Powaga
Michał Powaga

Reputation: 23183

You can use table name only when creating query (I mean concatenating it from parts: "INSERT INTO " + foreignTable + " (data_text) VALUES..., AFAIK), not as query parameter. Check SqlParameterCollection.AddWithValue on MSDN for more information about SqlCommand parameters, there is very good example as well.

'Populate Connection Object 
Dim oCnn As New SqlConnection(strConnection) 

'Define our sql query 
Dim sSQL As String = "INSERT INTO " & foreignTable & " (data_text) VALUES (@data_text);" 

'Populate Command Object 
Dim oCmd As New SqlCommand(sSQL, oCnn) 

'Add up the parameter, associated it with its value 
oCmd.Parameters.AddWithValue("@data_text", data_text) 

'Opening Connection for our DB operation   
oCnn.Open()

Edit:

+ changed to & because of C# as "native language".

Upvotes: 2

LarsTech
LarsTech

Reputation: 81620

Yeah, that's not right.

It should look like this:

Dim sSQL As String = "INSERT INTO [" & foreignTable & "] (data_text) VALUES (@data_text);"

and for the parameter:

oCmd.Parameters.AddWithValue("@data_text", data_text)

Note: I don't "think" you can pass the table name as a parameter. You would have to have the table name in the string. See Parametise table name in .Net/SQL?

Also, change this:

Dim results As Integer = oCmd.ExecuteScalar

to

Dim results as Integer = oCmd.ExecuteNonQuery()

Upvotes: 2

Related Questions