hie,
m using visual studio 2005 and access 07.
this is my code. ive also attached an access 07 file and this is the exact data tht my actual database has..plz help

Dim DataAdapter As New OleDb.OleDbDataAdapter("Select ID from Customer", oledbcon)
        Dim datatable As New DataTable
        DataAdapter.Fill(datatable)
        Dim CommandBuilder As New OleDb.OleDbCommandBuilder(DataAdapter)
        id = datatable.Rows(datatable.Rows.Count - 1)("ID")

the problem is id does not reflect the last value(i.e. 44) as it shud
it reflects 43.
how ever if i delete row with id 44 thn is works fine but as soon as i add row with id 44..problem strts plz help

Your code shows only a select statement. You mention the problem is after adding a row but your not showing the code for adding the row or getting the new field after adding it.

I dont have access installed to look at your db, not that i think that would help. But if you upload the form that is having problems, I'll take a look at the code to see if I can spot the problem.

If Me.txtCompany.Text.Trim = "" Or Me.txtTelephone.Text.Trim = "" Or Me.txtEmail.Text.Trim = "" Or Me.txtAddress1.Text.Trim = "" Then
            MessageBox.Show("Please enter all informaiton", "Libra Plastic")
            Exit Sub
        End If

        Dim addcom As New OleDb.OleDbCommand
        addcom.CommandText = "Insert into Customer (ID,Company,Address,Telephone,Email) values (?,?,?,?,?)"
        addcom.Connection = oledbcon

        addcom.Parameters.Add("?", OleDb.OleDbType.VarChar)
        addcom.Parameters.Add("?", OleDb.OleDbType.VarChar)
        addcom.Parameters.Add("?", OleDb.OleDbType.VarChar)
        addcom.Parameters.Add("?", OleDb.OleDbType.VarChar)
        addcom.Parameters.Add("?", OleDb.OleDbType.VarChar)

        id = id + 1
        addcom.Parameters(0).Value = id
        addcom.Parameters(1).Value = Me.txtCompany.Text.Trim
        addcom.Parameters(2).Value = Me.txtAddress1.Text.Trim
        addcom.Parameters(3).Value = Me.txtTelephone.Text.Trim
        addcom.Parameters(4).Value = Me.txtEmail.Text.Trim

        oledbcon.Open()
        addcom.ExecuteNonQuery()
        oledbcon.Close()

        btnAddColour.Enabled = True
        mnuiAddNew.Enabled = True
        mnuiAddColour.Enabled = True
       Me.GroupBox1.Enabled = False

Sorry, the word "In" was supposed to be "Is" in my last post.

You seem to be attempting to manually generate your "Id" field value getting the last record value and increasing it by one.

In the database you can set a column as an identity seed where it will automatically increment the field number whenever you add a new record. I was wondering if your database column is set up to do this?

no it is not being automatically incremented. i am aware of tht option but cannot use it since at a later stage i have to manually assign tht value.
moreover the porblem seems to be only at the id value 44 before tht is was working fine.

Are you refilling your datatable after performing your insert statement?

Edited 7 Years Ago by TomW: n/a

This article has been dead for over six months. Start a new discussion instead.