Member Avatar for babbu

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

Database1.zip (12.85 KB)
  • 2 Contributors
  • 7 Replies
  • 122 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by TomW

Dani AI

Generated

This looks like a classic case of relying on DataTable row order (and not refreshing it) rather than asking the database for the current maximum ID. As hinted, if you use the DataTable's "last row" to compute the next ID you can get wrong or stale results unless you explicitly re-query the database and/or enforce an ORDER BY. Also check the ID column type: if the field is text, sorting is lexical and "44" can end up in the wrong place.

A reliable immediate fix is to ask the database for the highest ID instead of reading the last DataTable row. For example, run an aggregate or a TOP query and handle DBNull safely:

Dim cmd As New OleDb.OleDbCommand("SELECT MAX(ID) FROM Customer", oledbcon)
oledbcon.Open()
Dim obj = cmd.ExecuteScalar()
oledbcon.Close()
Dim lastId As Integer = If(IsDBNull(obj), 0, Convert.ToInt32(obj))

Other practical points to resolve the symptom you reported:

  • If you keep an in-memory DataTable, refresh it after inserts (or add the new row to it) before using its last row.
  • Use the correct parameter types when inserting. If ID is numeric, pass it as an integer parameter (not VarChar). Example: addcom.Parameters.Add("?", OleDb.OleDbType.Integer).Value = newId.
  • Check the return from ExecuteNonQuery() and catch exceptions so failed inserts are visible instead of silently ignored.
  • For multi-user scenarios, manual last+1 allocation can race. Prefer AutoNumber, a centralized counter, or a transactional mechanism to avoid duplicates.

Finally, open the Access table in Access and verify the actual stored rows (type and values) for ID 44 to confirm whether the problem is ordering, a failed insert, or a type mismatch.

Recommended Answers

All 7 Replies

Member Avatar for TomW

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.

Member Avatar for babbu 
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
Member Avatar for TomW

In your database field: Id and identity seed?

Member Avatar for babbu

didnt get you...

Member Avatar for TomW

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?

Member Avatar for babbu

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.

Member Avatar for TomW

Are you refilling your datatable after performing your insert statement?

Edited by TomW because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.