Error on updating access table using DataGrid

Question

I am working with DataGridView to update, insert or delete data to table. The code inserts data very well but on updating or deleting the data or row it gives an error. The code is as follows.

OleDbCommand sCommand;
    OleDbDataAdapter sAdapter;
    OleDbCommandBuilder sBuilder;
    DataSet sDs;
    DataTable sTable; 

    //Load Data...
    private void button1_Click(object sender, EventArgs e)
    {
        string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + Application.StartupPath + @"\Label-Data.accdb";
        string sql = "SELECT * FROM _Default";
        OleDbConnection connection = new OleDbConnection(connectionString);
        connection.Open();
        sCommand = new OleDbCommand(sql, connection);
        sAdapter = new OleDbDataAdapter(sCommand);
        sBuilder = new OleDbCommandBuilder(sAdapter);
        sDs = new DataSet();
        sAdapter.Fill(sDs, "_Default");
        sTable = sDs.Tables["_Default"];
        connection.Close();
        dataGridView1.DataSource = sDs.Tables["_Default"];
        dataGridView1.ReadOnly = true;
        button4.Enabled = false;
        dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
    }

    //Add or Edit...
    private void button2_Click(object sender, EventArgs e)
    {
        dataGridView1.ReadOnly = false;
        button4.Enabled = true;
        button2.Enabled = false;
        button3.Enabled = false;
    }

    //Delete...
    private void button3_Click(object sender, EventArgs e)
    {
        if (MessageBox.Show("Do you want to delete this row ?", "Delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
        {
            dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[0].Index);
            sAdapter.Update(sTable);
        }
    }

    //Save...
    private void button4_Click(object sender, EventArgs e)
    {
        sAdapter.Update(sTable);
        dataGridView1.ReadOnly = true;
        button4.Enabled = false;
        button2.Enabled = true;
        button3.Enabled = true;
    }

It gives an error on sAdapter.Update(sTable); The error is

Syntax error in query expression '((ID = ?) AND ((? = 1 AND Prn IS NULL) OR (Prn = ?)) AND ((? = 1 AND _Copy IS NULL) OR (_Copy = ?)) AND ((? = 1 AND _Item IS NULL) OR (_Item = ?)) AND ((? = 1 AND _Design IS NULL) OR (_Design = ?)) AND ((? = 1 AND Tray_No IS NULL) OR (Tray_No = ?)) AND (('.

How to eliminate this error?

Is something missing after the last "AND" operator? – Sandeep Jan 25 '13 at 07:11 — Sandeep, Jan 25 '13 at 07:11

Kiran1016 · Answer 1 · 2013-01-25T09:12:37.797

Try this.

 private void button3_Click(object sender, EventArgs e)
 {
    if (MessageBox.Show("Do you want to delete this row ?", "Delete",     MessageBoxButtons.YesNo) == DialogResult.Yes)
    {
        dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[0].Index);
       string sql="write your Delete query"
      OleDbCommand dCommand = new OleDbCommand(sql, connection);
        OleDbCommandBuilder builder = new OleDbCommandBuilder(sAdapter);
       sAdapter.DeleteCommand=dCommand
        sAdapter.Update(sTable);
    }
}

//Save...
private void button4_Click(object sender, EventArgs e)
{
     string sql="write your update query"
     OleDbCommand uCommand = new OleDbCommand(sql, connection);
     OleDbCommandBuilder builder = new OleDbCommandBuilder(sAdapter);
     sAdapter.UpdateCommand=uCommand 
    sAdapter.Update(sTable);
    dataGridView1.ReadOnly = true;
    button4.Enabled = false;
    button2.Enabled = true;
    button3.Enabled = true;
}

see this post ,this is the good way to add parameters.http://stackoverflow.com/questions/1966275/c-sharp-update-table-using-sqlcommand-parameters-asp-net — Kiran1016, Jan 25 '13 at 13:14

score 0 · Answer 2 · edited May 23 '17 at 11:52

0

You've got some really odd field names, what's with the leading underscore??

But it looks like the framework is failing to generate the insert and update SQL automatically.

Look at the following SO question, that describes adding your own UPDATE and INSERT statements to the adapter.

Alternatively, you could look at using VisualStudio's DataSet designer.

edited May 23 '17 at 11:52

Community

1
1

answered Jan 25 '13 at 09:19

cjb110

1,310
14
30

Error on updating access table using DataGrid

2 Answers2