how to create EMP01 like Emp idin sql server 2005

Hi,

First, you have to create the ID in asp.net:
int i = 1;
string ID= "Emp" + i;
insert this value in the Employee ID column .
Now you can autogenerate the employee code.
select isnull(max(right(nvrempcode,3)),0)+1 as nvrempcode from tablename.
If you have any doubts, ask me.

Thanks && Regards,
V. Santhi.

Thanks & Regards,
Santhi .V

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

hi, i didn't get this.could you please explain with example

sankarreddy

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

Tell me your question clearly.
I will explain you according to that with an example.

Thanks && Regards,
V. Santhi

Thanks & Regards,
Santhi .V

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

I need coding for EMP will be my prefix and number will digit start with 0001.my First registered user id will be this EMP0001.Anthoner Employee registraion Id will be EMP0002.How to this in C# or sql server 2005

sankarreddy

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

First time you time you have to generate the ID as EMP0001.

For Example : Get this value in a string.
first time check whether there is a record in the database by using the query.

Query = "select * from tablename.
Get the values in a datatable.
if the count value is equal to zero
then follow the following step:
if(dt.rows.count == 0)
{
string strID = "EMP0001";
}

Insert this value in the Employee ID column.
so, Next time the value will not be equal to zero.
If this value has been inserted in the table, you have to use the following query to autogenerate the ID.


select isnull(max(right(nvrempcode,4)),0)+1 as nvrempcode from tablename.
This query will autogenerate the ID.


Thanks && Regards,
V. Santhi.

Thanks & Regards,
Santhi .V

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

Hi,
try this way

Query:

 Select max(empid) from tbl_employee_master

Store max of EmpId in string.
Split a string using string array.
Eg:

string empId ="EMP0001";

string spiId[] = emplId.Split('p');

Now,
You get

  splid [1] = 0001;

Now , you Increase a value like this

int currentId = Convert.Toint32(splid[1]) +1;

and
concanate with Prefix value

string lastId= "EMP" + Convert.ToString(currentId )

Regards,
Lakshmi Naraayanan.S
http://dotnettechrocks.blogspot.in/
http://abaprocker.blogspot.com/

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

hi..to all

please try the following code as an example........

and edit it accordingly.............

Create table #temp 

(

	pk_id int identity,

	empcode varchar(50) default 'EMP',

	empname varchar(50)

)



Insert into #temp (empname) values('A')



Declare @empcode varchar(10)



set @empcode = @@IDENTITY



while Len(@empcode) < 5 

begin



	set @empcode = '0' + @empcode

	

end



Update #temp 

set empcode = empcode + cast(@empcode as varchar)

where pk_id=@@identity



Select * from #temp 



drop table #temp 

Sourabh07

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

sir what is nvrempcode in that answer.
i want to create employee id like EMP001 in database

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

I think, nvrempcode is the Column name of Employee table

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator