Access database insert statement with an autonumber (identity) in vb.net

  • Thread starter Thread starter Cindy H
  • Start date Start date
C

Cindy H

Hi

I'm having a problem getting the insert statement correct for an Access
table I'm using.
The Access table uses an autonumber for the primary key.
I have tried this:
INSERT INTO Tournaments (Tournament, MemberName, Score) VALUES (vtournament,
vMemberName, vScore) SELECT ID, Tournament, MemberName, Score FROM
Tournament WHERE (ID = @@IDENTITY);"

This works with a sql server database.

I'm getting this error:

System.Data.OleDb.OleDbException: Missing semicolon (;) at end of SQL
statement

Does anyone know how to do this?

Thanks,

CindyH
 
Cindy,

Try executing the Insert, then execute Select @@Identity as a second command.

Kerry Moorman
 
I think I'm kind of doing that by putting a semicolon after the insert
statement.
That gives me error - Characters found after end of SQL statement.

INSERT INTO Tournaments (Tournament, MemberName, Score) VALUES (?,
?, ?); SELECT ID, Tournament, MemberName, Score FROM Tournament WHERE
(ID = @@IDENTITY);"


Is this what you mean?
 
Cindy,

No, Access cannot process multiple sql statements in the same command.

Here is an example:

Dim cn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=CourseInfo.mdb;")
Dim cmd As New OleDb.OleDbCommand

cmd.CommandText = "Insert Into Students (Name, Test1, Test2)
Values (?, ?, ?)"
cmd.Parameters.Add("Name", "Smith, Mary")
cmd.Parameters.Add("Test1", 80)
cmd.Parameters.Add("Test2", 90)

cn.Open()
cmd.Connection = cn
cmd.ExecuteNonQuery()

Dim ID As Integer
cmd.CommandText = "Select @@IDENTITY"
ID = cmd.ExecuteScalar

cn.Close()

MsgBox("ID = " & ID)

Kerry Moorman
 
Cindy said:
Hi

I'm having a problem getting the insert statement correct for an Access
table I'm using.
The Access table uses an autonumber for the primary key.
I have tried this:
INSERT INTO Tournaments (Tournament, MemberName, Score) VALUES (vtournament,
vMemberName, vScore) SELECT ID, Tournament, MemberName, Score FROM
Tournament WHERE (ID = @@IDENTITY);"
Click to expand...

It's news to me that Access supports the @@IDENTITY special variable.
 
Hello Cindy,
Access does not support any @@variables. None. Nada. Furthermore, Access
has no reliable method for obtaining a key that was just inserted viat an
Autonumber field. You could of course do something like: SELECT MAX(TableID)
From Table, but that will ONLY work in a Single User, Single Threaded environment.
As soon as you start using multiple threads (to do your database work) or
allowing multiple users things begin to break down rather fast.

I'd suggest Using Sql Server or Sql Server Express or MSDE.

-Boo
 
¤ Hi
¤
¤ I'm having a problem getting the insert statement correct for an Access
¤ table I'm using.
¤ The Access table uses an autonumber for the primary key.
¤ I have tried this:
¤ INSERT INTO Tournaments (Tournament, MemberName, Score) VALUES (vtournament,
¤ vMemberName, vScore) SELECT ID, Tournament, MemberName, Score FROM
¤ Tournament WHERE (ID = @@IDENTITY);"
¤
¤ This works with a sql server database.
¤
¤ I'm getting this error:
¤
¤ System.Data.OleDb.OleDbException: Missing semicolon (;) at end of SQL
¤ statement
¤
¤ Does anyone know how to do this?

See the following:

HOW TO: Retrieve the Identity Value While Inserting Records into Access Database By Using Visual
Basic .NET
http://support.microsoft.com/default.aspx?scid=kb;en-us;815629


Paul
~~~~
Microsoft MVP (Visual Basic)
 
MDB is friggin crap; spit on anyone that uses it anywhere.

it's not scalable enough for a single record and a single user.

-Aaron
 
Back
Top