Question Adding New Records, "Autonumber Duplication Error"

[vbbeginer]

I am using VS 2010 (Visual basic) and MS Access 2004 (mdb)
Everytime i run the program and add new records, when the autonumber back to negative one (-1), I got error like this


I try different insert codes but its not working, some of the codes I tried is not saving the record in the database..

Here is my code:

ADD NEW BUTTON:

Private Sub btn_add_3rd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_add_3rd.Click

  Me.X_subjects_3rdBindingSource.AddNew()

 SubnameTextBox2.Clear()
 DescTextBox2.Clear()
 Unit_noTextBox2.Clear()
 Unit_priceTextBox2.Clear()

End Sub

SAVE RECORD BUTTON:

Private Sub btn_save_3rd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_save_3rd.Click
 
Dim Mycn As New OleDbConnection
                Dim Command As OleDbCommand
                Dim icount As Integer
                Dim SQLstr As String

                Try
                    Mycn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\mydbv1.mdb")

                    Mycn.Open()

                    SQLstr = "INSERT INTO x_subjects_3rd VALUES('" & IDTextBox3.Text & "','" & SubnameTextBox2.Text & "','" & DescTextBox2.Text & "','" & LaboratoryComboBox2.Text & "','" & Unit_noTextBox2.Text & "','" & Unit_priceTextBox2.Text & "','" & SchedComboBox2.Text & "')"

                    Command = New OleDbCommand(SQLstr, Mycn)
                    icount = Command.ExecuteNonQuery

                    MsgBox("Record has been added to the database", MsgBoxStyle.Information, MessageBoxButtons.OK)
                    Refresh()

 Catch ex As Exception

                    MsgBox(ex.Message)
                    Mycn.Close()
                    Exit Sub

                End Try

    End Sub

 

[jmcilhinney]

First of all, there's no such thing as Access 2004. Office 2003 was followed by Office 2007.

As for the problem, you're creating it for yourself. If your database column truly is an AutoNumber then the whole point is that the database generates the value automatically. The only way a duplicate could be entered is if you entered it. Why are you including the ID column in your INSERT statement? You don;t insert the ID; the database generates it.

What you need to do is specify exactly which columns you're inserting and provide a value for each of those columns. Don't omit the column list because doing so requires you to provide a value for every column but you can't provide a value for the ID column because the database generating that itself.

Also, don't use string concatenation to insert values into SQL code. Always use parameters. To learn why and how, follow the Blog link in my signature and check out my post on parameters in ADO.NET.

 

[vbbeginer]

But hwn i deleted the IDTextBox3.Text in the INSERT statement i got this error:
"Number of query values and destination fields are not the same"

 

[jmcilhinney]

I'll wager that you haven't followed my instructions, specifically this:

What you need to do is specify exactly which columns you're inserting and provide a value for each of those columns.

If you don't follow instructions then it's not surprising that it doesn't work.

 

[vbbeginer]

I changed my insert code using parameters but i have errors in my "UPDATE INTO statement"

Here is my code:

[/FONT]Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\mydbv1.mdb")

               Dim insert As String

               Try
                   insert = "INSERT INTO x_subjects_1st (ID,subname,desc,laboratory,unit_no,unit_price,sched) VALUES (@ID,@subname,@desc,@laboratory,@unit_no,@unit_price,@sched)"

                   Dim cmd As OleDbCommand = New OleDbCommand(insert, con)

                   cmd.Parameters.AddWithValue("@ID", IDTextBox1.Text)
                   cmd.Parameters.AddWithValue("@subname", SubnameTextBox.Text)
                   cmd.Parameters.AddWithValue("@desc", DescTextBox.Text)
                   cmd.Parameters.AddWithValue("@laboratory", LaboratoryComboBox.Text)
                   cmd.Parameters.AddWithValue("@unit_no", Unit_noTextBox.Text)
                   cmd.Parameters.AddWithValue("@unit_price", Unit_priceTextBox.Text)
                   cmd.Parameters.AddWithValue("@sched", SchedComboBox.Text)

                   con.Open()
                   Dim rowsaffected As Integer = cmd.ExecuteNonQuery
                   MessageBox.Show(rowsaffected.ToString & "rows added")
                   con.Close()



               Catch ex As Exception
                   MsgBox(ex.Message)
               End Try


[FONT=Verdana]

 

[jmcilhinney]

Once again you have not done as instructed. Please read and follow instructions when they're provided.

You don't insert the ID; the database generates it.

 

[vbbeginer]

I followed the code in ur blog post, but still got error in insert into statement..

so i troubleshoot again my old code and i noticed that i forgot to put tableadapter.fill in my form_load, and it works,
thank you

 

[vbbeginer]

i tried troubleshoot the code in ur blog post and i changed desc to [desc], bcause i searched that its a reserved word.. and it works too, thnx!