problem with quotes in SQL statement
 

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

Re: problem with quotes in SQL statement
 

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.

Re: problem with quotes in SQL statement
 

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

Re: problem with quotes in SQL statement
 

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

Re: problem with quotes in SQL statement
 

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

Re: problem with quotes in SQL statement
 

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