none
Update Access Database with an OledbDataAdapter RRS feed

  • Question

  • Ok I have this code, just to learn the system.data.whatever dataprovider classes: 

    I can load the data into the datagridview and i can read an updated status into the datagrid after update. But I can't update my original AccessDatabase that I used. What am I missing here? 

    I also get this error message:

    Syntaxisfout (operator ontbreekt) in query-expressie (((? = 1 AND Familienaam IS NULL) OR (Familienaam = ?)) AND ((? = 1 AND Voornaam IS NULL) OR (Voornaam = ?)) AND ((? = 1 AND Zelfbetalende IS NULL) OR (Zelfbetalende = ?)) AND ((? = 1 AND OCMW IS NULL) OR (OCMW = ?)) AND ((? = 1 AND Dagprijs IS NULL) OR (.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.OleDb;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    
    namespace dbResidentenNoordhinder
    {
        public partial class Form1 : Form
        {
    
            OleDbDataAdapter dAdapter;
            DataTable dTable;
            BindingSource bSource;
            OleDbCommandBuilder cBuilder;
            
            public Form1()
            {
                InitializeComponent();
            }
    
            private void btnInladen_Click(object sender, EventArgs e)
            {
                string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\olivier\Bureaublad\residentenlijst.mdb; User ID=admin; Password=;";
                string query = @"SELECT * FROM [Residentenlijst facturatie]";
    
                //create an OleDbDataAdapter to execute the query
                dAdapter = new OleDbDataAdapter(query,connString);
    
                //create a command builder
               cBuilder = new OleDbCommandBuilder(dAdapter);
    
                //create a DataTable to hold the query results
                dTable = new DataTable();
    
                //fill the DataTable
                dAdapter.Fill(dTable);
    
                //BindingSource to sync DataTable and DataGridView
                bSource = new BindingSource();
                
    
                //set the BindingSource DataSource
                bSource.DataSource = dTable;
    
                //set the DataGridView DataSource
                dgView.DataSource = bSource;
    
            }
    
            private void btnUpdate_Click(object sender, EventArgs e)
            {
                dAdapter.Update(dTable);
            }
    
            private void dgView_CellContentClick(object sender, DataGridViewCellEventArgs e)
            {
            }
        }
    }
    
    


    Thursday, October 20, 2011 12:36 PM

Answers

  • I am afraid that you have got the wrong command query here.

    You can have a look at the sample code below which is shown at he page in my previews reply:

    OleDbCommand command; 
    // You will have to set the data fields in the query here.
    command = new OleDbCommand("UPDATE dbo.Customers SET CustomerID = ?, CompanyName = ? " +  "WHERE CustomerID = ?", connection);
    
    //when you set the parameters here, the OleDbDataAdapter will map the parameters according to the column name in the DataTable and update them in your database.
    command.Parameters.Add( "CustomerID", OleDbType.Char, 5, "CustomerID");
    command.Parameters.Add("CompanyName", OleDbType.VarChar, 40, "CompanyName");
    parameter = command.Parameters.Add("oldCustomerID", OleDbType.Char, 5, "CustomerID");
    
    dataAdapter.UpdateCommand = command;
    

    After you set it with the selectcommand, whenever you want to update the data, you just need to update it.

    Let me show a full method for you:

    /// <summary>
    /// update the data
    /// </summary>
    /// <param name="dt"></param>
    public void UpdateAllStudent(DataTable dt)
    {
        string selectsql = "select * from Student";
        string sql = "update Student set s_name=@s_name, s_age=@s_age,s_classId=@s_classId,s_desc = @s_desc, s_createTime=@s_createTime where id=@id";
        OleDbConnection odbConn = new OleDbConnection(ConnectionString);
        OleDbDataAdapter adapter = new OleDbDataAdapter(selectsql, odbConn);
        adapter.UpdateCommand = new OleDbCommand(sql, odbConn);
    
        adapter.UpdateCommand.Parameters.Add("@s_name", OleDbType.VarChar,20,"s_name");
        adapter.UpdateCommand.Parameters.Add("@s_age", OleDbType.Integer,3, "s_age");
        adapter.UpdateCommand.Parameters.Add("@s_classId", OleDbType.Integer,3, "s_classId");
        adapter.UpdateCommand.Parameters.Add("@s_desc", OleDbType.VarChar, 100, "s_desc");
        adapter.UpdateCommand.Parameters.Add("@s_createTime", OleDbType.DBTimeStamp,20, "s_createTime");
        adapter.UpdateCommand.Parameters.Add("@id", OleDbType.Integer, 20, "id");
    
        adapter.Fill(dt);
        adapter.Update(dt);
        odbConn.Close();
    }
    

    This just a sample for a student table. You can update any tables like that.

    Best Regards

     


    Neddy Ren [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Neddy Ren Wednesday, November 2, 2011 2:17 AM
    Tuesday, October 25, 2011 7:23 AM
  • Hi,

    Welcome to the MSDN Forum and sorry for the delay.

    The mistakes here is the updatecommand. You haven't set the update query for the DataAdapter:

    http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter.updatecommand.aspx 

    you can get the sample code at the page and it shows how to use the OleDataAdapter.UpdateCommand.

    If you have any questions, please feel free to tell us.

    Best Regards


    Neddy Ren [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Neddy Ren Wednesday, November 2, 2011 2:17 AM
    Monday, October 24, 2011 9:14 AM

All replies

  • nobody? Then this code should work, but for some reason it doesn't?
    Monday, October 24, 2011 6:54 AM
  • Hi,

    Welcome to the MSDN Forum and sorry for the delay.

    The mistakes here is the updatecommand. You haven't set the update query for the DataAdapter:

    http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter.updatecommand.aspx 

    you can get the sample code at the page and it shows how to use the OleDataAdapter.UpdateCommand.

    If you have any questions, please feel free to tell us.

    Best Regards


    Neddy Ren [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Neddy Ren Wednesday, November 2, 2011 2:17 AM
    Monday, October 24, 2011 9:14 AM
  • I'm still doing something wrong.

    Do I need parameters if I just use a datagridview? Because there should be a way to tell the data adapter that the grid is updated and the data should return to the access database. 

    namespace dbResidentenNoordhinder

    {

        public partial class Form1 : Form

        {

            static string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\olivier\Bureaublad\residentenlijst.mdb; User ID=admin; Password=;";

            static string query = @"SELECT * FROM Residentenlijst_facturatie";

            static string queryUpdate = @"UPDATE * FROM Residentenlijst_facturatie";

     

            public static OleDbDataAdapter dAdapter = new OleDbDataAdapter(query,connString);

            DataTable dTable = new DataTable();

            BindingSource bSource;

            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);

            public static OleDbCommand command;

     

            public Form1()

            {

                InitializeComponent();

            }

     

            private void btnInladen_Click(object sender, EventArgs e)

            {

     

                //create an OleDbDataAdapter to execute the query

                //dAdapter = new OleDbDataAdapter(query,connString);

                //create a command builder

               //cBuilder = new OleDbCommandBuilder(dAdapter)

                //create a DataTable to hold the query results

                //dTable = new DataTable();

     

                //fill the DataTable

                dAdapter.Fill(dTable);

     

                //BindingSource to sync DataTable and DataGridView

                bSource = new BindingSource();

     

     

                //set the BindingSource DataSource

                bSource.DataSource = dTable;

     

                //set the DataGridView DataSource

               dgView.DataSource = bSource;

     

            }

     

            private void btnUpdate_Click(object sender, EventArgs e)

            {

                command = new OleDbCommand(queryUpdate);

                dAdapter.UpdateCommand = command;

            }

        }

    }

    Monday, October 24, 2011 9:40 AM
  • I am afraid that you have got the wrong command query here.

    You can have a look at the sample code below which is shown at he page in my previews reply:

    OleDbCommand command; 
    // You will have to set the data fields in the query here.
    command = new OleDbCommand("UPDATE dbo.Customers SET CustomerID = ?, CompanyName = ? " +  "WHERE CustomerID = ?", connection);
    
    //when you set the parameters here, the OleDbDataAdapter will map the parameters according to the column name in the DataTable and update them in your database.
    command.Parameters.Add( "CustomerID", OleDbType.Char, 5, "CustomerID");
    command.Parameters.Add("CompanyName", OleDbType.VarChar, 40, "CompanyName");
    parameter = command.Parameters.Add("oldCustomerID", OleDbType.Char, 5, "CustomerID");
    
    dataAdapter.UpdateCommand = command;
    

    After you set it with the selectcommand, whenever you want to update the data, you just need to update it.

    Let me show a full method for you:

    /// <summary>
    /// update the data
    /// </summary>
    /// <param name="dt"></param>
    public void UpdateAllStudent(DataTable dt)
    {
        string selectsql = "select * from Student";
        string sql = "update Student set s_name=@s_name, s_age=@s_age,s_classId=@s_classId,s_desc = @s_desc, s_createTime=@s_createTime where id=@id";
        OleDbConnection odbConn = new OleDbConnection(ConnectionString);
        OleDbDataAdapter adapter = new OleDbDataAdapter(selectsql, odbConn);
        adapter.UpdateCommand = new OleDbCommand(sql, odbConn);
    
        adapter.UpdateCommand.Parameters.Add("@s_name", OleDbType.VarChar,20,"s_name");
        adapter.UpdateCommand.Parameters.Add("@s_age", OleDbType.Integer,3, "s_age");
        adapter.UpdateCommand.Parameters.Add("@s_classId", OleDbType.Integer,3, "s_classId");
        adapter.UpdateCommand.Parameters.Add("@s_desc", OleDbType.VarChar, 100, "s_desc");
        adapter.UpdateCommand.Parameters.Add("@s_createTime", OleDbType.DBTimeStamp,20, "s_createTime");
        adapter.UpdateCommand.Parameters.Add("@id", OleDbType.Integer, 20, "id");
    
        adapter.Fill(dt);
        adapter.Update(dt);
        odbConn.Close();
    }
    

    This just a sample for a student table. You can update any tables like that.

    Best Regards

     


    Neddy Ren [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Neddy Ren Wednesday, November 2, 2011 2:17 AM
    Tuesday, October 25, 2011 7:23 AM