473,836 Members | 1,381 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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.Ole Db.OleDbExcepti on: Missing semicolon (;) at end of SQL
statement

Does anyone know how to do this?

Thanks,

CindyH
Jun 2 '06 #1
7 28759
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.Ole Db.OleDbExcepti on: Missing semicolon (;) at end of SQL
statement

Does anyone know how to do this?

Thanks,

CindyH

Jun 2 '06 #2
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**********@d iscussions.micr osoft.com> wrote in message
news:6D******** *************** ***********@mic rosoft.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.Ole Db.OleDbExcepti on: Missing semicolon (;) at end of SQL
statement

Does anyone know how to do this?

Thanks,

CindyH

Jun 2 '06 #3
Cindy,

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

Here is an example:

Dim cn As New
OleDb.OleDbConn ection("Provide r=Microsoft.Jet .OLEDB.4.0;Data
Source=CourseIn fo.mdb;")
Dim cmd As New OleDb.OleDbComm and

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.ExecuteNonQ uery()

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

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**********@d iscussions.micr osoft.com> wrote in message
news:6D******** *************** ***********@mic rosoft.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.Ole Db.OleDbExcepti on: Missing semicolon (;) at end of SQL
statement

Does anyone know how to do this?

Thanks,

CindyH


Jun 2 '06 #4

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.Ole Db.OleDbExcepti on: Missing semicolon (;) at end of SQL
statement

Does anyone know how to do this?

Thanks,

CindyH


Jun 2 '06 #5
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.Ole Db.OleDbExcepti on: Missing semicolon (;) at end of SQL
statement

Does anyone know how to do this?

Thanks,

CindyH

Jun 4 '06 #6
On Fri, 2 Jun 2006 12:38:02 -0500, "Cindy H" <no*****@nowher e.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.Ole Db.OleDbExcepti on: 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
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*****@nowher e.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.Ole Db.OleDbExcepti on: 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
3604
by: Trevor Best | last post by:
I don't know if this has been reported before but it appears to be a bug with Access. If I create two tables both with an identity column then create an insert trigger on table1 that inserts a related record into table2, now create a form on table1 with a subform on table2. Insert records into the main form to your heart's content and everything's fine, each main record automatically gets a child record and so far the identity columns...
0
1198
by: refer_to_website | last post by:
I am using VB.NET to insert a record into an Access database. The database table uses an AutoNumber for its primary key. How can I determine what value Access assigned to the primary key? All the research I've done says to use @@IDENTITY but that appears to only work for SQL Server? How can I do this in Access?
1
2372
by: nicholas | last post by:
To insert a record in a Ms Access database and be able to retrieve the newly created ID (autonumber) I used the code below (code 1). Now, the problem is that this is not very secure and that, if for example an insertion contains a ' or a " this code fails. It is much better to work with @parameters. So could someone change my code to make it work with @parameters and that I still can retrieve that autonumber. (note that it is for a MS...
1
2900
by: loreille | last post by:
To insert a record in a Ms Access database and be able to retrieve the newly created ID (autonumber) I used the code below (code 1). Now, the problem is that this is not very secure and that, if for example an insertion contains a ' or a " this code fails. It is much better to work with @parameters. So could someone change my code to make it work with @parameters and
3
1109
by: Sparky Arbuckle | last post by:
I can't for the life of me figure out why this won't insert into my database. I'm also not getting any errors when I run this. I've even tried inside a TRY CATCH block and still nothing. I've run this web app numerous times and each time I check the database, nothing is there! I must be missing something small here. Private Sub btnSubmitDLNumber_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles...
2
2692
by: kerriejones | last post by:
I am a bit rusty with my php, but I need to move some code on to a new website and coming upon a lot of problems. This one I can not solvefor the life of me. I am trying to insert a record into a table which has an auto number as the key. I was not sure if my connection was the problem so I tried the following select first, but it runs withput any problem: $mySQL = "SELECT * FROM Audit Where AuditID = 1089"; $resNews =...
7
2223
by: shivasusan | last post by:
Hello Friend! I am new for ASP. Database is Microsoft Access. So pls help me..... My problem is --- 1. In login page, two text boxs. first textbox is username & second textbox is Password. Password is Date of Birth. If DOB is starting 09-08-90 or 11-01-91 is not login or DOB is starting more than 13 (13-03-91 or 28-11-91) is succefully login. 2. So I check, How? the date is print. Below code
2
15447
by: Good Guy | last post by:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Customers_Contact". The conflict occurred in database "BreakAway", table "dbo.Contact", column 'ContactID'. The statement has been terminated. Hi, all. This is my maiden post. Some background: I am doing a C# project using Entity Framework 4.0 in Visual Studio 2010 and SQL Server 2005 Expresss. The project is from the book, Programming Entity Framework, Chapter 9, which...
0
2355
by: Ed Pisa | last post by:
Hello, I have been working with this problem now for several days. I can delete and Update my data but I can not get it to insert a new record. I receive the syntax error insert into. I am not using any reserved words and I have not been able to find my answer googling like crazy. Any help would be greatly appreciated. below is the code that seems to give me the problem. ElseIf NEWMODE = True Then Dim oAdapter As...
0
9672
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10854
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10559
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10601
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10260
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9388
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5829
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4023
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.