locked
oleDbDataAdapter and Updating RRS feed

  • Question

  • I am using an oleDbDataAdapter and a dataset.  When I use this code to insert a new record, the record instantly is included in the dataset....

    this.dataSet11.tblDVD.AddtblDVDRow(field1, field2 );

    this.oleDbDataAdapter1.Update(this.dataSet11, "myTable");

    this.dataSet11.AcceptChanges();

    But, when I use this code to update a record, the changes are in the database, but NOT the dataset....

    this.oleDbDataAdapter1.UpdateCommand.CommandText = sql;

    this.oleDbConnection1.Open();

    this.oleDbDataAdapter1.UpdateCommand.ExecuteNonQuery();

    this.oleDbDataAdapter1.Fill(dataSet11);

    this.oleDbConnection1.Close();

    I can clear the dataset and and refill it, but don't want to do that.

    Suggestions?

    Thanks,

    Zath

    Sunday, January 1, 2006 9:41 PM

Answers

  • Why dont you try the following snippet taken out of SDK Documentation:

    Why don't you try using the following snippet, copied and pasted from SDK Documentation?

     

    public static DataSet UpdateRows(string connectionString, 
        string queryString, string tableName)
    {
        DataSet dataSet = new DataSet();
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = new OleDbCommand(queryString, connection);
            OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);
    
            connection.Open();
    
            adapter.Fill(dataSet, tableName);
    
            //code to modify data in DataSet here
    
            //Without the OleDbCommandBuilder this line would fail
            adapter.Update(dataSet, tableName);
    
            connection.Close();
        }
        return dataSet;
    }
    

    It will serve nice, if you provide it with the querystring (that is the SELECT command). OleDbDataAdapter works pretty nice and smoothly and when you update the dataset, the database will be updated too - dont know if you are looking for direct update in the database though, so in that case you have to set additional parameters or whatnot...

     

     

     

    Tuesday, January 3, 2006 12:34 PM

All replies

  • In the first example, the record is included in the dataset because you are explicitly adding it. In the second example, you are not explicitly adding the record. However, the Fill command should be updating existing records in the dataset IF your table has a primary key, and the primary key column is in the dataset; otherwise it cant update existing records.
    Monday, January 2, 2006 12:11 AM
  • Since it's a new record, to make the second example work, you would have to execute the InsertCommand instead of the UpdateCommand (unless the UpdateCommand is actually an INSERT SQL query.)

    However, I can't see a reason to use the second example. In the first example calling Update for the DataSet automatically uses the UpdateCommand, DeleteCommand or InsertCommand according to each modified record. It's much easier, and it works perfectly.

    [EDIT: And by the way, calling DataSet.Update automatically calls AcceptChanges on each record as it's updated on the underlying database, so it's not necessary for you to do it manually.]

    Luis Alonso Ramos

    Monday, January 2, 2006 12:44 AM
  • First, the primary keys are in the dataset - it's a multiple primary key.

    And for the update, I am using a sql statement....

    string sql = "UPDATE myTable SET " +

    ", fldDate='" + sDate +

    "', fldLength='" + iLength +

    "', fldTitle='" + sTitle +

    "' WHERE fldTitle='" + strTitle + "' AND fldType='" + strType + "'";

    I have been trying to adjust the code to do the update differently rather than accessing the db directly, but the dataset as in the insert example I gave, but with no luck.

    Even added this to the update and still, the dataset is not updating, but the database is...

    this.oleDbDataAdapter1.Update (this.dataSet11, "myTable");

    this.dataSet11.AcceptChanges();

    anyone have an example?

    Thanks,

    Zath

    Monday, January 2, 2006 7:22 PM
  • Why dont you try the following snippet taken out of SDK Documentation:

    Why don't you try using the following snippet, copied and pasted from SDK Documentation?

     

    public static DataSet UpdateRows(string connectionString, 
        string queryString, string tableName)
    {
        DataSet dataSet = new DataSet();
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = new OleDbCommand(queryString, connection);
            OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);
    
            connection.Open();
    
            adapter.Fill(dataSet, tableName);
    
            //code to modify data in DataSet here
    
            //Without the OleDbCommandBuilder this line would fail
            adapter.Update(dataSet, tableName);
    
            connection.Close();
        }
        return dataSet;
    }
    

    It will serve nice, if you provide it with the querystring (that is the SELECT command). OleDbDataAdapter works pretty nice and smoothly and when you update the dataset, the database will be updated too - dont know if you are looking for direct update in the database though, so in that case you have to set additional parameters or whatnot...

     

     

     

    Tuesday, January 3, 2006 12:34 PM