Input correct syntax in the form

al4629740
al4629740 used Ask the Experts™
on
I have a form in vb6 that attaches to an SQL database.  I had a user enter a name in one field:

D'Mare

The name above caused a problem when I later had to do a query.  Mainly because of the apostrophe ( ' )

Is there a way to check the field on my form to make sure that this does not happen when someone is entering a name?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
when you send data to SQL, you need to double the quotes to save one:

replace(yourvariablename, "'", "''")

Author

Commented:
D'Mare is a rare example.  95% of names do not have an apostrophe in there.  In the case that it does, what code do I use to block such entries?

Hi

I am not familliar with vb6 any more but i am sure that there is a KeyPress event on the textbox which you can handle.
This event takes a KeyAscii as parameter which is the ascii code of the key pressed. Apostrophe is ascii 39.

Then if KeyAscii matches 39 reset it to 0.

I would really suggest using emoreau's solution though because i don't think it would be nice to cut functionality from the user.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
you can always use my code for any string you try to send to SQL. what it does is that it doubles the quotes which will save only 1 in the database.

Author

Commented:
Ok, I will try it later

Author

Commented:
Should it be:

Private Sub Text4_LostFocus()
Replace(.Text4, "'", "''") = Text4
End Sub
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
no, you leave what the user enters as is. only when you need to create the query to send the value to the database that you transform it. can you show your insert/update query?

it will be something like this:

strSQL = "update tableX " & _
         "Set LastName = '" & replace(text4.text, "'", "''") & "' " & _
         "where ........."

Open in new window

Author

Commented:
The following is how I place the values into the database
  esql = "select * from tblRegistrationTitleXX"
        rec.Open (esql), conn, adOpenDynamic, adLockOptimistic
        rec.AddNew
        
        rec.Fields(1) = frmRegistrationTitleXX.Text1
        rec.Fields(2) = frmRegistrationTitleXX.Text2
        rec.Fields(3) = frmRegistrationTitleXX.Text3
        rec.Fields(4) = frmRegistrationTitleXX.Text4

Open in new window

Senior .Net Consultant
Top Expert 2016
Commented:
use this:

rec.Fields(4) = replace(frmRegistrationTitleXX.Text4, "'", "''")

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial