insert value error!!!

[hounetdev]

Hi, I am trying to insert a value into a access database field but the value i am tyring to insert has an ' in it and i think that is what is causing the problem. for example dataadapter.insertcommand.commandtext = "insert into table1 (clientname) values " & txtclient.Text & "" I think that if the user enters something with a ' in it i get an error. Can any one please let me know if i am right and how to fix it? Thank you, Santana

[eramgarden]

you need to replace the single quote with 2 single quotes. insert into table1 (clientname) values ('o''neil')

[hounetdev]

What if i am getting the data from a textbox? Thank you, Santana

[Charlie Williams]

Use parameters so you don't have to worry about escaping your input. It's also safer than concatenating SQL statements. Charlie if(!curlies){ return; }

[Guerven]

do this first textbox1.text = replace(textbox1.text,"'","''") or replace(textbox1.text,"'","''") Marvin N. Guerrero - Casting More!!

[Dr_X]

Here you go. Just use these prior to saving or retrieving any text field.

  Friend Function SingleQuoteTextFromDB(ByVal text As Object) As String
    'If a user saved a single quote in a text field, it was changed to
    '2 single quotes. This function reverses the changes when displayed
    If Not text Is Nothing Then
      If Not text Is DBNull.Value Then
        If InStr(text, "''") Then
          Return Replace(text, "''", "'")
        End If
      End If
    End If
    Return text
  End Function

  Friend Function SingleQuoteTextToDB(ByVal text As String) As String
    'If a user enters a single quote in a text field, 
    'This will replace the single quote with 2 single quotes
    If Not text Is Nothing Then
      If Not text.Length.Equals(0) Then
        If InStr(text, "'") Then
          Return Replace(text, "'", "''")
        End If
      End If
    End If
    Return text
  End Function

Michael I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious. Vince Lombardi (1913-1970)

[Anonymous]

The values aren't actually stored with the second quote, therefore values don't need to be decoded after being retrieved from the db.

[hounetdev]

Thanks for all the help Guys, i used the replace method that Guerven suggested. Santana

[Steven Campbell]

When you're done, please let me know the URL to your website, so that I can practise SQL injection attacks and delete your database. Thanks in advance, Steve :sigh: