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


"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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:6D0458BC-0484-4A37-99BC-(E-Mail Removed)...
> 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
> >
> >
> >

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 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" <(E-Mail Removed)> wrote in message
> news:6D0458BC-0484-4A37-99BC-(E-Mail Removed)...
> > 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
> > >
> > >
> > >
>
>
>

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

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
>

On Fri, 2 Jun 2006 12:38:02 -0500, "Cindy H" <(E-Mail Removed)> 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)

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" <(E-Mail Removed)> 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)