Re: Updated Primary Key (Identity) Value

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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@xxxxxxxxxxxxxxxxx> wrote in message news:FBpri.16581$_d2.8541@xxxxxxxxxxxx
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@xxxxxxxxxxxxx> wrote in message news:OFF4fyg0HHA.4816@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxx> wrote in message news:OzkDVDH0HHA.464@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxx> wrote in message news:8Joqi.7975$_d2.6876@xxxxxxxxxxxx
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







.

Relevant Pages

  • Re: A pk is *both* a physical and a logical object.
    ... indivdual at some database values. ... I repeat, what does it matter? ... significantly limits the expressiveness of the model, ... expressions that can be used to denote individuals, ...
    (comp.databases.theory)
  • Re: Access is just to hard to understand
    ... "No matter what the database was required to do". ... >> Your Resource For Help With Access, Excel And Word Applications ... we'll be done for extortion. ...
    (microsoft.public.access.gettingstarted)
  • Re: walking a directory with very many files
    ... all .jpg files between 40 and 250KB with the path stored in a ... Why not put the images themselves into database fields? ... on modern filesystems it doesn't matter whether you accessing ... that's not the case with filesystems like FreeBSD ufs2 (at least ...
    (comp.lang.python)
  • Re: Posting to another page attribute: PostTargetUrl
    ... In .NET it is a simple matter to respond to the OnClick event, ... data from simple server controls and store in the database. ... >> context, saving to a temporary table in the database between each page, ...
    (microsoft.public.dotnet.general)
  • Re: Posting to another page attribute: PostTargetUrl
    ... In .NET it is a simple matter to respond to the OnClick event, ... data from simple server controls and store in the database. ... >> context, saving to a temporary table in the database between each page, ...
    (microsoft.public.dotnet.framework.aspnet)