|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Access database insert statement with an autonumber (identity) in vb.netI'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 Show quoteHide quote "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? Show quoteHide quote "Kerry Moorman" <KerryMoor***@discussions.microsoft.com> wrote in message news:6D0458BC-0484-4A37-99BC-5635677A98A7@microsoft.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 > > > > > > 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 Show quoteHide quote "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" <KerryMoor***@discussions.microsoft.com> wrote in message > news:6D0458BC-0484-4A37-99BC-5635677A98A7@microsoft.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 > > > > > > > > > > > > Cindy H wrote:
> Hi It's news to me that Access supports the @@IDENTITY special variable.> > 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);" Show quoteHide quote > > 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 Show quoteHide quote > 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" <nonn***@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.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 Paul Clement wrote: Show quoteHide quote > On Fri, 2 Jun 2006 12:38:02 -0500, "Cindy H" <nonn***@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.aspx?scid=kb;en-us;815629 > > > Paul > ~~~~ > Microsoft MVP (Visual Basic) 
Other interesting topics
|
|||||||||||||||||||||||
