Updating database from a DataSet

  • Thread starter Thread starter marc.birkett
  • Start date Start date
M

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
 
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.
Click to expand...
All the rest you use is OleDB

Cor
 
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
 
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?
 
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
 
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();
 
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();
 
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
 
Back
Top