SQL and Access
Author
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 yet..is there something like an @ (like the one used in C#) that goes infront of the string?
thanks
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.AddNew
rs!MyField = "Some text"
rs!MyOtherField = 51
rs.Update
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]
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.AddNew
rs!MyField = "Some text"
rs!MyOtherField = 51
rs.Update
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]
Author
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 char..like "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" ???
Thanks
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 char..like "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" ???
Thanks
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.
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.
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.
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.
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.
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.
In PHP, I use addslashes() that turns ' into \', for all escape-characters. Maybe you can find something similar.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
