I'm using a dataset created from an Access database. After deleting one of

the rows in tha dataset I try to save the new dataset back to the

database. But next time I look in the database it looks the same. For some

reason it is not updated. Below is a transcript of my code.

Could anyone please help me spot what I've been doing wrong



String connstr = @"Provider=Microsoft.JET.OLEDB.4.0; Data

Source=c:\datastores\RegEx.mdb";

ADOConnection dataconn = null;

DataSet ds = null;

String SQL = "select * from RegEx";

ADODataSetCommand MyCommand = null;

ADODataSetCommand MyCommand1 = null;



try {

  dataconn = new ADOConnection(connstr);

  dataconn.Open();

  MyCommand = new ADODataSetCommand(SQL, dataconn);   

  ds = new DataSet();

  MyCommand.FillDataSet(ds, "Expressions");     

}

catch (Exception e) {

  e.ToString();

}

finally {

  if(dataconn != null) {

    dataconn.Close();

    dataconn = null;

   }

}

String name = "Testname";

DataTable table = ds.Tables["Expressions"];

DataRow[] rows = table.Select("Name = '" + name + "'");

DataRow editrow = rows[0];

editrow.BeginEdit();

editrow.Delete();

editrow.AcceptChanges();



try {

  dataconn = new ADOConnection(connstr);

  dataconn.Open();

  MyCommand.Update(ds,"Expressions");

  ds.AcceptChanges();

}

catch (Exception e) {

  e.ToString();

}

finally {

  if(dataconn != null) {

     dataconn.Close();

     dataconn = null;

   }

}

Your problem is that you did not supply a DeleteCommand on your MyCommand object.



When you create your MyCommand object like this:



MyCommand = new ADODataSetCommand(SQL, dataconn);



The SQL parameter that you pass in gets stored as the SelectCommand of your MyCommand object.  The ADODataSetCommand object also has
an UpdateCommand, DeleteCommand, and an InsertCommand.  You need to specify the SQL strings for these if you intend to use them. 
Try this:



try {

  dataconn = new ADOConnection(connstr);

  dataconn.Open();

  MyCommand = new ADODataSetCommand(SQL, dataconn); 

  //Add a DeleteCommand

  MyCommand.DeleteCommand = new ADOCommand("{Your delete command}", dataconn);



  ds = new DataSet();	

  MyCommand.FillDataSet(ds, "Expressions");     

}









-----Original Message-----

From: alex.lehmberg@i... [mailto:alex.lehmberg@i...]

Sent: Tuesday, November 28, 2000 6:18 AM

To: ASP+

Subject: [aspx] Trouble updating a database with a dataset





I'm using a dataset created from an Access database. After deleting one of

the rows in tha dataset I try to save the new dataset back to the

database. But next time I look in the database it looks the same. For some

reason it is not updated. Below is a transcript of my code.

Could anyone please help me spot what I've been doing wrong



String connstr = @"Provider=Microsoft.JET.OLEDB.4.0; Data

Source=c:\datastores\RegEx.mdb";

ADOConnection dataconn = null;

DataSet ds = null;

String SQL = "select * from RegEx";

ADODataSetCommand MyCommand = null;

ADODataSetCommand MyCommand1 = null;



try {

  dataconn = new ADOConnection(connstr);

  dataconn.Open();

  MyCommand = new ADODataSetCommand(SQL, dataconn);   

  ds = new DataSet();

  MyCommand.FillDataSet(ds, "Expressions");     

}

catch (Exception e) {

  e.ToString();

}

finally {

  if(dataconn != null) {

    dataconn.Close();

    dataconn = null;

   }

}

String name = "Testname";

DataTable table = ds.Tables["Expressions"];

DataRow[] rows = table.Select("Name = '" + name + "'");

DataRow editrow = rows[0];

editrow.BeginEdit();

editrow.Delete();

editrow.AcceptChanges();



try {

  dataconn = new ADOConnection(connstr);

  dataconn.Open();

  MyCommand.Update(ds,"Expressions");

  ds.AcceptChanges();

}

catch (Exception e) {

  e.ToString();

}

finally {

  if(dataconn != null) {

     dataconn.Close();

     dataconn = null;

   }

}

Thanks for the response.



There is just one thing I don't understand. According to the SDK

documentation, the update or delete commands should be generated

automatically, when the datasetcommand.Update is called if the dataset

contains primary key information. The database that I read into the

dataset contains a primary key, so why doesn't it work?

I would have to work up an example and give it a shot myself.  Right now, 

I don't know why it's not working for you.  I approached the problem 

originally by supplying my own, so I never saw the problem that you are 

seeing.



Scott Davis

MCSD, MCP

International Parts

Decision Consultants Inc.





-----Original Message-----

From: alex.lehmberg@i... [mailto:alex.lehmberg@i...]

Sent: Wednesday, November 29, 2000 3:59 AM

To: ASP+

Subject: [aspx] RE: Trouble updating a database with a dataset





Thanks for the response.



There is just one thing I don't understand. According to the SDK

documentation, the update or delete commands should be generated

automatically, when the datasetcommand.Update is called if the dataset

contains primary key information. The database that I read into the

dataset contains a primary key, so why doesn't it work?