Preventing escaping apostrophes with parameter query not working

Question

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.

score 2 · Accepted Answer · edited May 23 '17 at 10:24

2

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()

edited May 23 '17 at 10:24

Community

1
1

answered Nov 22 '11 at 21:05

LarsTech

80,625
14
153
225

ITs working now except for the 10 records or so with apostrphes – htm11h Nov 22 '11 at 21:16
@marc11h Can you define not working for the 10 records with apostrophes? Errors? – LarsTech Nov 22 '11 at 21:21
Yes given the number can be much larger, this is test data. – htm11h Nov 22 '11 at 21:28
I think I got it. The issue is that the record is not getting inserted. No reported error. – htm11h Nov 22 '11 at 21:29
@marc11h Maybe edit your question by adding the new code you are using now. It's pretty straight forward how this works, so I'm guessing you still have a syntax error. – LarsTech Nov 22 '11 at 21:35
NOPE, the records with apostrophes are still not getting INSERTED. – htm11h Nov 22 '11 at 21:36
@marc11h Updated answer. You should be using `ExecuteNonQuery`. `ExecuteScalar` is for return one column, one record "Select" statements. – LarsTech Nov 22 '11 at 21:50

Michał Powaga · Answer 2 · 2011-11-22T21:52:14.580

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".

Preventing escaping apostrophes with parameter query not working

2 Answers2