Require to insert alphanumeric value based on identity clmn

Gowtham

10y865Learn iOS Programming

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,

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke10y ago
Accepted 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 Sontakke10y ago
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?
Gowtham10y ago
Thankyou sir.
Gowtham10y ago
Sir, nitin sontakk,
Code behind the form
cmd.CommandText =
"insert into employee_record(Employee_dob,Employee_age,Employee_sal,Employee_qual,Employee_state,Eployee_add,Employee_martial,employee_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(0,1) 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.
Nitin Sontakke10y ago
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!