DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

problem with quotes in SQL statement

Please support our Visual Basic 4 / 5 / 6 advertiser: Programming Forums - DaniWeb Sister Site

•

Join Date: Dec 2007

Posts: 7

Reputation: ashishprem is an unknown quantity at this point

Solved Threads: 0

ashishprem ashishprem is offline

Offline

Newbie Poster

problem with quotes in SQL statement

Jan 10th, 2008

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

•

Join Date: Feb 2007

Posts: 2,132

Reputation: debasisdas will become famous soon enough

Solved Threads: 131

debasisdas debasisdas is offline

Offline

Postaholic

Re: problem with quotes in SQL statement

Jan 10th, 2008

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.

Share your Knowledge.

•

Join Date: Nov 2007

Posts: 218

Reputation: hkdani is an unknown quantity at this point

Solved Threads: 24

hkdani

Offline

Posting Whiz in Training

Re: problem with quotes in SQL statement

Jan 10th, 2008

•

Is there anyother to achieve this

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

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
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
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

•

Join Date: Apr 2007

Posts: 106

Reputation: SCBWV is an unknown quantity at this point

Solved Threads: 16

SCBWV

Offline

Junior Poster

Re: problem with quotes in SQL statement

Jan 10th, 2008

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

•

Join Date: Sep 2007

Posts: 1

Reputation: duncanj is an unknown quantity at this point

Solved Threads: 0

duncanj

Offline

Newbie Poster

Re: problem with quotes in SQL statement

Jan 20th, 2008

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:

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
strQuery = "update files set files." & sField & " = '" & Replace(strOut, "'", "''") & "' where whatever=" & adoRS("whatever")
strQuery = "update files set files." & sField & " = '" & Replace(strOut, "'", "''") & "' where whatever=" & adoRS("whatever")