Inserting records into MS Access where there is an autonumber for a primary key

[ashes]

Hi,

I am creating an ecommerce website using Microsoft Visual Studio, VB.Net and MS Access 2003. I am new to VB.Net

When someone wants to register on the website, they fill out a form and the contents of the form is inserted into the MS Access database.

The Customer table in the database already has 30 records (with CustomerIDs 1 - 30) in it (from when the database was first created). The CustomerID field in the database is an autonumber datatype and it is set to increment. However, new customers' information needs to be entered when they fill out the form. I am trying to now insert the information from the form into the database using VB.Net but the code i am using is inserting records at CustomerID 0.

I found many solutions on the net but none of them worked. When i don't use the autonumber field in the VALUES part of the query i get an error saying

“Number of query values and destination fields are not the same.”

When i actually get information to be inserted into the database it starts at CustomerID 0 instead of 31. Then if i try to input another record it is trying to overwrite the records i already have in the database. i get this error:

“The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.”

This is the code I am working with right now:

Dim connectionstring As String
connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("GameDB.mdb")

...
        Using myConnection As New OleDbConnection(connectionstring)
        myConnection.Open()
 
        Dim cmdText As String = "SELECT * FROM Customer"
        Dim command As OleDbCommand
        command = New OleDbCommand(cmdText, myConnection)
 
        Dim reader As OleDbDataReader
        reader = command.ExecuteReader
 
        Dim autonumber As Integer
        Dim newauto As Integer
 
 
        If autonumber > Microsoft.VisualBasic.Val("CustomerID")  Then
        autonumber = Int(autonumber) + 1
        newauto = autonumber
        End If
 
OR
 
If autonumber = Microsoft.VisualBasic.Val("CustomerID") Then
                autonumber = Int(autonumber) + 1
                newauto = autonumber
            End If
 
cmdText = "INSERT INTO Customer VALUES ('" + autonumber.ToString + "','" + FirstName.Text + "','" + LastName.Text + "','" + Address.Text + "','" + Country.Text + "','" + PhoneNo.Text + "','" + Email.Text + "','" + UserName1.Text + "','" + Password.Text + "')"
        command = New OleDbCommand(cmdText, myConnection)
        command.ExecuteNonQuery()
        Response.Redirect("RegistrationApproved.aspx")
 
 
        End If
        End Using

Can anyone help me please. I have tried many different codes for this to work....

[ADezii]

1. Is the Name of your AutoNumber Field actually called autonumber?
2. It appears as though you are trying to coerce the AutoNumber Field to an Integer, an AutoNumber Field is a Long Integer.
3. You cannot dynamically assign a value to an AutoNumber Field, it will, and must, increment on its own.
4. The AutoNumber Field should not be in the INSERT INTO Clause.

[ashes]

i got the code to work by naming the columns in the INSERT INTO statement. Before, i had only named the VALUES fields and not the columns in the database. Also the autonumber field (CustomerID) had to be left out. So the query now would be:

INSERT INTO Customer (FirstName, LastName, Address ect...)    
VALUES('"FirstName.Text + "','" + LastName.Text + "','" + Address.Text ect..)

[ADezii]

i got the code to work by naming the columns in the INSERT INTO statement. Before, i had only named the VALUES fields and not the columns in the database. Also the autonumber field (CustomerID) had to be left out. So the query now would be:

INSERT INTO Customer (FirstName, LastName, Address ect...)
VALUES('"FirstName.Text + "','" + LastName.Text + "','" + Address.Text ect..)

Thanks for sharing the solution with us, glad you worked it out.

[pkpanda]

I have similar but bit different issue that is

I have a table INVOICE when user enter data in invoice I have to insert record into Transactions (Master ) and Transaction_details (detail) both the tables have ID type autonumber i have a VB code that insert into record into Transaction table then when I try to insert record into transaction_details record I don't have the trasnsation ID which links these 2 tables. I am using MSaccess 2003. can any one help me in this.

Thanks ,
PP

[NeoPa]

PP,

You have asked your question in another member's thread. This is called thread hijacking and is not allowed on this site. There are various reasons for this, the principal of which is that it typically diverts the flow of the thread away from his needs and into yours. Another is that it will tend to mean that your new question is not seen as such, and may get overlooked by many of our experts (You appreciate that they're not looking for answered questions).
Please post your questions in their own threads in future (See How to Ask a Question).

ADMIN.

[NeoPa]

As to the original question, this is a frequently asked question which indicates a misunderstanding as to what AutoNumbers are for.

They are not designed to provide a sequential list. They are not designed to provide record sorting.

They are SIMPLY there to ensure that each record has a value which is different from every other value - Unique.

There are other ways of doing what you need, but they generally involve using fields other than AutoNumber fields.