Auto number increasement

[kimlong2012]

Dear all programmer;

I build an application using vb.net and sql sever. I want to
create auto number increasement like (STAFF-0001). For STAFF-000
mean like default value and number is increasement. example: STAFF-0001,
STAFF-0002, STAFF-0003.

Please help me thank.

Best regard,
Kimlong

[poojavb]

Just an idea...

I had to save the data as INP1 - first 3 characters and last integer

We cant save the alphanumeric as integer

but what I had done was....I had one temp table....which had the integer value...
every time I need to increment the value I used to check the value in that table and update in my new table....that had datatype as varchar...so everytime it used to get incremented correctly....after the insert statement then update the temp table by 1....

[jezguitarist30]

Hi Kim,

I would suggest that you count the record in your database and put it in a textbox.

for example:

  cmd = New Sqlcommand("Select count(staffid) from tblStaff",con)
  dr = cmd.ExecuteReader

  While dr.read

  Textbox1.text = dr.item(0)

  end while

  'Use to Increment the current Value
  Textbox1.text = val(Textbox1.text) + 1

and then after you have the incremented value you can now format to what ever you like.

for example

  Textbox1.text = "5"

then if you want to format it to STAFF-0005

try this

  Textbox1.text = String.Format("{0:0000}", Textbox1.text)

or

  Textbox1.text = Textbox1.text.ToString("0000")

After you have format the value of "5" to "0005"
concatenate to "STAFF-" just like this

  Textbox1.text = "STAFF-" & Textbox1.text

and you're done.

Please marked answer if it helps.

[satti]

Suppose you are saving data in TB_STAFF and the column field name to save numbers is num and display textbox on form is txt1. so here we go...
Call this funcion on form_load.

Private Sub AUTOCODE()
        myRead.myfill("SELECT num FROM TB_STAFF ORDER BY num DESC")
        If myRead.dt.Rows.Count > 0 Then
            txt1.Text = Val(myRead.dt.Rows(0).Item(0)) + 1
        Else
            txt1.Text = "STAFF-0001"
        End If
    End Sub

[kimlong2012]

Yes,Thank jezguitarist30. It's very helpful.

[poojavb]

if u use the code what jezguitarist30 has given u can face one issue that is...
Suppose there are 10 Staffs in the database i.e. Staff ID - 0010 will be the last id in ur database
and in case Staff ID - 0003 is deleted
and then again if u go and add some new id then according to ur count it will get 10
and then it will be difficult to add since it will be a primary key issue....
think on this....

[jezguitarist30]

Poo was right, I forgot toing of that problem....

So Instead of counting the records in your database you can just search the last record
and after that Increment it.

Ex:

cmd = New Sqlcommand ("Select StaffID from tblStaff order by StaffID desc",con)

Best Regards to Poo for Pointing the future issue.