|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
DataAdapter update not including all parametersI have some code which takes a datatable, makes some changes to the rows, and uses the dataadapter.update(datatable) command to write the changes back to the db. When I look at the changed rows in the datatable (and export them out to xml), the updated fields are present. When I capture the UpdatingEvent thrown by the dataadapter, the updatecommand is missing one of my parameters. Because of this, the changes don't make it into the database. What would cause a parameter to NOT be included in the update command? The field does allow nulls and every other field in the table is updating fine, so I know the transaction is committing. Thanks in advance for your help! -Kevin Code (some of the variable declarations moved for brevity): public void updateProcedure(ref SqlTransaction tran) //transaction object passed into this method //create the datatable to be updated and dataadapter updateDataAdapter = new SqlDataAdapter(); updateDataAdapter.SelectCommand = new SqlCommand(sqlStatement, cn, tran); SqlCommandBuilder custCB = new SQLCommandBuilder(updateDataAdapter); DataTable updateTable = new DataTable(); updateDataAdapter.Fill(updateTable) //call method to make some changes to the updateTable //now that the table has been update, write to xml to confirm changes DataTable xDataTable = updateTable.GetChanges(); WriteXMLToFile(xDataTable, "c:\temp\DataTableChanges.xml"); //perform the update //add update handlers updateDataAdapter.RowUpdating += new SqlRowUpdatingEventHandler(OnRowUpdating); updateDataAdapter.RowUpdated += new SqlRowUpdatedEventHandler( OnRowUpdated ); //call the update method updateDataAdapter.Update(updateTable); //remove handlers updateDataAdapter.RowUpdating -= new SqlRowUpdatingEventHandler(OnRowUpdating); updateDataAdapter.RowUpdated -= new SqlRowUpdatedEventHandler( OnRowUpdated ); //this method exits and the transaction object which was passed in is committed. What do you mean by "Missing" is it not there or is a value not present.
Show quote "kwgainey" <kevin.gai***@gmail.com> wrote in message news:1137093414.314807.3990@o13g2000cwo.googlegroups.com... > Hi folks, > > I have some code which takes a datatable, makes some changes to the > rows, and uses the dataadapter.update(datatable) command to write the > changes back to the db. When I look at the changed rows in the > datatable (and export them out to xml), the updated fields are present. > When I capture the UpdatingEvent thrown by the dataadapter, the > updatecommand is missing one of my parameters. Because of this, the > changes don't make it into the database. > > What would cause a parameter to NOT be included in the update command? > The field does allow nulls and every other field in the table is > updating fine, so I know the transaction is committing. Thanks in > advance for your help! > > -Kevin > > Code (some of the variable declarations moved for brevity): > > public void updateProcedure(ref SqlTransaction tran) > //transaction object passed into this method > > //create the datatable to be updated and dataadapter > updateDataAdapter = new SqlDataAdapter(); > updateDataAdapter.SelectCommand = new SqlCommand(sqlStatement, cn, > tran); > SqlCommandBuilder custCB = new SQLCommandBuilder(updateDataAdapter); > DataTable updateTable = new DataTable(); > updateDataAdapter.Fill(updateTable) > > //call method to make some changes to the updateTable > > //now that the table has been update, write to xml to confirm changes > DataTable xDataTable = updateTable.GetChanges(); > WriteXMLToFile(xDataTable, "c:\temp\DataTableChanges.xml"); > > //perform the update > //add update handlers > updateDataAdapter.RowUpdating += new > SqlRowUpdatingEventHandler(OnRowUpdating); > updateDataAdapter.RowUpdated += new SqlRowUpdatedEventHandler( > OnRowUpdated ); > > //call the update method > updateDataAdapter.Update(updateTable); > > //remove handlers > updateDataAdapter.RowUpdating -= new > SqlRowUpdatingEventHandler(OnRowUpdating); > updateDataAdapter.RowUpdated -= new SqlRowUpdatedEventHandler( > OnRowUpdated ); > > //this method exits and the transaction object which was passed in is > committed. > Sorry for the confusion. When I say missing, I mean that neither the
fieldname or a value to populate it is in the UpdateCommand string. For example, I have 15 fields (not including the primary key) in the database schema. The updateTable object shows all 15 fields. When ..NET populates the UpdateCommand.CommandText property, only 14 of the updateable fields are in the SET or WHERE sections of the SQL statement. It truly is missing and does not show up at all in the Command objects Parameters collection. Let me know if that makes sense or you need additional info. Thanks in advance! hejdig.
It sounds like the adapter and your tables are out of sync. Check: DataTable has the right columns with the right types and the right allowNull and map to the right DatabaseTableColumn. UpdateCommand SQL string looks right. Check the parameter collection that the names are right and the types are right, a tedious task. Run the Update command from the wizard. Or If it is a simple TableAdapter - recreate it; probably faster. HTH /OF Show quote >"kwgainey" <kevin.gai***@gmail.com> wrote > Sorry for the confusion. When I say missing, I mean that neither the > fieldname or a value to populate it is in the UpdateCommand string. > For example, I have 15 fields (not including the primary key) in the > database schema. The updateTable object shows all 15 fields. When > .NET populates the UpdateCommand.CommandText property, only 14 of the > updateable fields are in the SET or WHERE sections of the SQL > statement. It truly is missing and does not show up at all in the > Command objects Parameters collection. Let me know if that makes sense > or you need additional info. Thanks in advance! Kwgainey,
Can it be that there is a timestamp field including your columns? Cor Cor,
There are no timestamp fields in this table. Plus, other columns in the table update fine. Thanks. how does the UpdateCommand get created? If you are using the design time
wizards, then maybe you changed the database schema after the fact, or maybe you just need to rerun the wizard. I would also try to see what is different about the column being omitted. Show quote "kwgainey" <kevin.gai***@gmail.com> wrote in message news:1137093414.314807.3990@o13g2000cwo.googlegroups.com... > Hi folks, > > I have some code which takes a datatable, makes some changes to the > rows, and uses the dataadapter.update(datatable) command to write the > changes back to the db. When I look at the changed rows in the > datatable (and export them out to xml), the updated fields are present. > When I capture the UpdatingEvent thrown by the dataadapter, the > updatecommand is missing one of my parameters. Because of this, the > changes don't make it into the database. > > What would cause a parameter to NOT be included in the update command? > The field does allow nulls and every other field in the table is > updating fine, so I know the transaction is committing. Thanks in > advance for your help! > > -Kevin > > Code (some of the variable declarations moved for brevity): > > public void updateProcedure(ref SqlTransaction tran) > //transaction object passed into this method > > //create the datatable to be updated and dataadapter > updateDataAdapter = new SqlDataAdapter(); > updateDataAdapter.SelectCommand = new SqlCommand(sqlStatement, cn, > tran); > SqlCommandBuilder custCB = new SQLCommandBuilder(updateDataAdapter); > DataTable updateTable = new DataTable(); > updateDataAdapter.Fill(updateTable) > > //call method to make some changes to the updateTable > > //now that the table has been update, write to xml to confirm changes > DataTable xDataTable = updateTable.GetChanges(); > WriteXMLToFile(xDataTable, "c:\temp\DataTableChanges.xml"); > > //perform the update > //add update handlers > updateDataAdapter.RowUpdating += new > SqlRowUpdatingEventHandler(OnRowUpdating); > updateDataAdapter.RowUpdated += new SqlRowUpdatedEventHandler( > OnRowUpdated ); > > //call the update method > updateDataAdapter.Update(updateTable); > > //remove handlers > updateDataAdapter.RowUpdating -= new > SqlRowUpdatingEventHandler(OnRowUpdating); > updateDataAdapter.RowUpdated -= new SqlRowUpdatedEventHandler( > OnRowUpdated ); > > //this method exits and the transaction object which was passed in is > committed. >  |
|||||||||||||||||||||||
Other interesting topics