reflecting changes of DataTable to Database

[weberwhennner]

Hi All,

Ive spent days breaking my head over why the code below doesnt update changes to the database. Could someone please help...

 
 
            OleDbDataAdapter adp = new OleDbDataAdapter("select * from TestTable", connection );
            adp.InsertCommand = new OleDbCommandBuilder(adp).GetInsertCommand();
 
            DataTable dt_dst = new DataTable("TestTable");
            adp.Fill(dt_dst);
 
        DataRow rw = dt_dst.NewRow();
        rw["ID"] =123;
        rw["CAT_ID"] = "adadas";
 
        dt_dst.Rows.Add(rw);
 
 
    dt_dst.AcceptChanges();
    adp.Update(dt_dst);
 
 

[bplacker]

Hi All,

Ive spent days breaking my head over why the code below doesnt update changes to the database. Could someone please help...

 
 
            OleDbDataAdapter adp = new OleDbDataAdapter("select * from TestTable", connection );
            adp.InsertCommand = new OleDbCommandBuilder(adp).GetInsertCommand();
 
            DataTable dt_dst = new DataTable("TestTable");
            adp.Fill(dt_dst);
 
        DataRow rw = dt_dst.NewRow();
        rw["ID"] =123;
        rw["CAT_ID"] = "adadas";
 
        dt_dst.Rows.Add(rw);
 
 
    dt_dst.AcceptChanges();
    adp.Update(dt_dst);
 
 

I've never done it this way... the way I've done it before involves writing the insert command out.
so once you have your insert command... just do:

apd.InsertCommand = new SQLCommand(insertSQL)
adp.InsertCommand.Connection = connection
adp.InsertCommand.ExecuteNonQuery()

[bplacker]

You might also try something like this:

Dim addedFields as new DataTable
Dim builder as new OleDBCommandBuilder
builder.DataAdapter = myTableAdapter

newFields = myDataTable.GetChanges(Data.DataRowState.Added)

builder.refreshSchema()
myTableAdapter.UpdateCommand = builder.getUpdateCommand()
'or insert command, I'm not sure.

myTableAdapter.Update(newFields)

myDataTable.AcceptChanges()

please let me know if this works, that might be somewhat useful

[bhar]

You might also try something like this:

Dim addedFields as new DataTable
Dim builder as new OleDBCommandBuilder
builder.DataAdapter = myTableAdapter

newFields = myDataTable.GetChanges(Data.DataRowState.Added)

builder.refreshSchema()
myTableAdapter.UpdateCommand = builder.getUpdateCommand()
'or insert command, I'm not sure.

myTableAdapter.Update(newFields)

myDataTable.AcceptChanges()

please let me know if this works, that might be somewhat useful

Hi,

Steps:

Instantiate the DataAdapter
Set the DataAdapter command properties
The DataAdapter class has four properties:

SelectCommand
InsertCommand
UpdateCommand
DeleteCommand

Each of the property perform a specific operation on database.

InsertCommand:
This read-write property returns or sets the SQL statement that will be used whjen rows are inserted into the data source.

DataAdapter needs to retrieve or send data to and from the database, it uses Command objects which must be specified.

This is done by creating command objects, and then assigning them to teh appropriate DataAdapter property:

Ex:

MyAdapter.InsertCommand=comInsert

Updation happens with Update() method of DataAdapter.

Try this book: "Database programming using Vb.net and sql server", [Link Removed]
Regards
Madhv