R
rtennys
I ran into a problem when I tried to use SqlCommandBuilder to generate
my insert statement. The problem was the identity column wasn't being
set when I called Update. It was set correctly in the database, it
just wasn't updating my inserted DataRow. This caused issues when I
went to add rows in other tables that keyed off the one I just inserted
because I didn't have the primary key.
Long story short... I searched high and low and was finally able to
piece it together between what I found in these newsgroups and the MSDN
documentation. I thought I'd share how simple it ended up being
because I saw some crazy work-arounds in all my searching.
There were two key things that I kept missing somehow. 1. calling
RefeshSchema to clear the command builders handy work and then
resetting the insert command with my mods to the originally generated
one. 2. setting UpdatedRowSource to FirstReturnedRecord. It ended up
being so simple... sheesh! The hours I spent searching.
I hope someone else gets some value out of this.
Rob
// Set up
_tblMain = new DataTable(TBL_PENDING_MAIN);
_cmdBuilderMain = new SqlCommandBuilder(
new SqlDataAdapter("SELECT * FROM " + TBL_PENDING_MAIN, _conn));
_cmdBuilderMain.DataAdapter.FillSchema(_tblMain, SchemaType.Mapped);
// Later after adding a row to the table...
// take care of the pesky bug in the command builder
// (doesn't get the identity colomn)
SqlCommand cmd = _cmdBuilderMain.GetInsertCommand(true);
_cmdBuilderMain.RefreshSchema(); // Clears the generated commands
cmd.CommandText += "; SELECT SCOPE_IDENTITY() AS PIF_ID;";
cmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
_cmdBuilderMain.DataAdapter.InsertCommand = cmd;
_cmdBuilderMain.DataAdapter.Update(_tblMain);
// now _tblMain.Rows[0]["PIF_ID"] is correctly set!!
my insert statement. The problem was the identity column wasn't being
set when I called Update. It was set correctly in the database, it
just wasn't updating my inserted DataRow. This caused issues when I
went to add rows in other tables that keyed off the one I just inserted
because I didn't have the primary key.
Long story short... I searched high and low and was finally able to
piece it together between what I found in these newsgroups and the MSDN
documentation. I thought I'd share how simple it ended up being
because I saw some crazy work-arounds in all my searching.
There were two key things that I kept missing somehow. 1. calling
RefeshSchema to clear the command builders handy work and then
resetting the insert command with my mods to the originally generated
one. 2. setting UpdatedRowSource to FirstReturnedRecord. It ended up
being so simple... sheesh! The hours I spent searching.
I hope someone else gets some value out of this.
Rob
// Set up
_tblMain = new DataTable(TBL_PENDING_MAIN);
_cmdBuilderMain = new SqlCommandBuilder(
new SqlDataAdapter("SELECT * FROM " + TBL_PENDING_MAIN, _conn));
_cmdBuilderMain.DataAdapter.FillSchema(_tblMain, SchemaType.Mapped);
// Later after adding a row to the table...
// take care of the pesky bug in the command builder
// (doesn't get the identity colomn)
SqlCommand cmd = _cmdBuilderMain.GetInsertCommand(true);
_cmdBuilderMain.RefreshSchema(); // Clears the generated commands
cmd.CommandText += "; SELECT SCOPE_IDENTITY() AS PIF_ID;";
cmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
_cmdBuilderMain.DataAdapter.InsertCommand = cmd;
_cmdBuilderMain.DataAdapter.Update(_tblMain);
// now _tblMain.Rows[0]["PIF_ID"] is correctly set!!