Updating database from a DataSet

[marc.birkett]

Hello.

I am attempting to update the changes from a DataSet back to the
database, i am however getting the following error:

"The DataAdapter.SelectCommand property needs to be initialized"

The code i am using is:

//generate command objects to update source
OleDbCommandBuilder objCentreCommands = new
OleDbCommandBuilder(oDA);

//update table
oConn.Open();
oDA.Update(ds,"tblCentre");
oConn.Close();

Where oDA is an OdbcDataAdapter, oConn is the connection and ds is the
dataset which are all publically available. The error occurs on the
"oDA.Update()" line.

I dont understand my error as when i fill the dataset on the page
onload event, i use the code:

oConn.Open();
oDA.SelectCommand = new OleDbCommand(sqlCentre, oConn);
oDA.Fill(ds, "tblCentre");
oConn.Close();

So why do i need to redeclare the SelectCommand property?

Thanks,

Marc

 

[Cor Ligthert]

Mark,

Is this true?

Where oDA is an OdbcDataAdapter, oConn is the connection and ds is the
dataset which are all publically available. The error occurs on the
"oDA.Update()" line.

All the rest you use is OleDB

Cor

 

[Marc]

Actually it isnt! Meant OleDbDataAdapter. Sorry about that....

ta

marc

 

[Cor Ligthert]

Marc,

Did you create the commandbuilder after that the dataadapter had was
intialised with the select command or after that?

I think seeing your message that you did it before. I would place it here.

oConn.Open();
oDA.SelectCommand = new OleDbCommand(sqlCentre, oConn);
oDA.Fill(ds, "tblCentre");
*****CommandBuilder*******
oConn.Close();

I hope this helps,

Cor

 

[Marc]

I will try it.

Thanks for the reply.

 

[Marc]

Now i get "Update requires a valid InsertCommand when passed DataRow
collection with new rows". Been spending the last few days trying to
fix it.

I take it this means i have to write my own Insert, Update and Delete
commands?

 

[Cor Ligthert]

Marc,

Show how you did it in that fill, we see only a very little fragment of your
code.

And where you instance your dataadapter.

When you instance it new after doing the commandbuilder, you can be sure
that you get this message as now.


Cor

 

[Marc]

Hello.

This is where i do the fill's:

//add relations to dataset
sqlCentre = "SELECT * FROM tblCentre";
sqlActivity = "SELECT * FROM tblActivity";
sqlActivityTimes = "SELECT * FROM tblActivityTimes";
sqlTimes = "SELECT * FROM tblTimes";
sqlAdminLogins = "SELECT * FROM tblAdminLogins";

oConn.Open();
//data adapter to fill dataset and create command
builder
oDA.SelectCommand = new OleDbCommand(sqlCentre,
oConn);
oDA.Fill(ds, "tblCentre");
centreComms = new OleDbCommandBuilder(oDA);

oDA.SelectCommand = new OleDbCommand(sqlActivity,
oConn);
oDA.Fill(ds, "tblActivity");
activityComms = new OleDbCommandBuilder(oDA);

oDA.SelectCommand = new OleDbCommand(sqlActivityTimes,
oConn);
oDA.Fill(ds, "tblActivityTimes");
actTimesComms = new OleDbCommandBuilder(oDA);

oDA.SelectCommand = new OleDbCommand(sqlTimes, oConn);
oDA.Fill(ds, "tblTimes");
timesComms = new OleDbCommandBuilder(oDA);

oDA.SelectCommand = new OleDbCommand(sqlAdminLogins,
oConn);
oDA.Fill(ds, "tblAdminLogins");
adminLoginsComms = new OleDbCommandBuilder(oDA);
oConn.Close();

 

[Marc]

and this is where im trying to do the update - this is an event on a
button click:

string centName, centAddress, centTown, centTelephone,
centMail, centLocality, centDescription;
bool centBus, centMetro;

//get values
centName = centreName.Text;
centAddress = centreAddress.Text;
centTown = centreTown.Text;
centTelephone = centreTel.Text;
centMail = centreMail.Text;
centLocality = centreLocality.Text;
centDescription = centreDescription.Text;
centBus = centreBus.Checked;
centMetro = centreMetro.Checked;

//get datatable to update
DataTable dtActivity = ds.Tables["tblCentre"];
//set unique column
DataColumn pkCol = dtActivity.Columns["CentreID"];
pkCol.Unique = true;

//create new row.
DataRow dr = dtActivity.NewRow();
//DataRow dr = ds.Tables["tblCentre"].NewRow();
dr["Name"] = centreName.Text;
dr["Address"] = centreAddress.Text;
dr["Town"] = centreTown.Text;
dr["Tel"] = centreTel.Text;
dr["EMail"] = centreMail.Text;
dr["Locality"] = centreLocality.Text;
dr["Description"] = centreDescription.Text;
dr["Bus"] = centreBus.Checked;
dr["Metro"] = centreMetro.Checked;

//add new row
dtActivity.Rows.Add(dr);

//update table
oDA.Update(ds,"tblCentre");

//rebind datagrid
binddgCent();

 

[Marc]

Solved this. The following code creates a OleDbCommandBuilder in the
on-click event. Updates the dataset and database - then is disgarded.

Quite easy in the end no?

//commandbuilder
centreComms = new OleDbCommandBuilder(oDA);
oDA.SelectCommand = new OleDbCommand(sqlCentre, oConn);
oDA.SelectCommand.Connection = dbConnect(db.connString());
oDA.SelectCommand.CommandText =
(string)ViewState["sqlCentre"];

//update table
oDA.Update(ds,"tblCentre");


thanks for your help.

Marc