Error in datagridview using ms acess(c#)

Question

hi all, the below mentioned code raises error "SYNTAX ERROR IN INSERT INTO STATEMENT"

here i took name and number field has text

no as autonumber(primary key)

con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/ppd/db1.mdb;Jet OLEDB:Database Password=techsoft");

    da = new OleDbDataAdapter("select * from contacts", con);

    DataSet ds = new DataSet();

    da.Fill(ds, "contacts");

    dt = ds.Tables[0];

    dataGridView1.DataSource = dt;
    OleDbConnection con;

     OleDbDataAdapter da;

     DataTable dt;

     OleDbCommand cm;  

    string insertQuery = @"insert into  contacts(names,number,no) Values (@names,@number,@no)";

    cm = new OleDbCommand(insertQuery, con);

    cm.Parameters.Add("@names", OleDbType.VarChar, 10, "names");

    cm.Parameters.Add("@number", OleDbType.VarChar, 10, "number");

     cm.Parameters.Add("@no", OleDbType.VarChar, 10, "no");

    da.InsertCommand = cm;

    private void button26_Click(object sender, EventArgs e)
            {
                da.Update(dt);
            }

Answer 1

no is Autonumber, you can not give autonumber as parameter in your insert query.

Use below code :

con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/ppd/db1.mdb;Jet OLEDB:Database Password=techsoft");

    da = new OleDbDataAdapter("select * from contacts", con);

    DataSet ds = new DataSet();

    da.Fill(ds, "contacts");

    dt = ds.Tables[0];

    dataGridView1.DataSource = dt;
    OleDbConnection con;

     OleDbDataAdapter da;

     DataTable dt;

     OleDbCommand cm;  

    string insertQuery = @"insert into  contacts([names],[number]) Values (@names,@number)";

    cm = new OleDbCommand(insertQuery, con);

    cm.Parameters.Add("@names", OleDbType.VarChar, 10, "names");

    cm.Parameters.Add("@number", OleDbType.VarChar, 10, "number");


    da.InsertCommand = cm;

Note : I have just removed no from parameter list.

Answer 2

You can't put text in a number-type field. If "no" is autonumber, don't give it a value at all and it will auto-increment. Just change the line that say:

string insertQuery = @"insert into  contacts(names,number,no) Values (@names,@number,@no)";

to

string insertQuery = @"insert into  contacts(names,number) Values (@names,@number)";

and remove the line that says

cm.Parameters.Add("@no", OleDbType.VarChar, 10, "no");