P
pwebster
Scanning through the newsgroups I have noticed several questions about
da.update method not working when used on datatables created with SQL JOIN
statements (mostly questions about populating datagrid or listview then
modifying the data and needing to update SQLCE tables) This is by design
because the DataAdapter is not smart enough to figure out what table should
be updated.
I use two different methods to work around this issue. I am posting because
I would like to hear other developer opinions on which method is best, and I
hope this information will help others resolve similar issues.
Method 1:
I use this method typically when I create a single form with multiple panels
that are hidden or visible depending upon what the user is doing.
Declare the DataAdapter ("daMain"), DataSet , and DataTables as form level
objects (immediately following "Public Class frmName")
When creating the DataAdapter and DataSet, fill the DataSet with multiple
DataTables. One table will use the SQL JOIN statement which will be used as
the data source for a datagrid or listview (This table is not necessarily
declared as a form level object). The remaining DataTables will be
something like "SELECT * FROM tblCustomer WHERE ...". These DataTables are
the ones which need to be updated and will be declared at the top of the
form.
After binding the datagrid, the user makes a selection and is taken to a
hidden panel on same form to edit the data. After editing the data, I
declare a new DataRow and use the DataTable.Find method to find the record
that needs to be updated. I update the appropriate columns (Items) in the
found DataRow and declare a New SqlCeCommandBuilder(daMain) then call the
DataAdapter.Update(DataTable) on the table or tables (declared at form
level) that require updating.
NOTE: If I have declared the table that is used to populate the datagrid or
listview as a form level object, I make identical modifications to that
DataTable so they are reflected in the datagrid or listview. If this was
not done, I clear the datagrid or listview and populate it after the update.
Method 2:
A more manual approach which is easier to explain but requires more coding,
and I typically use this in applications where I use multiple forms to show
and edit data.
Create the necessary objects and bind the datagrid or listview to the
desired datasource. When a selection is made, the user is taken to the form
for editing. The "edit" form is typically populated using a
SqlCeDataReader. After the data has been modified, use a SqlCeCommand and
ExecuteNonQuery to update the data in the appropriate tables. Return to the
form with the datagrid or listview and clear the datagrid or listview then
rebuild the datatable used for the datasource.
Method 2 is usually slower depending on the number of tables that need to be
updated, the number of records displayed in the datagrid or listview, and
the complexity of the SQL statements.
da.update method not working when used on datatables created with SQL JOIN
statements (mostly questions about populating datagrid or listview then
modifying the data and needing to update SQLCE tables) This is by design
because the DataAdapter is not smart enough to figure out what table should
be updated.
I use two different methods to work around this issue. I am posting because
I would like to hear other developer opinions on which method is best, and I
hope this information will help others resolve similar issues.
Method 1:
I use this method typically when I create a single form with multiple panels
that are hidden or visible depending upon what the user is doing.
Declare the DataAdapter ("daMain"), DataSet , and DataTables as form level
objects (immediately following "Public Class frmName")
When creating the DataAdapter and DataSet, fill the DataSet with multiple
DataTables. One table will use the SQL JOIN statement which will be used as
the data source for a datagrid or listview (This table is not necessarily
declared as a form level object). The remaining DataTables will be
something like "SELECT * FROM tblCustomer WHERE ...". These DataTables are
the ones which need to be updated and will be declared at the top of the
form.
After binding the datagrid, the user makes a selection and is taken to a
hidden panel on same form to edit the data. After editing the data, I
declare a new DataRow and use the DataTable.Find method to find the record
that needs to be updated. I update the appropriate columns (Items) in the
found DataRow and declare a New SqlCeCommandBuilder(daMain) then call the
DataAdapter.Update(DataTable) on the table or tables (declared at form
level) that require updating.
NOTE: If I have declared the table that is used to populate the datagrid or
listview as a form level object, I make identical modifications to that
DataTable so they are reflected in the datagrid or listview. If this was
not done, I clear the datagrid or listview and populate it after the update.
Method 2:
A more manual approach which is easier to explain but requires more coding,
and I typically use this in applications where I use multiple forms to show
and edit data.
Create the necessary objects and bind the datagrid or listview to the
desired datasource. When a selection is made, the user is taken to the form
for editing. The "edit" form is typically populated using a
SqlCeDataReader. After the data has been modified, use a SqlCeCommand and
ExecuteNonQuery to update the data in the appropriate tables. Return to the
form with the datagrid or listview and clear the datagrid or listview then
rebuild the datatable used for the datasource.
Method 2 is usually slower depending on the number of tables that need to be
updated, the number of records displayed in the datagrid or listview, and
the complexity of the SQL statements.