Updated Primary Key (Identity) Value

[Lucas Bussey]

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

 

[Jim Rand]

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.

 

[Jim Rand]

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()

 

[Lucas Bussey]

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

 

[Lucas Bussey]

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

 

[Jim Rand]

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