How to insert new value autonumber by parameters to access

Question

When I press insert, I receive the error: Syntax error in INSERT INTO statement

EmployeeID (Autonumber)
EmployeeName(Text)
Position(Text)

Address(Text)

OleDbDataAdapter ad;
DataSet ds;
DataTable dt;

protected void SetInsertParameters()
{
    string sql = "INSERT INTO Employee(EmployeeName,Position,Address)"+
                 " VALUES (@EmployeeName,@Position,@Address)";
    ad.InsertCommand = new OleDbCommand(sql, con);


    OleDbParameter param = new OleDbParameter("@EmployeeName", OleDbType.VarChar);
    param.SourceColumn = "EmployeeName";
    param.SourceVersion = DataRowVersion.Current;
    ad.InsertCommand.Parameters.Add(param);

    param = new OleDbParameter("@Position", OleDbType.VarChar);
    param.SourceColumn = "Position";
    param.SourceVersion = DataRowVersion.Current;
    ad.InsertCommand.Parameters.Add(param);

    param = new OleDbParameter("@Address", OleDbType.VarChar);
    param.SourceColumn = "Address";
    param.SourceVersion = DataRowVersion.Current;
    ad.InsertCommand.Parameters.Add(param);
}


void InsertNewValues()
{
    dt = ds.Tables["Employee"];
    DataRow row = dt.NewRow();
    row[0] = txt_employeeID.Text;
    row[1] = txt_name.Text;
    row[2] = txt_position.Text;
    row[3] = txt_address.Text;
    dt.Rows.Add(row);
    ad.Update(ds, "Employee");
    ad.Fill(ds);

}

Have you named your columns in your table? I don't see it here. — Dominic Zukiewicz, Jan 01 '13 at 21:31
The other thing is that you are probably using a 'CommandType.Text` which I don't believe is compatible with this type of insert. Its used for `.TableDirect` or `.StoredProcedure` — Dominic Zukiewicz, Jan 01 '13 at 21:33

score 0 · Answer 1 · answered Jan 01 '13 at 22:58

0

Position is a reserved word and must be bracketed:

 "INSERT INTO Employee(EmployeeName,[Position],Address)"

answered Jan 01 '13 at 22:58

Fionnuala

90,370
7
114
152

How to insert new value autonumber by parameters to access

1 Answers1