Web Development

Ask something
Discussion Locked

VB6 : Error in Insert Query.

0 helpful
10 replies

by aamsoh

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

When i used this query in vb then error occur plz tell me what i do now.

text1.text = 4
text2.text = "Aamir's & sons co."

Insert into suppliers ( supplierid, companyname) values ('" & text1.text & "', '" & text2.text & "')"

Error Message : Incorrect syntax near 's'

10 replies

by mark.thomson

The SQL parser does not like the apostrophe in "Aamir's"

You will need to write a validation routine that does 1 of 2 things

1 Removes any apostrophes from any string you are goning to use as a string literal in any SQL statement

or

2 Replaces single apostrophes with double apostrophes - "Aamir''s" should be OK

by sl-campbell

you could write it this way:

Insert into suppliers ( supplierid, companyname) values (""" & text1.text & """, """ & text2.text & """)".

But you will always have this problem when users enter a single or double quotes.

You could prevent the users from entering double quotes and then always use the double quotes in the SELECT statement, or vice-versa using single quotes.

You could also mix them.
Create a function that checks each user input value for the presence of double or singlequotes using the InStr() function. This function would then return a string including the outside double or single quotes.

Something like:
Private Function TestQuotes(strTest As String) As String

If InStr(strTest, "'") Then
TestQuotes = """" & strTest & """"
Else
TestQuotes = "'" & strTest & "'"
End If
End Function

Then build the SELECT string at run time calling the function for each field.

by sl-campbell

All the below statements will work on a MDB databank (No removing of quotes needed):

1). An Update query updating TWO fields, one with SINGLE quotes and one with DOUBLE quotes

UPDATE Personal SET Personal.LastName = "Test'Single", _
Personal.FirstName = 'Test" Double'
WHERE Personal.PersonalNr="0010101";

2). A Select query with criteria on a field that contains a SINGLE quote

SELECT Personal.PersonalNr, _
Personal.LastName, _
Personal.FirstName
FROM Personal
WHERE Personal.LastName="Test'Single";

3). A Select query with criteria on a field that contains a DOUBLE quote

SELECT Personal.PersonalNr, _
Personal.LastName, _
Personal.FirstName
FROM Personal
WHERE Personal.FirstName='Test" Double';

It only matters on how you build the SQL statement, in which a function would make it easy to check for the presence double or single quotes, (Using InStr), and add the needed surrounding quotes.

by sl-campbell

Two more comment: What you will need to check for is the presence of both, a single and a double quote, in the value to be saved.

If both are present, then you can either notify the user that both are not possible, or removed one of the quote types.

by john_wills

Mark Thomson's answer seems right, but I would like to add a workaround. If you are using Access(or some other screeen/database interface) you can store the text with the apostrophe, but you will then have to use a function stripping apostrophes outwhen seeking or comparing names. Your system could then not distinguish between Aamir's and AAmirs'.

This discussion is closed to new replies.