PC Review


Reply
Thread Tools Rating: Thread Rating: 4 votes, 4.00 average.

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

 
 
Cindy H
Guest
Posts: n/a
 
      2nd Jun 2006
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


 
Reply With Quote
 
 
 
 
=?Utf-8?B?S2VycnkgTW9vcm1hbg==?=
Guest
Posts: n/a
 
      2nd Jun 2006
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
>
>
>

 
Reply With Quote
 
 
 
 
Cindy H
Guest
Posts: n/a
 
      2nd Jun 2006
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:(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
> >
> >
> >



 
Reply With Quote
 
=?Utf-8?B?S2VycnkgTW9vcm1hbg==?=
Guest
Posts: n/a
 
      2nd Jun 2006
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:(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
> > >
> > >
> > >

>
>
>

 
Reply With Quote
 
zacks@construction-imaging.com
Guest
Posts: n/a
 
      2nd Jun 2006

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


 
Reply With Quote
 
GhostInAK
Guest
Posts: n/a
 
      4th Jun 2006
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
>



 
Reply With Quote
 
Paul Clement
Guest
Posts: n/a
 
      5th Jun 2006
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)
 
Reply With Quote
 
aaron.kempf@gmail.com
Guest
Posts: n/a
 
      5th Jun 2006
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)


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
insert data into Access database (could it be AutoNumber?) Scott Starker Microsoft ADO .NET 3 20th Apr 2007 03:00 PM
Using a SQL 'INSERT INTO' statement with a table having an AutoNumber Field Jack G Microsoft Access Getting Started 12 23rd May 2006 04:15 PM
VB.NET - How do I return an AutoNumber from an Insert Statement using the DataGridView Phil Williams Microsoft ADO .NET 7 26th Jan 2006 12:54 PM
insert record in Ms Access database AND retrieve the autonumber (ID) nicholas Microsoft ASP .NET 5 20th Oct 2004 05:02 PM
AutoNumber field does not contain "(AutoNumber)" when adding a record Keith Microsoft Access Forms 3 12th Nov 2003 05:50 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:15 PM.