Re: SqlCommandBuilder - not setting my identity column
- From: "William \(Bill\) Vaughn" <billvaRemoveThis@xxxxxxxxxx>
- Date: Sun, 20 Aug 2006 21:12:34 -0700
See my blog for a reply.
http://betav.com/blog/billva/2006/08/question_of_the_day_commandbui.html
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
<rtennys@xxxxxxxxxxx> wrote in message
news:1155874502.508732.117540@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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!!
.
- References:
- SqlCommandBuilder - not setting my identity column
- From: rtennys
- SqlCommandBuilder - not setting my identity column
- Prev by Date: Re: OleDb vs. Sql Interoperability
- Next by Date: Re: OleDb vs. Sql Interoperability
- Previous by thread: SqlCommandBuilder - not setting my identity column
- Next by thread: Stored Proc problem
- Index(es):
Relevant Pages
|
