Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sanlen
Starting Member
29 Posts |
 Posted - 2008-10-19 : 23:34:08
|
| Hi All,In the table of SQL server, i have the emp_id which is needed to be auto generated in formatted of EM0001, EM0002, EM0003,....EM1000,...I am actually the newbie and really have no idea how to get this aut generated number start with 'EM'.Could you please advise?Thanks you very much for your time and any advise you might give me.Best Regards,SANLEN |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-19 : 23:40:11
|
| make an identity column in your table and make the sequence column a calaculated column based on identity column.It will be something like'EM' + RIGHT('0000'+ cAST(ID as varchar(5)),4) |
 |
|
|
sanlen
Starting Member
29 Posts |
Posted - 2008-10-20 : 00:13:26
|
| Hi,I have tried with this create table tbltest( id int identity(1,1), myid as 'EM' + RIGHT('0000'+ CAST(id as varchar(5)),4), name varchar(30))and it's working. however, what should i do if i want to take off myid column. It was error if i dont put myid as....Could you also please advise what is the way to learn SQL Server? i found that it's difficult to deal with by just reading book and follow its exercise.Thanks you very much for any further advise you may give me.Best Regards,SANLEN |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-20 : 01:23:02
|
quote:
Originally posted by sanlen
Hi,I have tried with this create table tbltest( id int identity(1,1), myid as 'EM' + RIGHT('0000'+ CAST(id as varchar(5)),4), name varchar(30))and it's working. however, what should i do if i want to take off myid column. It was error if i dont put myid as....Could you also please advise what is the way to learn SQL Server? i found that it's difficult to deal with by just reading book and follow its exercise.Thanks you very much for any further advise you may give me.Best Regards,SANLEN
take off myid column? why? didnt get that.The best way to learn sql server is to learn basics from site like www.w3schools.com then try out solutions yourselves and ask in forums like thais whatever doubt you come across. get solution, understand and learn it and use it in future. |
|
|
|
Kotti
Posting Yak Master
129 Posts |
Posted - 2008-10-20 : 03:03:41
|
| public static string PrtyGenId() { SqlConnection con = new SqlConnection(sConnectionString); try { con.Open(); string sSelect = "select max((prtyGenId)) from table name"; SqlCommand cmd = new SqlCommand(sSelect, con); string genValue = Convert.ToString(cmd.ExecuteScalar()); if (genValue == "") { genValue = "EM0001"; } else { int genInt = Convert.ToInt32(genValue.Substring(2, genValue.Length - 2)); genInt = (genInt) + 1; genValue = "EM" + genInt; } return genValue; } catch { throw; } finally { con.Close(); } } |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-10-21 : 07:43:52
|
| please disregard Kotti's idea. It will never work properly. Identities & computed columns (if you must) are the way to go. And yes, you will have gaps. |
|
|
|
|
|
|
|
|
Need help with auto generated id