We help IT Professionals succeed at work.

VB6 SQL usage myDB.execute help

groone
groone asked
on
Medium Priority
528 Views
Last Modified: 2008-03-03
In an application I have a sql call similar to the one below:
mydb.Execute ("DELETE FROM myTable WHERE myField = '" & Combo1.Text & "'")

Now the problem exist that the myField can contain an apostrophe like in - Gun's - but when this happens the SQL statement bombs.  How can I use this statement when such an occurence exists?

Comment
Watch Question

hes
CERTIFIED EXPERT

Commented:
Try using this

mydb.Execute ("DELETE FROM myTable WHERE myField = '" & Replace(Combo1.Text,"'","''") & "'")
 Because it is hard to read it is replace one ' with two ''
Suat OzgurWeb / Application Developer
CERTIFIED EXPERT

Commented:
300 point? :)

mydb.Execute ("DELETE FROM myTable WHERE myField = """ & Combo1.Text & """")

hes i am not agree with you about using another function instead using two " in a text.

regards
suat
Commented:
If myfield can contain an apostrophe try:

mydb.Execute ("DELETE FROM myTable WHERE [myField] = '" & Combo1.Text & "'")

If the value of Combo1.Text can contain an apostrophe, try:

mydb.Execute ("DELETE FROM myTable WHERE [myField] = " & Chr(34) & Combo1.Text & Chr(34))
Dim sText as String

sText = Replace(Combo1.Text, "'", "''")

mydb.Execute("DELETE FROM myTable WHERE myField = '" & sText & "''"


The object is to replace any apostrophes within the text with double apostrophes.  This will keep SQL from bombing as well as retain the apostrophes within the original text in the DB.

Thank u

Explore More ContentExplore courses, solutions, and other research materials related to this topic.