| Groups | Blog | Home |
dotnet ado.net : Updated Primary Key (Identity) Value
Take a look at your data adapter and how the INSERT statement is configured.
It has to be: INSERT INTO Matter file_no, date_created, date_modified; SELECT m_id FROM Matter WHERE m_id = SCOPE_IDENTITY() In order to get this to work with SQL Server, you also have to add code that looks like this to add an event handler for the row updated event of the underlying data adapter. da.RowUpdated += new System.Data.SqlClient.SqlRowUpdatedEventHandler(da_RowUpdated); void da_RowUpdated(object sender, System.Data.SqlClient.SqlRowUpdatedEventArgs e) { if (e.StatementType == System.Data.StatementType.Insert) e.Status = System.Data.UpdateStatus.SkipCurrentRow; } If you are using table adapters, you have to expose the underlying data adapter with the partial class. [quoted text, click to view] "Lucas Bussey" <lucas@mtsinc.no.spam.ca> wrote in message news:8Joqi.7975$_d2.6876@pd7urf3no... > To start, I'm using VB 2005 and SQL Server Express 2005. > > This almost appears to be a bug, but I've yet to find an actual bug and > generally narrow my issues down to user-error on my part. I have the > following code snippet (LogFile is a class I've created simply to output > to > a textfile): > > --- > Dim ta As New mattersTableAdapter > Dim dt As New mattersDataTable > Dim dr As mattersRow > > LogFile.WriteLine("Adding new matter...") > > 'Fill data table > ta.Fill(dt) > > 'Add new data row and update > dr = dt.NewmattersRow > dr.Item("file_no") = "New Matter" > dr.Item("date_created") = Now > dr.Item("date_modified") = Now > dt.AddmattersRow(dr) > ta.Update(dt) > dt.AcceptChanges() > > LogFile.WriteLine("New matter added. Id: {0}.", > dr.Item("m_id").ToString) > --- > > As you can probably already tell I'm using a Strongly Typed Dataset. The > table schema includes "m_id" being the Primary Key and has the property > "Identity Specification" set to Yes and increments by 1. > > My issue is that if the DataTable is empty after being filled from the > database (ie: the database table is empty) I get 0 (zero) returned to > "m_id" > in the DataRow. If the DataTable already contains existing records and I > run > this code, I get the appropriate, incremented value. > > Is there something I'm missing? > > Thanks in advance for any help... > > Luke >
To start, I'm using VB 2005 and SQL Server Express 2005.
This almost appears to be a bug, but I've yet to find an actual bug and generally narrow my issues down to user-error on my part. I have the following code snippet (LogFile is a class I've created simply to output to a textfile): --- Dim ta As New mattersTableAdapter Dim dt As New mattersDataTable Dim dr As mattersRow LogFile.WriteLine("Adding new matter...") 'Fill data table ta.Fill(dt) 'Add new data row and update dr = dt.NewmattersRow dr.Item("file_no") = "New Matter" dr.Item("date_created") = Now dr.Item("date_modified") = Now dt.AddmattersRow(dr) ta.Update(dt) dt.AcceptChanges() LogFile.WriteLine("New matter added. Id: {0}.", dr.Item("m_id").ToString) --- As you can probably already tell I'm using a Strongly Typed Dataset. The table schema includes "m_id" being the Primary Key and has the property "Identity Specification" set to Yes and increments by 1. My issue is that if the DataTable is empty after being filled from the database (ie: the database table is empty) I get 0 (zero) returned to "m_id" in the DataRow. If the DataTable already contains existing records and I run this code, I get the appropriate, incremented value. Is there something I'm missing? Thanks in advance for any help... Luke
Woops,
INSERT INTO Matter file_no, date_created, date_modified VALUES(@file_no, @date_created, @date_modified); SELECT m_id FROM Matter WHERE m_id = SCOPE_IDENTITY() [quoted text, click to view] "Jim Rand" <jimrand@ix.netcom.com> wrote in message news:OzkDVDH0HHA.464@TK2MSFTNGP02.phx.gbl... > Take a look at your data adapter and how the INSERT statement is > configured. > > It has to be: > INSERT INTO Matter file_no, date_created, date_modified; SELECT m_id FROM > Matter WHERE m_id = SCOPE_IDENTITY() > > In order to get this to work with SQL Server, you also have to add code > that looks like this to add an event handler for the row updated event of > the underlying data adapter. > > da.RowUpdated += new > System.Data.SqlClient.SqlRowUpdatedEventHandler(da_RowUpdated); > > void da_RowUpdated(object sender, > System.Data.SqlClient.SqlRowUpdatedEventArgs e) > { > if (e.StatementType == System.Data.StatementType.Insert) e.Status = > System.Data.UpdateStatus.SkipCurrentRow; > } > > If you are using table adapters, you have to expose the underlying data > adapter with the partial class. > > > > "Lucas Bussey" <lucas@mtsinc.no.spam.ca> wrote in message > news:8Joqi.7975$_d2.6876@pd7urf3no... >> To start, I'm using VB 2005 and SQL Server Express 2005. >> >> This almost appears to be a bug, but I've yet to find an actual bug and >> generally narrow my issues down to user-error on my part. I have the >> following code snippet (LogFile is a class I've created simply to output >> to >> a textfile): >> >> --- >> Dim ta As New mattersTableAdapter >> Dim dt As New mattersDataTable >> Dim dr As mattersRow >> >> LogFile.WriteLine("Adding new matter...") >> >> 'Fill data table >> ta.Fill(dt) >> >> 'Add new data row and update >> dr = dt.NewmattersRow >> dr.Item("file_no") = "New Matter" >> dr.Item("date_created") = Now >> dr.Item("date_modified") = Now >> dt.AddmattersRow(dr) >> ta.Update(dt) >> dt.AcceptChanges() >> >> LogFile.WriteLine("New matter added. Id: {0}.", >> dr.Item("m_id").ToString) >> --- >> >> As you can probably already tell I'm using a Strongly Typed Dataset. The >> table schema includes "m_id" being the Primary Key and has the property >> "Identity Specification" set to Yes and increments by 1. >> >> My issue is that if the DataTable is empty after being filled from the >> database (ie: the database table is empty) I get 0 (zero) returned to >> "m_id" >> in the DataRow. If the DataTable already contains existing records and I >> run >> this code, I get the appropriate, incremented value. >> >> Is there something I'm missing? >> >> Thanks in advance for any help... >> >> Luke >> > >
Thanks for the tip, I will be looking into incorporating this. I've used
SCOPE_IDENTITY() before... However, why would the tableadapter return 0 when inserting a row into an empty table? Then, subsequent inserts return the properly incremented value? Thanks again for the insight. Luke [quoted text, click to view] "Jim Rand" <jimrand@ix.netcom.com> wrote in message
news:OFF4fyg0HHA.4816@TK2MSFTNGP04.phx.gbl... > Woops, > > INSERT INTO Matter file_no, date_created, date_modified VALUES(@file_no, > @date_created, @date_modified); SELECT m_id FROM Matter WHERE m_id = > SCOPE_IDENTITY() > > > "Jim Rand" <jimrand@ix.netcom.com> wrote in message > news:OzkDVDH0HHA.464@TK2MSFTNGP02.phx.gbl... >> Take a look at your data adapter and how the INSERT statement is >> configured. >> >> It has to be: >> INSERT INTO Matter file_no, date_created, date_modified; SELECT m_id FROM >> Matter WHERE m_id = SCOPE_IDENTITY() >> >> In order to get this to work with SQL Server, you also have to add code >> that looks like this to add an event handler for the row updated event of >> the underlying data adapter. >> >> da.RowUpdated += new >> System.Data.SqlClient.SqlRowUpdatedEventHandler(da_RowUpdated); >> >> void da_RowUpdated(object sender, >> System.Data.SqlClient.SqlRowUpdatedEventArgs e) >> { >> if (e.StatementType == System.Data.StatementType.Insert) e.Status = >> System.Data.UpdateStatus.SkipCurrentRow; >> } >> >> If you are using table adapters, you have to expose the underlying data >> adapter with the partial class. >> >> >> >> "Lucas Bussey" <lucas@mtsinc.no.spam.ca> wrote in message >> news:8Joqi.7975$_d2.6876@pd7urf3no... >>> To start, I'm using VB 2005 and SQL Server Express 2005. >>> >>> This almost appears to be a bug, but I've yet to find an actual bug and >>> generally narrow my issues down to user-error on my part. I have the >>> following code snippet (LogFile is a class I've created simply to output >>> to >>> a textfile): >>> >>> --- >>> Dim ta As New mattersTableAdapter >>> Dim dt As New mattersDataTable >>> Dim dr As mattersRow >>> >>> LogFile.WriteLine("Adding new matter...") >>> >>> 'Fill data table >>> ta.Fill(dt) >>> >>> 'Add new data row and update >>> dr = dt.NewmattersRow >>> dr.Item("file_no") = "New Matter" >>> dr.Item("date_created") = Now >>> dr.Item("date_modified") = Now >>> dt.AddmattersRow(dr) >>> ta.Update(dt) >>> dt.AcceptChanges() >>> >>> LogFile.WriteLine("New matter added. Id: {0}.", >>> dr.Item("m_id").ToString) >>> --- >>> >>> As you can probably already tell I'm using a Strongly Typed Dataset. The >>> table schema includes "m_id" being the Primary Key and has the property >>> "Identity Specification" set to Yes and increments by 1. >>> >>> My issue is that if the DataTable is empty after being filled from the >>> database (ie: the database table is empty) I get 0 (zero) returned to >>> "m_id" >>> in the DataRow. If the DataTable already contains existing records and I >>> run >>> this code, I get the appropriate, incremented value. >>> >>> Is there something I'm missing? >>> >>> Thanks in advance for any help... >>> >>> Luke >>> >> >> > >
1) Make sure that in your dataset, you have specified that the key field is
an auto increment and both the seed and step are set at -1. 2) The hint that I gave you only applies to Sql Server. 3) For Microsoft Access, refer to page 171 of the book ADO.NET Cookbook published by O'Reilly. Jim [quoted text, click to view] "Lucas Bussey" <lucas@mtsinc.no.spam.ca> wrote in message news:dCrsi.23565$_d2.20513@pd7urf3no... > Just an update: > > I'm getting the same results when using OleDb and an MS Access database > file. The first record added to a table returns 0 as the primary key value > when the primary key field is an auto-incremented one. > > Any thoughts? > > Thanks again, > Luke > > "Lucas Bussey" <lucas@mtsinc.no.spam.ca> wrote in message > news:FBpri.16581$_d2.8541@pd7urf3no... >> Thanks for the tip, I will be looking into incorporating this. I've used >> SCOPE_IDENTITY() before... >> >> However, why would the tableadapter return 0 when inserting a row into an >> empty table? Then, subsequent inserts return the properly incremented >> value? >> >> Thanks again for the insight. >> >> Luke >> >> "Jim Rand" <jimrand@ix.netcom.com> wrote in message >> news:OFF4fyg0HHA.4816@TK2MSFTNGP04.phx.gbl... >>> Woops, >>> >>> INSERT INTO Matter file_no, date_created, date_modified VALUES(@file_no, >>> @date_created, @date_modified); SELECT m_id FROM Matter WHERE m_id = >>> SCOPE_IDENTITY() >>> >>> >>> "Jim Rand" <jimrand@ix.netcom.com> wrote in message >>> news:OzkDVDH0HHA.464@TK2MSFTNGP02.phx.gbl... >>>> Take a look at your data adapter and how the INSERT statement is >>>> configured. >>>> >>>> It has to be: >>>> INSERT INTO Matter file_no, date_created, date_modified; SELECT m_id >>>> FROM Matter WHERE m_id = SCOPE_IDENTITY() >>>> >>>> In order to get this to work with SQL Server, you also have to add code >>>> that looks like this to add an event handler for the row updated event >>>> of the underlying data adapter. >>>> >>>> da.RowUpdated += new >>>> System.Data.SqlClient.SqlRowUpdatedEventHandler(da_RowUpdated); >>>> >>>> void da_RowUpdated(object sender, >>>> System.Data.SqlClient.SqlRowUpdatedEventArgs e) >>>> { >>>> if (e.StatementType == System.Data.StatementType.Insert) e.Status = >>>> System.Data.UpdateStatus.SkipCurrentRow; >>>> } >>>> >>>> If you are using table adapters, you have to expose the underlying data >>>> adapter with the partial class. >>>> >>>> >>>> >>>> "Lucas Bussey" <lucas@mtsinc.no.spam.ca> wrote in message >>>> news:8Joqi.7975$_d2.6876@pd7urf3no... >>>>> To start, I'm using VB 2005 and SQL Server Express 2005. >>>>> >>>>> This almost appears to be a bug, but I've yet to find an actual bug >>>>> and >>>>> generally narrow my issues down to user-error on my part. I have the >>>>> following code snippet (LogFile is a class I've created simply to >>>>> output to >>>>> a textfile): >>>>> >>>>> --- >>>>> Dim ta As New mattersTableAdapter >>>>> Dim dt As New mattersDataTable >>>>> Dim dr As mattersRow >>>>> >>>>> LogFile.WriteLine("Adding new matter...") >>>>> >>>>> 'Fill data table >>>>> ta.Fill(dt) >>>>> >>>>> 'Add new data row and update >>>>> dr = dt.NewmattersRow >>>>> dr.Item("file_no") = "New Matter" >>>>> dr.Item("date_created") = Now >>>>> dr.Item("date_modified") = Now >>>>> dt.AddmattersRow(dr) >>>>> ta.Update(dt) >>>>> dt.AcceptChanges() >>>>> >>>>> LogFile.WriteLine("New matter added. Id: {0}.", >>>>> dr.Item("m_id").ToString) >>>>> --- >>>>> >>>>> As you can probably already tell I'm using a Strongly Typed Dataset. >>>>> The >>>>> table schema includes "m_id" being the Primary Key and has the >>>>> property >>>>> "Identity Specification" set to Yes and increments by 1. >>>>> >>>>> My issue is that if the DataTable is empty after being filled from the >>>>> database (ie: the database table is empty) I get 0 (zero) returned to >>>>> "m_id" >>>>> in the DataRow. If the DataTable already contains existing records and >>>>> I run >>>>> this code, I get the appropriate, incremented value. >>>>> >>>>> Is there something I'm missing? >>>>> >>>>> Thanks in advance for any help... >>>>> >>>>> Luke >>>>> >>>> >>>> >>> >>> >> >
Just an update:
I'm getting the same results when using OleDb and an MS Access database file. The first record added to a table returns 0 as the primary key value when the primary key field is an auto-incremented one. Any thoughts? Thanks again, Luke [quoted text, click to view] "Lucas Bussey" <lucas@mtsinc.no.spam.ca> wrote in message
news:FBpri.16581$_d2.8541@pd7urf3no... > Thanks for the tip, I will be looking into incorporating this. I've used > SCOPE_IDENTITY() before... > > However, why would the tableadapter return 0 when inserting a row into an > empty table? Then, subsequent inserts return the properly incremented > value? > > Thanks again for the insight. > > Luke > > "Jim Rand" <jimrand@ix.netcom.com> wrote in message > news:OFF4fyg0HHA.4816@TK2MSFTNGP04.phx.gbl... >> Woops, >> >> INSERT INTO Matter file_no, date_created, date_modified VALUES(@file_no, >> @date_created, @date_modified); SELECT m_id FROM Matter WHERE m_id = >> SCOPE_IDENTITY() >> >> >> "Jim Rand" <jimrand@ix.netcom.com> wrote in message >> news:OzkDVDH0HHA.464@TK2MSFTNGP02.phx.gbl... >>> Take a look at your data adapter and how the INSERT statement is >>> configured. >>> >>> It has to be: >>> INSERT INTO Matter file_no, date_created, date_modified; SELECT m_id >>> FROM Matter WHERE m_id = SCOPE_IDENTITY() >>> >>> In order to get this to work with SQL Server, you also have to add code >>> that looks like this to add an event handler for the row updated event >>> of the underlying data adapter. >>> >>> da.RowUpdated += new >>> System.Data.SqlClient.SqlRowUpdatedEventHandler(da_RowUpdated); >>> >>> void da_RowUpdated(object sender, >>> System.Data.SqlClient.SqlRowUpdatedEventArgs e) >>> { >>> if (e.StatementType == System.Data.StatementType.Insert) e.Status = >>> System.Data.UpdateStatus.SkipCurrentRow; >>> } >>> >>> If you are using table adapters, you have to expose the underlying data >>> adapter with the partial class. >>> >>> >>> >>> "Lucas Bussey" <lucas@mtsinc.no.spam.ca> wrote in message >>> news:8Joqi.7975$_d2.6876@pd7urf3no... >>>> To start, I'm using VB 2005 and SQL Server Express 2005. >>>> >>>> This almost appears to be a bug, but I've yet to find an actual bug and >>>> generally narrow my issues down to user-error on my part. I have the >>>> following code snippet (LogFile is a class I've created simply to >>>> output to >>>> a textfile): >>>> >>>> --- >>>> Dim ta As New mattersTableAdapter >>>> Dim dt As New mattersDataTable >>>> Dim dr As mattersRow >>>> >>>> LogFile.WriteLine("Adding new matter...") >>>> >>>> 'Fill data table >>>> ta.Fill(dt) >>>> >>>> 'Add new data row and update >>>> dr = dt.NewmattersRow >>>> dr.Item("file_no") = "New Matter" >>>> dr.Item("date_created") = Now >>>> dr.Item("date_modified") = Now >>>> dt.AddmattersRow(dr) >>>> ta.Update(dt) >>>> dt.AcceptChanges() >>>> >>>> LogFile.WriteLine("New matter added. Id: {0}.", >>>> dr.Item("m_id").ToString) >>>> --- >>>> >>>> As you can probably already tell I'm using a Strongly Typed Dataset. >>>> The >>>> table schema includes "m_id" being the Primary Key and has the property >>>> "Identity Specification" set to Yes and increments by 1. >>>> >>>> My issue is that if the DataTable is empty after being filled from the >>>> database (ie: the database table is empty) I get 0 (zero) returned to >>>> "m_id" >>>> in the DataRow. If the DataTable already contains existing records and >>>> I run >>>> this code, I get the appropriate, incremented value. >>>> >>>> Is there something I'm missing? >>>> >>>> Thanks in advance for any help... >>>> >>>> Luke >>>> >>> >>> >> >> >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |