Require to insert alphanumeric value based on identity clmn

Question

I have a table column

sno identity

emp_id varchar(30)

emp_address (30)

Sno emp_id emp_address emp_address

0001 Rs_0001 kolkatta park

0002 Rs_0002 mumbai gate

0003 Rs_0003 delhi others

Based on identity column sno the empid should be incremented like rs_0001,rs_0002...

Once the form is saved rs_0001 should stored in respective identity coulmn

regards

gowtham,

Nitin Sontakke · Answer

As I mentioned earlier if possible do all of this in a stored procedure it is much simpler. Google and you will know. It is very easy and elegant. And that's how you should do all the time. Once you have a stored procedure in place do all of this in the stored procedure itself. I notice you already have a computed column in place which is nice. But I am confused what is VL_? If you want to show rs_ why not just replace VL_ with rs_ for the purpose of display. In addition you have employee name at the end which should have been just after Id. There is no need to prefix every column with Employee when table itself is called Employee_record. You have almost all the columns nullable which is not good. At least name column should be not null.

Nitin Sontakke · Answer

Also there is no primary key. Have a primary key on sno column. Give proper and long column names just like others. Why not call it Serial_Number? Also why is it starting with 0? Any reason?

Nitin Sontakke · Answer

You cannot do it at the time of insert as you don't have value of identity column. If you don't want a very smart solution first of all perform the insert in the stored procedure and update that column just after the insert. You can also think of another thing. If it is always rs_ + identity column why do you want to store that information in another column? It is data redundancy and must be avoided at any cost. Why not just say rs_ + identity in the select itself and call that surrogate column as emp_id? Think about it. Remember to always keep the solutions elegant and difficult to fail. Hope you are getting the point!

Gowtham · Answer

Thankyou sir.

Gowtham · Answer

Sir nitin sontakk Code behind the form cmd.CommandText = insert into employee_record(Employee_dobEmployee_ageEmployee_salEmployee_qualEmployee_stateEployee_addEmployee_martialemployee_name) values ('+dat+'+age++salary+'+emp_qual+''+state+''+addr+''+state+''+emp_name+'); MessageBox.Show(cmd.CommandText); con.Open(); cmd.ExecuteNonQuery(); MessageBox.Show(Inserted sucessfully); cmd.CommandText = SELECT @@IDENTITY; //textBox1.Text = cmd.CommandText; object empid = cmd.ExecuteScalar(); textBox1.Text = VL_+empid.ToString(); MessageBox.Show(textBox1.Text+ The data are generated for this employee); con.Close(); While creating a table I was prefixed other column with identity column. At time of insert I can fetch last inserted row from identity column.I try to get alphanumeric value to show in text box it doesn't make any changes. CREATE TABLE [dbo].[employee_record]( [Sno] [int] IDENTITY(01) NOT NULL [Employee_id] AS ('VL_'+CONVERT([varchar](10)[Sno]0)) PERSISTED NOT NULL [Employee_dob] [date] NULL [Employee_age] [int] NULL [Employee_sal] [money] NULL [Employee_qual] [varchar](10) NULL [Employee_state] [varchar](10) NULL [Eployee_add] [varchar](50) NULL [Employee_martial] [nvarchar](10) NULL [employee_name] [varchar](50) NULL because I have to maintain two column sno and identity column I have a requirement to show likeEmployee_id is generated for this id is:rs_0001 after insert successfully. regards Gowtham.