Save to database problem

Question

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?

NeCroFire · Answer

Schweet! I just tried it and it works fine. Thnx

Alan · Answer

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(''')