VB.NET - how to dynamically increment the empid

Asked By rama chandran on 07-Sep-11 02:39 AM

hi,

in code behind i want to increment the empid with prefix EMP,

ex: EMP_01 next time EMP_02, like this

Reena Jain replied to rama chandran on 07-Sep-11 02:45 AM

Hi,

You can get last Inserted Auto Value using below query

SELECT IDENT_CURRENT('tablename')

It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

protected void Button1_Click(object sender, EventArgs e)

{
  SqlConnection con = new SqlConnection("Connection String");
  con.Open();
  SqlCommand comm = new SqlCommand("select IDENT_CURRENT('table1')", con);
  int currentAutoIncrementValue = Convert.ToInt32(comm.ExecuteScalar());
  con.Close();
  
//now add the id with EMP prefix like this
string empid="Emp_" +currentAutoIncrementValue.ToString()
}

Try this and let me know

Jitendra Faye replied to rama chandran on 07-Sep-11 02:49 AM

Here your empid is alphanumeric,

so you can not directly increment is, for this you have to get max numeric value in empid

like this-

emp_1
emp_5

here 5 is max.

after getting max numeric in empid , you can add one ,

finally you have to concate this created id to "emp_"

like this-

"emp_" + 5+1

Hope this will help you.

smr replied to rama chandran on 07-Sep-11 03:02 AM

Hi

here is an example

Public Class Form1

Public employee As New List(Of emp)

Public paychecks As New List(Of paycheck)

Public timecards As New List(Of timecard)

Public purchaseOrders As New List(Of PO)

'Public emp_cntr As Integer

'Public pay_cntr As Integer

'Public time_cntr As Integer

'Public po_cntr As Integer

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim admin As New salaried_emp(4)

Dim splash As New AcmeSplashScreen

employee.Add(admin)

'emp_cntr += 1

Dim emp1 As New comm_emp

employee.Add(emp1)

'emp_cntr += 1

Dim emp2 As New hourly_emp

employee.Add(emp2)

'emp_cntr += 1

Dim emp4 As New salaried_emp

employee.Add(emp4)

'emp_cntr += 1

Me.Hide()

splash.ShowDialog()

End Sub

End Class

refer
http://www.computing.net/answers/programming/problem-incrementing-a-purchase-order-number/25338.html
http://forums.asp.net/t/1643265.aspx

dipa ahuja replied to rama chandran on 07-Sep-11 03:16 AM

You have to first retrieve the last empid from the table:

For that fire the query as:

"SELECT MAX(empId) from emp"

Next Declare a string variable as:

string empID="EMP_"

Now store the value of the query result in this string variable with SqlCommand's ExcuteScalar method:

empID + = cmd.ExecuteScalar().ToString();

Thats it!

TSN ... replied to rama chandran on 07-Sep-11 04:45 AM

here is the sample try like this...

AutoGenerated Primary Key Values in sqlsserver:

For Creating a Autogenerated Sequence in sqlserver the better way is to create an identity column in the table for using it as a primary key and then by using this column as reference we can generate the autogenerated sequence that consists as combination characters and numericals

IdentityColumn	Username	CustomAuto Sequence
1	Abc	emp_0001
2	Xyz	emp_0002

Lets First create a function that accepts a integer and returns our specified Sequence i.e CustomAutoSequence.

Create function CustomSequence(@id int)

returns char(10)

begin

return 'emp_'+right( '0000' + convert( varchar(10) , @id ), 4) ;

end

By looking at the above function you can now think something like this passing the identity value to this we can generate the CustomAutoSequence.

But the Question is when to call this function and where to call this function.

The above question has two solutions.

First we can alter our customAutoSequence Column defination like the below shown

ALTER TABLE Table_Name

ALTER COLUMN CustomAutoSequence as dbo.CustomSequence(IdentityColumn)

Secondly we can use triggers to add the CustomAuto Sequence to the table when a insert statement is executed.

CREATE TRIGGER Insert_CustomAutoSequence ON TableName

after Insert as

update

TableName

TableName.CustomAutoSequence=dbo,CustomSequence(TableName.IdentityColumn)

from

TableName

inner join

inserted on TableName.IdentityColumn=Inserted.IdentityColumn.

Thats it ..Now you can insert the rows like shown below.

Insert into TableName(UserName) values ('mnp')