Michael John
Michael John

Reputation: 27

incrementing ID in database in VB.net

i am facing an ID error in my database while adding new data after adding new data to my database.

    Private Sub TabPage1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TabPage1.Click
    Call setconnection()
    Try
        cd = New OleDbCommand("select * from  stud ", cn)
        dr = cd.ExecuteReader
        While dr.Read = True
            TextBox345.Text = dr.Item(0) + 1
        End While
        dr.Close()
        cn.Close()
    Catch ex As Exception
        MsgBox("Invalid Customer id! ")
    End Try

    Call setconnection1()
    Try
        cd = New OleDbCommand("select * from  instud ", cn)
        dr = cd.ExecuteReader
        While dr.Read = True
            TextBox423.Text = dr.Item(0) + 1
        End While
        dr.Close()
        cn.Close()
    Catch ex As Exception
        MsgBox("Invalid Customer id! ")
    End Try
   End Sub

This code automatically generates new ID for every student in all the streams like if in Plain BSc from 1-1000 Computer Science 1001-2000 Bio Tech 2001-3000 IT 3001-4000

The Error I face is that after some time of adding new data the ID does not increment after a certain number. I have found the reason also to why it does so: in the datagirdview when i add 10 student details the last ID is 10 and it places the next ID ie 11 before ID 1 hence my code keeps taking the value of last ID as 10 and does not proceed further!!! Please help i am stuck!!

Upvotes: 0

Views: 2287

Answers (1)

the_lotus
the_lotus

Reputation: 12748

I'm pretty sure there are a better ways to do what you are doing, but from why I can see from the limited code I think you want to pull the greatest value. Instead of looping through all the list and setting each number to the same textbox, get the greatest number and then set it.

Instead of

While dr.Read = True
    TextBox345.Text = dr.Item(0) + 1
End While

Try this

Dim maxId as Integer = 0

While dr.Read = True
    If maxId < dr.Item(0) Then maxId = dr.Item(0)
End While

TextBox345.Text = maxId + 1

If you want to get the top ID then a better way would be to get it directly from the database.

select max(id) from  stud 

Upvotes: 2

Related Questions