Sub auto()
        c.con.Open()
        Dim j As Integer = 0
        Dim cmd As New SqlCommand("select staffid from staff order by staffid", c.con)
        Dim dr As SqlDataReader()
        dr = cmd.ExecuteReader()
        While (dr.read())
            txtstaff_id.Text = dr("staffid").ToString()
        End While
        j = Convert.ToInt32("txtstaff_id.text").ToString()
        j = j + 1
        txtstaff_id.Text = j.ToString()
        dr.close()
        c.con.Close()


    End Sub

i have created this method but error occured...
i want to create method for auto_generate_id()
what is the wrong in this coding
please help me, it's urgent

If you're getting an actual error message it always help to post it up. It helps us know what is wrong.
I'm guessing there is more than one value in the staffid table of the database. If so, then looping through them all is a waste of time if you only want the highest one. Use "SELECT MAX(staffid) FROM staff" and you'll be returned the highest id currently in the database table and looping through them all is not needed.

Another approach is 'select top 1 staffid from staff order by staffid desc'.
This will return only one record with the highest staffid.

If you need to obtain the new id directly from the db, you can 'select top 1 staffid+1 from staff order by staffid desc' or 'SELECT MAX(staffid)+1 FROM staff' (hint by hericles) assuming that staffid is a numeric field.

Please, have in mind that all of those aproaches are valid only in a mono user environment.

Hope this helps.

error is occured in datareader. can you answer me????

Another approach is 'select top 1 staffid from staff order by staffid desc'.
This will return only one record with the highest staffid.

If you need to obtain the new id directly from the db, you can 'select top 1 staffid+1 from staff order by staffid desc' or 'SELECT MAX(staffid)+1 FROM staff' (hint by hericles) assuming that staffid is a numeric field.

Please, have in mind that all of those aproaches are valid only in a mono user environment.

Hope this helps.

error is occured in datareader.

As I said earlier, if you are getting an actual error message please post it up.
Have you debugged the code to check if the data reader contains any results? Or is the error coming from the dr("staffid").ToString() part of the code?

Also you can help us to help you if you put your database table design here and the errors descriptions or message.

Use this.

dim com as new sqlCommand("select max(staffid) from staff",c.con)
dim maxid as Integer
if(IsDbNULL(com.ExecuteScaler)<>true)
maxid=com.executeScaler
maxid+=1
esle
maxid=0
endif

txtstaff_id.Text=maxid.toString

I think this will solve your problem.

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