MWoolley MWoolley - 1 month ago 12
SQL Question

How to INSERT INTO access database with c#

I am trying to add data to my access database from winform using c#.

I keep getting a syntax error regarding my INSERT INTO statement and cannot see where I am going wrong.

Please can someone review my code and tell me where I am going wrong.

private void btnLog_Click(object sender, EventArgs e)
{
txtStatus.Text = "Open";

conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\mwool\\Desktop\\Uni\\3rd Year\\SEM 1\\AP\\Assignment\\Staff.accdb";

string sql = "INSERT INTO Fault (faultType, Status, TechId, StaffId, Zone, Description) VALUES ('" + txtFaultType.Text + "', '" + txtStatus.Text + "', " + txtTechId.Text + "' , '" + txtStaffId.Text + "' , '" + txtZone.Text + "' , '" + txtDescription.Text + "')";

OleDbCommand add = new OleDbCommand();

add.CommandText = sql;

add.Connection = conn;

add.Connection.Open();

add.ExecuteNonQuery();

conn.Close();

}

Answer
  • Always use parameterized queries, see Best Practices - Executing Sql Statements. This prevents simple mistakes like forgetting a ' with a string but more importantly prevents sql injection attacks.

  • Also always wrap your database connections, commands, and any other Disposable objects in using blocks.

Your code refactored with using statements and parameterized inputs.

using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\mwool\\Desktop\\Uni\\3rd Year\\SEM 1\\AP\\Assignment\\Staff.accdb"))
using (OleDbCommand cmd = new OleDbCommand())
{
    cmd.Connection = con;
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = string sql = "INSERT INTO Fault (faultType, Status, TechId, StaffId, Zone, [Description]) VALUES (?, ?, ?, ?, ?, ?)";

    cmd.Parameters.Add(new OleDbParameter("@faultType", OleDbType.VarChar)).Value = txtFaultType.Text;
    cmd.Parameters.Add(new OleDbParameter("@Status", OleDbType.VarChar)).Value = txtStatus.Text;

    // this parameter is an example of passing an int instead of a string. Alwaysuse the correct types!
    cmd.Parameters.Add(new OleDbParameter("@TechId", OleDbType.Int)).Value = int.Parse(txtTechId.Text);

    cmd.Parameters.Add(new OleDbParameter("@StaffId", OleDbType.VarChar)).Value = txtStaffId.Text;
    cmd.Parameters.Add(new OleDbParameter("@Zone", OleDbType.VarChar)).Value = txtZone.Text;
    cmd.Parameters.Add(new OleDbParameter("@Description", OleDbType.VarChar)).Value = txtDescription.Text;

    con.Open();
    cmd.ExecuteNonQuery();
}

OleDbCommand does not support named parameters, see OleDbCommand.Parameters

Remarks

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used.


Also note that:

  • OleConnection and OleDbCommand are wrapped in using blocks so they are disposed/cleaned up even when an exception occurs.
  • Parameters are now used instead of hard coding the string values
  • Parameters use the correct data types

It might be that the use of Description is not allowed because it is a reserved word (see link). In that case surround it with [] (see update above).

Comments