Need Help with Database Update using DataAdaptor
-
Greetings: I am working my way through the MCSD "Developing Windows Based Apps" book. I'm in the section on databases and ADO. This chapter is woefully lacking in working examples so I am trying to contrive my own in order to see some of this stuff in action. To do this, I am connecting to the Northwind database supplied with Access in order to have a database to play with. OK, so I have successfully connected to Northwind, I have used data readers and data adaptors to load tables from Northwind into data sets. I have successfully displayed the data using data grids. I have also successfully executed non querys, scalars and readers. Eveything is going tickety-boo. However, the last thing on my agenda before moving on is to ADD a row to one of the tables and then actually write it back to the Northwind database file. To do this, I have fashioned a simple input form and when I get all the required input fields, I return from the form and execute the following:
private void btnAddSupplier_Click(object sender, System.EventArgs e) { AddSupplier frmAdd = new AddSupplier(); frmAdd.ShowDialog( this ); if (frmAdd.AddSelected == false) return; oleDbDataAdapter_Suppliers.Fill( dataSet_Suppliers ); DataRow dr = dataSet_Suppliers.Tables[0].NewRow(); dr["SupplierID"] = System.DBNull.Value; dr["CompanyName"] = frmAdd.CompanyName; dr["ContactName"] = frmAdd.ContactName; dr["ContactTitle"] = frmAdd.ContactTitle; dr["Address"] = frmAdd.Address; dr["Region"] = frmAdd.City; dr["City"] = frmAdd.RegionProv; dr["PostalCode"] = frmAdd.Postal; dr["Country"] = frmAdd.Country; dr["Phone"] = ""; dr["FAX"] = ""; dr["HomePage"] = ""; oleDbDataAdapter_Suppliers.Update( dataSet_Suppliers ); dataSet_Suppliers.Tables[0].Rows.Add( dr ); dr.AcceptChanges(); }"dataSet_Suppliers" and "oleDbDataAdaptor_Suppliers" are both members of the main form that I am using for my experimentation scaffold. I am certain that the adaptor and data set are successfully loaded with the Supplier table. I am able to Fill the supplier data set through the data adaptor and bind the data set to a data grid and display it. The problem is that the above code does not write the new row to the database file. It appears unchanged. The code executes OK, but the changes are not applied to the file. I am confused about the order in which I should call "AcceptChanges" for the DataRow and "Update" for the data adaptor. The MCSD book says that you must call "Update" on the data adaptor FIRST. I -
Greetings: I am working my way through the MCSD "Developing Windows Based Apps" book. I'm in the section on databases and ADO. This chapter is woefully lacking in working examples so I am trying to contrive my own in order to see some of this stuff in action. To do this, I am connecting to the Northwind database supplied with Access in order to have a database to play with. OK, so I have successfully connected to Northwind, I have used data readers and data adaptors to load tables from Northwind into data sets. I have successfully displayed the data using data grids. I have also successfully executed non querys, scalars and readers. Eveything is going tickety-boo. However, the last thing on my agenda before moving on is to ADD a row to one of the tables and then actually write it back to the Northwind database file. To do this, I have fashioned a simple input form and when I get all the required input fields, I return from the form and execute the following:
private void btnAddSupplier_Click(object sender, System.EventArgs e) { AddSupplier frmAdd = new AddSupplier(); frmAdd.ShowDialog( this ); if (frmAdd.AddSelected == false) return; oleDbDataAdapter_Suppliers.Fill( dataSet_Suppliers ); DataRow dr = dataSet_Suppliers.Tables[0].NewRow(); dr["SupplierID"] = System.DBNull.Value; dr["CompanyName"] = frmAdd.CompanyName; dr["ContactName"] = frmAdd.ContactName; dr["ContactTitle"] = frmAdd.ContactTitle; dr["Address"] = frmAdd.Address; dr["Region"] = frmAdd.City; dr["City"] = frmAdd.RegionProv; dr["PostalCode"] = frmAdd.Postal; dr["Country"] = frmAdd.Country; dr["Phone"] = ""; dr["FAX"] = ""; dr["HomePage"] = ""; oleDbDataAdapter_Suppliers.Update( dataSet_Suppliers ); dataSet_Suppliers.Tables[0].Rows.Add( dr ); dr.AcceptChanges(); }"dataSet_Suppliers" and "oleDbDataAdaptor_Suppliers" are both members of the main form that I am using for my experimentation scaffold. I am certain that the adaptor and data set are successfully loaded with the Supplier table. I am able to Fill the supplier data set through the data adaptor and bind the data set to a data grid and display it. The problem is that the above code does not write the new row to the database file. It appears unchanged. The code executes OK, but the changes are not applied to the file. I am confused about the order in which I should call "AcceptChanges" for the DataRow and "Update" for the data adaptor. The MCSD book says that you must call "Update" on the data adaptor FIRST. IAdd before update?
A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
-
Add before update?
A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
-
Greetings: I am working my way through the MCSD "Developing Windows Based Apps" book. I'm in the section on databases and ADO. This chapter is woefully lacking in working examples so I am trying to contrive my own in order to see some of this stuff in action. To do this, I am connecting to the Northwind database supplied with Access in order to have a database to play with. OK, so I have successfully connected to Northwind, I have used data readers and data adaptors to load tables from Northwind into data sets. I have successfully displayed the data using data grids. I have also successfully executed non querys, scalars and readers. Eveything is going tickety-boo. However, the last thing on my agenda before moving on is to ADD a row to one of the tables and then actually write it back to the Northwind database file. To do this, I have fashioned a simple input form and when I get all the required input fields, I return from the form and execute the following:
private void btnAddSupplier_Click(object sender, System.EventArgs e) { AddSupplier frmAdd = new AddSupplier(); frmAdd.ShowDialog( this ); if (frmAdd.AddSelected == false) return; oleDbDataAdapter_Suppliers.Fill( dataSet_Suppliers ); DataRow dr = dataSet_Suppliers.Tables[0].NewRow(); dr["SupplierID"] = System.DBNull.Value; dr["CompanyName"] = frmAdd.CompanyName; dr["ContactName"] = frmAdd.ContactName; dr["ContactTitle"] = frmAdd.ContactTitle; dr["Address"] = frmAdd.Address; dr["Region"] = frmAdd.City; dr["City"] = frmAdd.RegionProv; dr["PostalCode"] = frmAdd.Postal; dr["Country"] = frmAdd.Country; dr["Phone"] = ""; dr["FAX"] = ""; dr["HomePage"] = ""; oleDbDataAdapter_Suppliers.Update( dataSet_Suppliers ); dataSet_Suppliers.Tables[0].Rows.Add( dr ); dr.AcceptChanges(); }"dataSet_Suppliers" and "oleDbDataAdaptor_Suppliers" are both members of the main form that I am using for my experimentation scaffold. I am certain that the adaptor and data set are successfully loaded with the Supplier table. I am able to Fill the supplier data set through the data adaptor and bind the data set to a data grid and display it. The problem is that the above code does not write the new row to the database file. It appears unchanged. The code executes OK, but the changes are not applied to the file. I am confused about the order in which I should call "AcceptChanges" for the DataRow and "Update" for the data adaptor. The MCSD book says that you must call "Update" on the data adaptor FIRST. I -
Suggestion but untested one. The ? absolves me of responsibility for it not working :p
A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
-
try the following code... DataRowView NewRow = DataSet.Tables[TableName].DefaultView.AddNew() NewRow[ColumnName] = ""; NewRow.EndEdit(); DataAdapter.Update(DataSet, TableName); bahaa
OK, so my code now looks like this:
DataRowView drv = dataSet_Suppliers.Tables[0].DefaultView.AddNew(); drv["SupplierID"] = System.DBNull.Value; ... more field assignments here ... drv["HomePage"] = ""; drv.EndEdit(); oleDbDataAdapter_Suppliers.Update( dataSet_Suppliers, "Suppliers" );When I call the "Update" command, an OleDbException is thrown. Any other thoughts? Mark -
Suggestion but untested one. The ? absolves me of responsibility for it not working :p
A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
Hi Ennis: I tried your suggestion. Now the lower part of my code block looks like this:
dr["HomePage"] = ""; dataSet_Suppliers.Tables[0].Rows.Add( dr ); oleDbDataAdapter_Suppliers.Update( dataSet_Suppliers ); dr.AcceptChanges();The call to "Update" now throws an OleDbException. Thanks for your help anyway. If you have any other thoughts, I'm all ears... Mark -
Hi Ennis: I tried your suggestion. Now the lower part of my code block looks like this:
dr["HomePage"] = ""; dataSet_Suppliers.Tables[0].Rows.Add( dr ); oleDbDataAdapter_Suppliers.Update( dataSet_Suppliers ); dr.AcceptChanges();The call to "Update" now throws an OleDbException. Thanks for your help anyway. If you have any other thoughts, I'm all ears... Markmove accept up one line. BTW, what are the specifics of the exception.
A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
-
OK, so my code now looks like this:
DataRowView drv = dataSet_Suppliers.Tables[0].DefaultView.AddNew(); drv["SupplierID"] = System.DBNull.Value; ... more field assignments here ... drv["HomePage"] = ""; drv.EndEdit(); oleDbDataAdapter_Suppliers.Update( dataSet_Suppliers, "Suppliers" );When I call the "Update" command, an OleDbException is thrown. Any other thoughts? Mark