Autoincremented value of the last record entered

  • Thread starter Thread starter Bruce A. Julseth
  • Start date Start date
B

Bruce A. Julseth

I want to find the primary key (autoincremented) of the record I just saved
(i.e.. the last record saved). With SQL, I can do this with "Select Top 1
PrimaryKey from database Order by PrimaryKey desc." How can I do this with
a "RowFilter?"

This //\\ dvRecords.RowFilter = "PrimaryKey = Top 1 PrimaryKey Order By
PrimaryKey desc" //\\ doesn't work. Maybe I have the syntax wrong!!

Appreciate suggestions on using "RowFilter" to find the last records
entered!

Thank you

Bruce
 
Hi,

Using of the "Select Top 1 PrimaryKey from database Order by PrimaryKey
desc." SQL statement to get latest identity value is a pretty bad way to do
this. First of all it will be very expensive because it would require to
sort the whole result. Second, you will not get proper result if other
client will insert new record in between your insert and this select. In
this case your application will be screwed up. Best way is to use SELECT
@@IDENTITY statement or SELECT SCOPE_IDENTITY (depending on a database)
In a case of DataTable newly inserted record is always the last one and you
could check it
 
Val:

Thanks for the response. How do I use "SELECT @@IDENTITY?" I'm a novice in
the area and have never seen @@IDENTITY? As for SELECT SCOPE_IDENTITY, same
story. My database is on an MDSE server. It is relational.

I'm doing my development in VB.NET.

Thanks for the help...

Bruce
 
Val:

One other thing. Yes, I know that the newly inserted record in the DataTable
but, unless I did something wrong, there was no value for the autoincrement
field (PrimaryKey). If the autoincrement value should be there, I'll go back
an check again. That would be what I need.

Thanks...

Bruce
 
Try this:

static void da_OnRowUpdate(object sender, OleDbRowUpdatedEventArgs e)
{
OleDbCommand oCmd = new OleDbCommand("SELECT @@IDENTITY",
e.Command.Connection);
if (e.StatementType == StatementType.Insert)
{

e.Row["myKey"] = oCmd.ExecuteScalar();
e.Row.AcceptChanges();
}
}

Do not forget this somewhere:

da.RowUpdated += new OleDbRowUpdatedEventHandler(da_OnRowUpdate);

Cheers,

Howard
 
These articles could help as well

http://support.microsoft.com/default.aspx?scid=kb;en-us;815629

http://support.microsoft.com/default.aspx?scid=kb;en-us;320141

--
Val Mazur
Microsoft MVP

http://xport.mvps.org



Howard Carpenter said:
Try this:

static void da_OnRowUpdate(object sender, OleDbRowUpdatedEventArgs e)
{
OleDbCommand oCmd = new OleDbCommand("SELECT @@IDENTITY",
e.Command.Connection);
if (e.StatementType == StatementType.Insert)
{

e.Row["myKey"] = oCmd.ExecuteScalar();
e.Row.AcceptChanges();
}
}

Do not forget this somewhere:

da.RowUpdated += new OleDbRowUpdatedEventHandler(da_OnRowUpdate);

Cheers,

Howard



Bruce A. Julseth said:
Val:

One other thing. Yes, I know that the newly inserted record in the
DataTable
but, unless I did something wrong, there was no value for the
autoincrement
field (PrimaryKey). If the autoincrement value should be there, I'll go
back
an check again. That would be what I need.

Thanks...

Bruce
 
Thanks.. These have been very helpful.. Appreciate all taking the time to
help me resolve this problem. This stage is now working. On to the next
"Opportunity!"

Bruce

Val Mazur (MVP) said:
These articles could help as well

http://support.microsoft.com/default.aspx?scid=kb;en-us;815629

http://support.microsoft.com/default.aspx?scid=kb;en-us;320141

--
Val Mazur
Microsoft MVP

http://xport.mvps.org



Howard Carpenter said:
Try this:

static void da_OnRowUpdate(object sender, OleDbRowUpdatedEventArgs e)
{
OleDbCommand oCmd = new OleDbCommand("SELECT @@IDENTITY",
e.Command.Connection);
if (e.StatementType == StatementType.Insert)
{

e.Row["myKey"] = oCmd.ExecuteScalar();
e.Row.AcceptChanges();
}
}

Do not forget this somewhere:

da.RowUpdated += new OleDbRowUpdatedEventHandler(da_OnRowUpdate);

Cheers,

Howard



Bruce A. Julseth said:
Val:

One other thing. Yes, I know that the newly inserted record in the
DataTable
but, unless I did something wrong, there was no value for the
autoincrement
field (PrimaryKey). If the autoincrement value should be there, I'll go
back
an check again. That would be what I need.

Thanks...

Bruce

Hi,

Using of the "Select Top 1 PrimaryKey from database Order by PrimaryKey
desc." SQL statement to get latest identity value is a pretty bad way
to
do
this. First of all it will be very expensive because it would require to
sort the whole result. Second, you will not get proper result if other
client will insert new record in between your insert and this select. In
this case your application will be screwed up. Best way is to use SELECT
@@IDENTITY statement or SELECT SCOPE_IDENTITY (depending on a database)
In a case of DataTable newly inserted record is always the last one and
you
could check it

--
Val Mazur
Microsoft MVP

http://xport.mvps.org

I want to find the primary key (autoincremented) of the record I just
saved
(i.e.. the last record saved). With SQL, I can do this with "Select
Top
1
PrimaryKey from database Order by PrimaryKey desc." How can I do this
with
a "RowFilter?"

This //\\ dvRecords.RowFilter = "PrimaryKey = Top 1 PrimaryKey Order
By
PrimaryKey desc" //\\ doesn't work. Maybe I have the syntax wrong!!

Appreciate suggestions on using "RowFilter" to find the last records
entered!

Thank you

Bruce
 
Back
Top