problem with quotes in SQL statement

[ashishprem]

Hi
I need to insert a string in a database but the string might or might not contain a quotes('). There is no problem if there is no quotes. But in case of quotes it throws a syntax error in insert statement and that obvious also. What i am thinking is to do some formatting all the time for all the strings before inserting do the insertion. Is there anyother to achieve this.. Thanks for the suggestions.

Ashish

[debasisdas]

if you are inserting value from VB , you can restrict the entry of single quites by restricting chr(39) in the key press event.
But if you dont want to do that replace ' by ''
i.e. single quote by 2 single quites .
Then there won't be any problem.

[hkdani]

Is there anyother to achieve this

You can remove the quotes using if statements before you insert the string into the SEQUEL statement:

Dim strInsert as String
Dim intLength as integer

if Mid(strInsert, 1,1) = "'" then
  strInsert = right(strInsert, len(strInsert) -1)
end if

intLength = len(strInsert)

if Mid(strInsert, intLength, 1) = "'" then
  strInsert = Left(strInsert, intLength -1)
Endif

Hank

[SCBWV]

See if this helps: http://support.microsoft.com/default.aspx/kb/178070

[duncanj]

I had a similar problem...I wanted to leave any single quotes or double quotes as they were when read in from my datasource but when trying to do this it caused problems with my SQL string in my VB code.
This is how I solved it: If my datasource involved quotes I used the string function Replace to make them two single quotes in my SQL statement ie:

strQuery = "update files set files." & sField & " = '" & Replace(strOut, "'", "''") & "' where whatever=" & adoRS("whatever")

[jireh]

I agree with you duncanj that problem is very common when it comes to queries, most programmer used that function (Replace())