[RESOLVED] SQL logic error in text with apostrophe

[threeeye]

Hi guys,
I have a similar problem as this thread http://www.vbforums.com/showthread.p...ter-in-textbox but a little bit different...
When I have a text with apostrophe (') (eg: Text1.text = "na'eblis"), I get an "SQL logic error near "eblis": syntax error"
The SQL can't deal with apostrophe in the textbox...
Is there a way to check if there is an apostrophe in the text string and change it?
Something like this:
Normal code:

cmd.CommandText = "INSERT INTO Info (Name) VALUES ('" & Name.Text & "')"

If there is an apostrophe in the text string:

Some code to split the text1.text string in to 3 (before apostrophe(str1), the apostrophe(str2) and after(str3))

Then we combine the 3 and add the proper syntax to the apostrophe.
But I can't think of the proper syntax for it...
Can someone help?
Thanks

[techgnome]

The direct answer to your question is yes, yes there is .... the real answer though is: don't do it, use parameters instead. This is why.

cmd.CommandText = "INSERT INTO Info (Name) VALUES (@NewName)"
cmd.Parameters.AddWithValue("@NewName", Name.Text)

Now you don't care about apostrophes or other funky characters that break the SQL.

-tg

[Shaggy Hiker]

Even better, you also don't have to worry about SQL injection exploits.

[si_the_geek]

For further explanation of various reasons why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of the Database Development forum).

[threeeye]

Thanks guys
I read all the links, and I see the error in my ways...
I will change my code and write back (on Tue - I hope)

Thanks for the help

[threeeye]

The direct answer to your question is yes, yes there is .... the real answer though is: don't do it, use parameters instead. This is why.

cmd.CommandText = "INSERT INTO Info (Name) VALUES (@NewName)"
cmd.Parameters.AddWithValue("@NewName", Name.Text)

Now you don't care about apostrophes or other funky characters that break the SQL.

-tg

This worked perfectly
Thanks for the help