Re: Updated Primary Key (Identity) Value by Jim
Jim
Thu Aug 02 17:35:14 CDT 2007
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
"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
>>>>>
>>>>
>>>>
>>>
>>>
>>
>