It's a Acces database and I store file names in it.
This is my Command text:
insert.CommandText = "INSERT INTO CompleteListTable(Source, [File
Name], [File Size], [Creation Date], Directory)VALUES('" +
ListTable.Rows[i].Cells[y].Value.ToString() + "','" +
ListTable.Rows[i].Cells[y + 1].Value.ToString() + "','" +
ListTable.Rows[i].Cells[y + 2].Value.ToString() + "','" +
ListTable.Rows[i].Cells[y + 3].Value.ToString() + "','" +
ListTable.Rows[i].Cells[y + 4].Value.ToString() + "')";
It works fine... until there is a " ' ", like in "don't", in the value or some other type of character that can mess up the query... then it gives me an syntax error.
I thought of using these [] brackets before and after each comma, but
then I get a "Incorrect use of brackets blah blah..." error.
What am I doing wrong, or what am I missing?
Loading
NeCroFirePosted Sep 26, 2007, 5:26 AM
Thnx
AlanPosted Sep 25, 2007, 12:00 PM
Well, the problem with apostrophes is that, when Access encounters one within a string, it thinks it's reached the end of the string and this then leaves an 'unpaired' trailing apostrophe which produces the syntax error.
The way to avoid this is to double the apostrophes which you can do using the String.Replace() method. This method has the twin advantages that it replaces all apostrophes (not just the first one) and has no affect on the string (and doesn't throw an exception) if the string doesn't actually contain one.
So if the following string could contain an apostrophe:
ListTable.Rows[i].Cells[y].Value.ToString()
you'd need to replace it with:
ListTable.Rows[i].Cells[y].Value.ToString().Replace("'","''")