By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,508 Members | 1,915 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,508 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jun 2 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Cindy,

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

Kerry Moorman
"Cindy H" wrote:
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

Jun 2 '06 #2

P: n/a
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?

"Kerry Moorman" <Ke**********@discussions.microsoft.com> wrote in message
news:6D**********************************@microsof t.com...
Cindy,

Try executing the Insert, then execute Select @@Identity as a second command.
Kerry Moorman
"Cindy H" wrote:
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

Jun 2 '06 #3

P: n/a
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.OLED B.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 H" wrote:
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?

"Kerry Moorman" <Ke**********@discussions.microsoft.com> wrote in message
news:6D**********************************@microsof t.com...
Cindy,

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

command.

Kerry Moorman
"Cindy H" wrote:
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


Jun 2 '06 #4

P: n/a

Cindy H wrote:
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);"
It's news to me that Access supports the @@IDENTITY special variable.

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


Jun 2 '06 #5

P: n/a
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?

Thanks,

CindyH

Jun 4 '06 #6

P: n/a
On Fri, 2 Jun 2006 12:38:02 -0500, "Cindy H" <no*****@nowhere.com> wrote:

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...b;en-us;815629
Paul
~~~~
Microsoft MVP (Visual Basic)
Jun 5 '06 #7

P: n/a
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


Paul Clement wrote:
On Fri, 2 Jun 2006 12:38:02 -0500, "Cindy H" <no*****@nowhere.com> wrote:

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...b;en-us;815629


Paul
~~~~
Microsoft MVP (Visual Basic)


Jun 5 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.