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.InsertComma nd = new SQLCommand(inse rtSQL)
adp.InsertComma nd.Connection = connection
adp.InsertComma nd.ExecuteNonQu ery()

[bplacker]

You might also try something like this:

Dim addedFields as new DataTable
Dim builder as new OleDBCommandBui lder
builder.DataAda pter = myTableAdapter

newFields = myDataTable.Get Changes(Data.Da taRowState.Adde d)

builder.refresh Schema()
myTableAdapter. UpdateCommand = builder.getUpda teCommand()
'or insert command, I'm not sure.

myTableAdapter. Update(newField s)

myDataTable.Acc eptChanges()

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 OleDBCommandBui lder
builder.DataAda pter = myTableAdapter

newFields = myDataTable.Get Changes(Data.Da taRowState.Adde d)

builder.refresh Schema()
myTableAdapter. UpdateCommand = builder.getUpda teCommand()
'or insert command, I'm not sure.

myTableAdapter. Update(newField s)

myDataTable.Acc eptChanges()

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.Inser tCommand=comIns ert

Updation happens with Update() method of DataAdapter.

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