Access using @@IDENTITY

[cpopham]

I have an INSERT statement that looks like this:

 

Dim strSQL As String = "INSERT INTO ULogRec ( ULDate, ULTime, ULName ) " & _
                              "VALUES (@Dat, @Tim, @UName)"

 

This works fine with an executenonquery statement.

 

But, I want to use @@IDENTITY with an ExecuteScalar statement to retrieve the value of the row that I just inserted into the database and assign this value to an integer variable.

 

The primary key of the table is an autonumber type.

 

Any ideas?

 

Thanks,

Chester

Edited April 22, 2005 by cpopham

[cpopham]

Got it:

 

myCom.CommandText = "SELECT @@Identity"
myCom.CommandType = CommandType.Text
intCLogKey = CInt(myCom.ExecuteScalar())

Edited April 22, 2005 by cpopham

[HJB417]

instead of sending 2 queries to the server, u can save a round trip by combinding the 2 sql queries and invoking ExecuteScalar.

 

e.x.:

myCom.CommandText = "INSERT INTO ULogRec ( ULDate, ULTime, ULName ) " & _

"VALUES (@Dat, @Tim, @UName);" &_ "SELECT @@Identity"

myCom.CommandType = CommandType.Text

intCLogKey = CInt(myCom.ExecuteScalar())

[cpopham]

Thanks, yeah that will cut down on database trips. I like your idea of combining both commands into one execution.

Thank you for the help :)

 

Chester

[cpopham]

When I combine both statements into one, I get an error: Here is my code:

 

      Dim strSQL As String = "INSERT INTO ULogRec ( ULDate, ULTime, ULName ) " & _
                              "VALUES (@Dat, @Tim, @UName) " & _
                              "SELECT @@IDENTITY;"

 

Now that code throws the error:

Missing semicolon (;) at end of SQL statement

 

       Dim strSQL As String = "INSERT INTO ULogRec ( ULDate, ULTime, ULName ) " & _
                              "VALUES (@Dat, @Tim, @UName); " & _
                              "SELECT @@IDENTITY "

 

The error for that statement reads:

Characters found after end of SQL statement.

 

My execution code looks like this:

myCom.CommandType = CommandType.Text
       Try
           intULogKey = CInt(myCom.ExecuteScalar())
       Catch ex As Exception
           MessageBox.Show(ex.Message)
       End Try

 

My backend is an Access database. Anyideas as to why I can not combine both statements into one?

 

Chester

[Mister E]

Not sure, but I don't think you can send multiple queries in the same command string to Access like you can in SQL Server.

[HJB417]

access doesn't seem to support multiple statements, even in the 'stored procedures'. Edited April 22, 2005 by HJB417

[cpopham]

Yeah I thought using stored procedures it might support it. I am going to switch this to MySQL later and it should support multiple statements :)

 

Thanksm Chester

[HJB417]

well, it depends which mysql driver/api you're using. I created and use an ado.net client/driver using the mysql c api and even though mysql 4.1 and above support multiple queries in a single statement, it only applies to unparameterized queries. The official .net provider uses the undocumented network protocol, so I think the multiple queries will probably work. I also think the odbc client is outdated should you choose to use that.