SQL Statement

[fasi]

Well i spent hours and hours but couldnt figure this probelem out

I am querying the database on a variablename that is a string.. it works fine .. but as soon as my varable name has an appostrpohe then the whole program crashes.

LIke i wm ausing the statement

rst.Open"SELECT * from Customers wher CustomerName = ' " & variable & " ';", cnn

If the customer name is say

Kevin it works fine

But when it is
Kevin's

it crashes


any solutions to this problem

[ExcalibursZone]

Try this:

Replace(variable, "'", "' + CHAR(27) + '")
If Right(Variable, 4) = " + '" Then
    Variable = Left(Variable, Len(Variable) - 4)
End If

Hope this helps you out. CHAR() is the SQL equivalent of Chr$ in VB.

[Paragon]

try:

rst.Open"SELECT * from Customers wher CustomerName = ' " & replace(variable,"'","''") & " ';", cnn

[fasi]

PARAGON

Dont you think this code will replace one apostrophe with two ... bacause what i think is that it is reading the apostropphe as the end of the statement

can you explain a little bit what this code will do ??

[Lethal]

You may want to use the 'Like' Operator. Without knowing the full impliminations of the program, I'm just throwing out another suggestion.

rst.Open "SELECT * from Customers where CustomerName Like '" & variable & "%" & "';"

[fasi]

Well Lethal I am not very familiar with the like operator .. Can you tell me what is the difference in like and = operators
.

[Paragon]

The reason why I replace one ' with '' is because sql statements don't like strings with ' i.e. "where customer = 'It's'" will cause an error because it thinks you're ending the string with the second apostrophe instead of the third but if you were to have "where customer = 'It''s'" will work. (just tested it, just in case) It's just one of those weird things that you need to because of when creating sql statements.

[fasi]

Paragon Thanx alor I tried it It works .. but tell me one thing .. this thing i used was for storing in the database .. Is is going to effect the query in any way... when i have to retrieve the data

[fasi]

and what about the names that doen't have an apostrophe .. is it going to effect them

[Lethal]

The "Like" operator instructs the dbms that the following search pattern is to compared using a wildcard match, rather than a exact math. Hence, Kevin is equal to Kevin's or Kevin1234, etc...

It should solve your problem, but unexpected data may creep up, but if your only searching on a first name field, I wouldn't really worry about it to much.

[Paragon]

Yep, if you were to try:

rst.Open "INSERT INTO Customers(CustomerName) Values ('It''s');"

This would actually insert "It's" into the record.

[Paragon]

Nope it won't affect names without ' it just won't replace anything in it