asked on

SQL Updates with aposthrophes

Can somone point me to an artical that describes the best way to handle SQL Updates with aposthrophes in the fields?
I can't seem to find alot of information on this topic.

Thanks.

dstanley9

Couple of options:

1) Use parameters instead of concatenating SQL strings
2) replace all single apostrophies (') with two apostrophies (two separate apostrophies not double-quotes).

Jedidia

ASKER

ok...that records the string into the database as (abc''123).

Based on this, when you do a select of the data, I would need to replace() the '' with just one aposthrophe.

Does that sound right?

dstanley9

Does using single apostrophies update the data correctly? If you're using string concatenation, you should use two apostrophies, which will get converted into one.

Can you post the code that is doing the update?

Using SQL parameters instead will be mch more secure and eliminate these types of conversion issues.

Jedidia

ASKER

strSQlUpdate = "UPDATE tblCategory "
strSQlUpdate &= "Set tblCategory.CategoryName = @CatName "
sqlCmd.parameters.add("@CatName", txtCategory.text)
strSQlUpdate &= "WHERE tblCategory.Category_ID =(SELECT tblCategory.Category_ID FROM tblCategory WHERE CategoryName = '" & txtCatName.text & "');"

Actually, The issue might be coming from my inbeded sql statement in my where clause.

ASKER CERTIFIED SOLUTION

dstanley9

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jedidia

ASKER

Thanks dstanley9!