How do I use the INSERT INTO query in Access to insert a record that is from a variable

Question

How do I use the INSERT INTO command from .NET to insert the contents of a variable Using CommandText.

Here is what I have:

(On button press)

string test = txtFirstName.Text;
comm.CommandText = "INSERT INTO Store (FirstName) Values (test)";

Integers and strings in quotes work, just not from a variable, and I need the program to write the text that is in the textbox to the database.

I'm getting this error:

An exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll but was not handled in user code

`Values ('" + test + "')";` but you **really** should use parametrized queries, they are designed for this - [Using parameters in SQL statements](http://stackoverflow.com/questions/7505808/using-parameters-in-sql-statements) — Alex K., Oct 31 '14 at 14:13
have a look at [this](http://www.dotnetperls.com/sqlparameter) for info on SQL parameters — jbutler483, Oct 31 '14 at 14:15

T.S. · Answer 1 · 2014-10-31T15:10:16.837

Your error happens because your string value is improperly formed. This is besides your syntax error. Feeding off @Alex K.

string test = txtFirstName.Text;
comm.CommandText = "INSERT INTO Store (FirstName) Values (@1)";
c.Parameters.AddWithValue("@1", test);

This is better than this

string test = txtFirstName.Text;
comm.CommandText = "INSERT INTO Store (FirstName) Values ('" + test.Replace("'", "''") + "')";

What if txtFirstName.Text has ', like in "O'Neal"? - the first example takes care of that as well, besides sql injection

1 Answers1