Auto increment to an existing database

[trisha0906]

hi, can someone help me? i am using vb.net2010 and mssql as my database.
what i want to do is when i open the form, the database will auto increment the student id and show it on the text box.

[Begginnerdev]

You will need to create table with an integer field with a default of 0.

On form_load you will want to pull the value from the database and add 1 to it.

On form_close you will want to save that value back to the database.

You can use my previous post here for the connection example.

You will do something like this:

'In the form load event.
sqlcmd = "SELECT curnum FROM table"
cmd = new OleDBCommand(sqlcmd,con)

'In the form close event.
sqlcmd = "UPDATE table SET curnum ='" & txtStudentID.text & "'"
cmd = new OleDBCommand(sqlcmd,con)

[adam_k]

We rarelly want to increment on form open or in any event other than the actual insert. The reason for this is that you are probably going to get either multiple records with the same id (if not set as unique in a multiuser environment) or one of your users will get an error (again in a multiuser environment). Think of it as a user opens your form and it takes him 5 mins to do the insert, if during those 5 mins another user opens your form he'll get the same id as the 1st one.

If you choose to store the "current" id in another table and increment it the same time you are retrieving it then you'll probably have chunks of numbers missing if the user decides not to go ahead with the insert.

My advice would be to let SQL assign it's ID and you retrieve it with OUTPUT in your insert statement. This way your key will be unique and with a continuation, allowing you to know if records are missing among other things.

[trisha0906]

We rarelly want to increment on form open or in any event other than the actual insert. The reason for this is that you are probably going to get either multiple records with the same id (if not set as unique in a multiuser environment) or one of your users will get an error (again in a multiuser environment). Think of it as a user opens your form and it takes him 5 mins to do the insert, if during those 5 mins another user opens your form he'll get the same id as the 1st one.

If you choose to store the "current" id in another table and increment it the same time you are retrieving it then you'll probably have chunks of numbers missing if the user decides not to go ahead with the insert.

My advice would be to let SQL assign it's ID and you retrieve it with OUTPUT in your insert statement. This way your key will be unique and with a continuation, allowing you to know if records are missing among other things.

hi adam_k, i'm sorry i wasn't able to state it clearly before but that's what i want to do, the mssql will generate the student id, then every time i open the form and fill up the information needed and click save, it will save it. but if i don't save it, the student it will be available until i save it. hope you can help me.

[trisha0906]

You will need to create table with an integer field with a default of 0.

On form_load you will want to pull the value from the database and add 1 to it.

On form_close you will want to save that value back to the database.

You can use my previous post here for the connection example.

You will do something like this:

'In the form load event.
sqlcmd = "SELECT curnum FROM table"
cmd = new OleDBCommand(sqlcmd,con)

'In the form close event.
sqlcmd = "UPDATE table SET curnum ='" & txtStudentID.text & "'"
cmd = new OleDBCommand(sqlcmd,con)

hi Begginnerdev, i'm sorry i wasn't able to state this earlier but i want to increment the student number with the mssql. hope you can help me

[Begginnerdev]

You can try loading the form, without getting the id.

When the user clicks "Save" you will retrieve the id then save.

That way you can prevent what adam_k is talking about.

The only way to receive the same key would be if they both press the buttons at the same time.

[adam_k]

Is this a multiuser environment? How many users/inserts per day or hour top?
What will you get out of displaying the next ID to be assigned? What do you plan to do with it?

[trisha0906]

Is this a multiuser environment? How many users/inserts per day or hour top?
What will you get out of displaying the next ID to be assigned? What do you plan to do with it?

i am doing a payment/accounting system. only one user will input the payment information of the student and generate the generate the student id number. i would like for the mssql to generate the id number for the user not to have to do it. once the student information was saved, if the user want to add another student information, the id number will increment automatically. but if the information was not saved, the student number will be available once the form is opened again until it was saved. i hope you understand and be able to help me

[adam_k]

I understand, but I don't see a reason for the ID to be on the form when opened.
For example:

CREATE TABLE students
(id int IDENTITY(1,1),
 name varchar(200),
 address varchar(200),
 phone varchar(200))

insert into students (name, address,phone) values ('Test','Homeless','0')

By setting the ID as IDENTITY the SQL will assign the next value according to the seed and increment specified automatically. You don't have to pass the ID value from your form to the SQL.

If you don't want to do it with IDENTITY then:

CREATE TABLE students
(id int,
 name varchar(200),
 address varchar(200),
 phone varchar(200))

insert into students (id,name, address,phone) 
select max(id) +1, 'Test','Homeless','0' from students

This insert will instruct the SQL server to calculate the ID, without passing the actual number.

If you do want your ID to display on your form (although I strongly recommend against it) use:

select max(id) +1 from students

adjusted to your table, field names to get the new ID.

[trisha0906]

I understand, but I don't see a reason for the ID to be on the form when opened.
For example:

CREATE TABLE students
(id int IDENTITY(1,1),
 name varchar(200),
 address varchar(200),
 phone varchar(200))

insert into students (name, address,phone) values ('Test','Homeless','0')

By setting the ID as IDENTITY the SQL will assign the next value according to the seed and increment specified automatically. You don't have to pass the ID value from your form to the SQL.

If you don't want to do it with IDENTITY then:

CREATE TABLE students
(id int,
 name varchar(200),
 address varchar(200),
 phone varchar(200))

insert into students (id,name, address,phone) 
select max(id) +1, 'Test','Homeless','0' from students

This insert will instruct the SQL server to calculate the ID, without passing the actual number.

If you do want your ID to display on your form (although I strongly recommend against it) use:

select max(id) +1 from students

adjusted to your table, field names to get the new ID.

ok thank you, i will try this

[adam_k]

Just saw an error in my second insert, the one without identity. It should be:

CREATE TABLE students
(id int,
 name varchar(200),
 address varchar(200),
 phone varchar(200))

insert into students (id,name, address,phone) 
select max(id) +1, 'Test','Homeless','0'

(Without the from students)

[poojavb]

ok thank you, i will try this

U can try this too for a alphanumeric or if u need only numeric increment then delete the red part

Try
            Dim myCommand As SqlCommand
            Dim STid As String
            myCommand = New SqlCommand("select ISNULL(Max(SUBSTRING(id,4,7)),0) From Studentreg", Connection)
            Dim reader As SqlDataReader = myCommand.ExecuteReader
            reader.Read()
            STid= reader.Item(0) + 1
            Textbox1.Text = "ST" +  STid.ToString()
            reader.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

[poojavb]

U can try this too for a alphanumeric or if u need only numeric increment then delete the red part

Try
            Dim myCommand As SqlCommand
            Dim STid As String
            myCommand = New SqlCommand("select ISNULL(Max(SUBSTRING(id,4,7)),0) From Studentreg", Connection)
            Dim reader As SqlDataReader = myCommand.ExecuteReader
            reader.Read()
            STid= reader.Item(0) + 1
            Textbox1.Text = "ST" +  STid.ToString()
            reader.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

use this for numeric increment...the datatype in database should be an integer

Try
            Dim myCommand As SqlCommand
            Dim STid As String
            myCommand = New SqlCommand("select MAX(ID) From Studentreg", Connection)
            Dim reader As SqlDataReader = myCommand.ExecuteReader
            reader.Read()
            STid= reader.Item(0) + 1
            Textbox1.Text = STid.ToString()
            reader.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

[trisha0906]

hi, my friend was able to solve the problem using the mssql.
at the student number, Identity Specification = Yes
thank you all for your help!