how to create EMP01 like Emp idin sql server 2005

Posted by reddysankark-13471 under Sql Server on 10/26/2012 | Points: 10 | Views : 2879 | Status : [Member] | Replies : 9
hi,

how to create EMP01 like Emp id in sql server 2005 or in ASp.net

sankarreddy


Responses

Posted by: Santhi on: 10/27/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: reddysankark-13471 on: 10/27/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Santhi on: 10/27/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: reddysankark-13471 on: 10/27/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Santhi on: 10/28/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Naraayanan on: 11/5/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Sourabh07 on: 11/6/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Lokesh8143 on: 2/12/2015 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Bandi on: 2/12/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Login to post response