Adding Autonumber in Access Database, Problems and Solution

Visual Basic Ado.Net Autonumber Update Problem

Autonumber fields are automatically updated in Microsoft Access database if we fill the database directly in the Microsoft Access but as a beginner, you may face many problems as a new programmer to add autonumber fields in Database through Visual Basic Programs. I am about to share my problems and solutions when I first tried to do this.

I am about to discuss three ways to add autonumber fields in Database keeping the reference as the database table shown in the figure up to make the concept clear.

First if you want to add some records in this database table keeping the autonumber field auto increased, then you can write the commandtext query in this way.

cmd.commandtext="Insert into table(Address,Phone) values('Address',Phone)"

Writing simply this will update the autonumber s_No itself.

Here cmd is oledbcommand

This is the simplest idea but the problem arise in the condition that you clear all the data from database and add the data from visual Basic program again. Autonumbers are started from where it ended last. For an instance if you add 3 data before and then clear them all going to database and save it. Afterwards when you add data from program again using this query, autonumber will start from 4 again.

To solve this problem, you can use this query.

Dim countrows as integer=ds.tables("adp").Rows.count +1
cmd.commandtext="Insert into table values(" + countrows.ToString + ","+"'Address',Phone)


Doing this will add autonumber to the database counting the rows. This is also other easy way to add autonumber to the Database.
In this case again we can face error. Suppose there are 5 data with 5 autonumber, if you delete data with autonumber 2 and 3 and then again add a data, then autonumber 4 will be repeated causing error if you have set S_NO as a primary key.

So if you want the S_No not to be repeated then you can use this idea that has taken me 3 hours to solve(because I am also a beginner)

Dim a as integer=1
Dim b as integer=0
do
   cmd.commandtext="Insert into table values(" + a.tostring +  ",'Address',Phone)
   cmd.connection= con
   try
      con.open()
      cmd.ExecutenonQuery()
      b=a+2
      MsgBox("Record Added")
   catch ex as exception
      a=a+1
   Finally
      con.close()
      cmd.Dispose()
      a.Dispose()
   End Try
While b<a


Here con is oledbconnection.

You may try this and if you find any error or if you do not understand, you may comment so that I can make you clear in this.

Posted in: Programming