Asp.net Forum

Ask Question   UnAnswered
Home » Forum » Asp.net       RSS Feeds

How do I load MS Access @@Identity into ExecuteScalar?

  Asked By: Dale    Date: Jul 26    Category: Asp.net    Views: 2059

Using myCommand.ExecuteScalar, I have set my connection, built my
command object to insert a row, and created a new ID because of the
Autonumber column in an Access table. I know this because I've looked
at the underlying table in question.

What I want is to return that Autonumber value and I don't know how
to use ExecuteScalar to do it. Documentation says ExecuteScalar
returns what I want (first row, first column) in an object format. So
I should be able to run an INSERT query, and return @@Identity.

For now I would like to just see the @@Identity/AutoNumber on my Web
Form. This doesn't work:

Dim intNewID As Integer

intNewID = CType(myCommand.ExecuteScalar,Integer)

Me.lblNewID = intNewID

What am I missing, and is this the best way?



8 Answers Found

Answer #1    Answered By: Alexis Castillo     Answered On: Jul 26

When you say "It does not work" do you mean it throws an error or returns incorrect values?

Answer #2    Answered By: Dot net Sachin     Answered On: Jul 26

The label being filled by the initialized integer displays a "0",
which I am assuming is the default value for an Integer that hasn't
been filled. Intellisense says that ExecuteScalar returns an object.

I'm thinking I have to somehow load  myCommand.ExecuteScalar into a
datareader or some other object  and read that. . . .

Hope this is enough info. Just want to add a record to an Access
database and get the Autonumber back. . . . .

Answer #3    Answered By: Renee Lane     Answered On: Jul 26

Give us the SQL you are using....................

Answer #4    Answered By: Volney Fischer     Answered On: Jul 26

Ok, here is my code for my "LinkAdd" click. It does add a record. I'm
just trying to get back the record's autonumber at the same time I
append a row  (simplified for the purpose of illustration).
Pushing "F1" when my cursor is inside of ".ExecuteScalar" gives me
help which says: "Executes the query, and returns the first column  of
the first row in the resultset returned by the query. Extra columns
or rows are ignored." So, maybe it only works on Select queries, but
it DOES return  the first row and first column:

Private Sub LinkSave_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles LinkSave.Click
Dim myConnection As New OleDbConnection

Dim strSQL As String = "INSERT INTO tblDances
(AdminID) VALUES(1)"
Dim myCommand As New OleDbCommand(strSQL,
Dim intDanceID As Integer

intDanceID = CType(myCommand.ExecuteScalar(), Integer)

lblDanceID.Text = intDanceID-----Nothing happens here


End Sub

Answer #5    Answered By: Sophie Campbell     Answered On: Jul 26

You don't need to pass it to a datareader in fact the soulnt work, it
returns a value that can be cast to to string, int etc. For whatever reason
it is not getting a value. I'm not sure that Access can reurn the record id
in this way (always had to use addnew and then update to get the id in asp).
It does certainly work  with Ms Sql Server but I guess thats no help.

Answer #6    Answered By: Adalwine Fischer     Answered On: Jul 26

ExecuteScalar("SELECT @@Identity")

I am skeptical because of round-robin and concurrency issues that this way is reliable but I am curious what SQL approach you are using.

keep in mind that ExecuteScalar returns 1 value, not the 1st row/column. Subtle difference.

Answer #7    Answered By: Kristin Johnston     Answered On: Jul 26

I tried what you suggested, the .ExecuteNonQuery and then
the .ExecuteScalar("SELECT @@Identity FROM myTable") and it worked.
I'm with you in thinking that it sure seems clunky. Any better ways
are welcomed to replace this:

Dim myConnection As New OleDbConnection

Dim strSQL As String = "INSERT INTO tblDances
(AdminID) VALUES(1)"
Dim myCommand As New OleDbCommand(strSQL,


strSQL = ""
strSQL = "SELECT @@Identity FROM tblDances"

Dim cmd As New OleDbCommand(strSQL, myConnection)

Dim intDanceID As Integer

intDanceID = CType(cmd.ExecuteScalar(), Integer)

lblDanceID.Text = intDanceID


Answer #8    Answered By: Beatriz Silva     Answered On: Jul 26
Didn't find what you were looking for? Find more on How do I load MS Access @@Identity into ExecuteScalar? Or get search suggestion and latest updates.