SQL Statement

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

:confused:

Try this:

Code:

---

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.

try:

Code:

---

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

---

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 ??

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

Code:

---

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

---

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

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.

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

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

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.

Yep, if you were to try:

Code:

---

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

---

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

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