Single quote problems 3

[savok]

What can I do about single quotes? Is there a way for me to prevent the user from entering single quotes in the text box? or a way to change my sql codes so that it doesnt give an error if there is a single quote in the text field?

the following would give an error and i am looking for ways to avoid it. Any advice?

txt = "TE'ST"
sql = "update table set field = '" & txt & "'"

Thanks

 

[elziko]

Can you use the Replace function to find all the single quotes it from the text string and replace it with "" before you stick it in the SQL statement? You need VB6 for replace though.

Or do you need to stop this occuring in the first place?

 

[savok]

yeah i guess i can strip all the single quotes before putting them into the database but that would take too long to do there has to be a better way

 

[gumby451]

Try placing conditional statement in the KeyPress event of the text box. If the keyascii value is equal to 39, set it equal to 0. This will throw away the keystroke, thus preventing the user from typing it in.

Inside KeyPress event:

Select case keyascii
Case 39
keyascii = 0
case else
End select

 

[Beesknees]

You could try cancelling the keystroke in the keypress event of the textbox so that if ascii=39 then keyascii=0 will do this or in sql (depending on language) in t-sql you could piece it together like and incorporating the function char(39) to take the place of the literal string.

 

[edderic]

"update table set field = '" & Replace(txt, "'", "''&quot & "'" Eric De Decker
vbg.be@vbgroup.nl

License And Copy Protection AxtiveX.

Download Demo version on my Site:

http://www.createsoftware2000.com

 

[savok]

thanks a lot very helpful

 

[TimLarkin]

Hey people! Undocumented SQL:
Use the # sign before and after the string. Also, routines that replace ' with '' note: SQL only recognizes the FIRST '' in the string - in "Joe's Mother's House" replaced with "Joe''s Mother''s House" , it'll blow up right after mother - that's why the # format is so nice, all the ' you want & no problem - HOWEVER!!!!!!!!!!!!!!! If the string ITSELF has a # in it your SOL!

strSQL = "Update MyTable (Comment) Values (#Joe's Mother's House#)"

 

[edderic]

to timlarkin

if in the database is "t'krantje" as saved ,ho do you seek of Find.First with # ???

# is for seek a date value and not quotes...
Eric De Decker
vbg.be@vbgroup.nl

License And Copy Protection AxtiveX.

Download Demo version on my Site:

http://www.createsoftware2000.com