MS Access & Select @@Identity

Question

User-323804624 posted

Hi,

I'm used MS Access,

after insert, i want get the Identity value,

My code's Insert commend success,

but ID value always 0.

        Dim query As String = "Insert Into Shippers (CompanyName) Values (?)"
        Dim query2 As String = "Select @@Identity"
        Dim ID As Integer
        Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Nwind.mdb"
        Using conn As New OleDbConnection(connect)
            Using cmd As New OleDbCommand(query, conn)
                cmd.Parameters.AddWithValue("", "test")
                conn.Open()
                cmd.ExecuteNonQuery()
                cmd.CommandText = query2
                ID = cmd.ExecuteScalar()
            End Using
        End Using
        Me.LabelMessage.Text = String.Format("@@Identity : {0}", ID)

Answer 1

User-1659704165 posted

Hi,

\http://support.microsoft.com/kb/815629

chk the abov elink

Answer 2

User-821857111 posted

My code's Insert commend success,

but ID value always 0.

I don't know why this is. I have just copied and pasted your code as-is, and it works fine for me. Just to be on the safe side, add cmd.Parameters.Clear() before the line where you set the CommandText to query2.

Answer 3

User-1199946673 posted

It looks good to me also. Just to be sure: Does the Shippers table contain an Identity (autonumber) column? 

Answer 4

User-821857111 posted

Does the Shippers table contain an Identity (autonumber) column? 

The default Northwind sample database does - first thing I checked. But that's a good question.

Answer 5

User-1199946673 posted

The default Northwind sample database does

 

 

Ah, I missed that is was the Northwind database. Then its should work, maybe a permission problem, but then I would expect an exception!

Answer 6

User-323804624 posted

actuality, this code can work,

I change a new access,

it's work!!!

maybe is northwind database question...