Sign in to follow this  

SQL and Access

Recommended Posts

Hi, I'm using C# and trying to add a string from a textbox into a table in an Access Database..the problem is..if a user types something like " or '... So I guess the question is..Is there an escape character for Jet Databases? Or better there something like an @ (like the one used in C#) that goes infront of the string? thanks

Share this post

Link to post
Share on other sites
It totally depends on how you're actually populating the database. If you're forming an SQL string, you have to double-pad the quotes with replaces (in ASP text = Replace(text, "'", "''") etc).

Another way, since you said you're using Jet is to take advantage of the recordset features. I'm not sure about C#, but in VB it's like this...

- Open your recordset (Set rs = databaseconneection.execute("SELECT * from myTable")

- Use the AddNew/Update methods
rs!MyField = "Some text"
rs!MyOtherField = 51

I'm pretty sure that C# will have an equivalent. Have a look at the Recordset object docs.

Hope this helped in some way [smile]

Share this post

Link to post
Share on other sites
Hmm well in C# im using an OleDBCommand

and to make things simpilar..what I wanted to add to a table would be...

"I don't know how to do this!!"

so I would try something like this...
commandstring = "UPDATE table SET column ='I don't know how to do this!' WHERE ID = 4"

As you can see the ' in "don't" will cause an error..
so how would I go about inserting the ' into a table.. I tried doing the \ escape "I don\'t know how to do this!!" but it still doesn't work...
So is there an escape char? I googled on it with no luck. Found one other post for Java that seems to work, but not with C#..

btw. I will try to the double padding the quotes to see if it works..but i'm not on my computer right now so I can't try it..
but you meant something like this right?
"UPDATE table SET column = 'I don"'"t know how to do this!' WHERE ID = 4" ???

Share this post

Link to post
Share on other sites
It's been a while since I've had to use Access, but I think the proper approach would be:

UPDATE table SET field = 'My brain ' ' s on fire.'

(without the extra spaces between the n, ', ', and s)

Where you use two ' characters instead of one. You can also do this with double-quotes:

UPDATE table SET field = "I will say ""My brain's on fire,"" and then run away."

Basically, if you use single quotes to surround your strings, then you can use double-quotes in those strings without worrying, but you need to use two single quotes to get a single quote to appear in the string. Similarly, if you use double quotes to surround a string, you can use single quotes inside that string without trouble, but you have to use two double quotes if you want a double quote in the final string.

Doing all of this through a Recordset object is a heck of a lot easier, though; if I were you I would look into that if it is at all possible.

Share this post

Link to post
Share on other sites
As I said, if you can use a function that replaces text (in ASP it's Replace, not sure about C# - probably a member of the String class).

But it's a case of:

text = "I don't know how to do this!"

text = Replace(text, "'", "''")

text is now: "I don''t know how to do this!"

Which works in SQL statements.

Share this post

Link to post
Share on other sites
I think I see a possible source of confusion here; it looks like the forum software replaces '' with a single ', which makes it look like our suggestions aren't doing anything [wink]

kag1, to see what we're really talking about, click on the Edit button on one of our posts, and it should show you the places to use two 's.

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this