I want to insert a recorde into my table (2 column) : name and Id
id is a primary key and this is the error MSG im getting everytime im trying to insert a record
Error:Cannot insert explicit value for identity column in table 'Customer' when IDENTITY_INSERT is set to OFF.
what is the problem
protectedvoid Button2_Click(object sender,EventArgs e)here is the code :
{
try
{
string str ="data source=.;integrated security=true; initial catalog=NH_Project2";SqlConnection conn =newSqlConnection(str);
SqlCommand comm =newSqlCommand("insert into [Customer](Name,Id) values(@.custName,@.custId)", conn);comm.CommandType =CommandType.Text;
comm.Parameters.AddWithValue("@.custName", TextBox1.Text);comm.Parameters.AddWithValue("@.custId", TextBox2.Text);conn.Open();
int rowaff = (int)comm.ExecuteNonQuery();
if (rowaff == 1){
Label4.Visible =true;Label4.Text ="one customer has added to our data base";}
else
{
Label4.Visible =false;Label4.Text ="not added try again";}
}
catch (Exception ex){
Response.Write("Error:" + ex.Message);
}
}
You need to understand IDENTITY columns. Please read up books online. If you want to explicitly insert a value into it you need to use SET IDENTITY_INSERT <Table> ON before the INSERT and set it to OFF after the insert. IF you want to let SQL Server handle the Id's you should remove the column from your INSERT list so SQL Server can do it for you.
|||"IF you want to let SQL Server handle the Id's you should remove the column from your INSERT list so SQL Server can do it for you."
how can I do that ? do you mean to call stored procedure and have it insert into that column?
thanks for your advice, any specific books that you would recommend ?
No just remove it from your INSERT list..Just insert the name, the ID will be inserted by SQL Server.
insert into [Customer](Name) values(@.custName)"
|||that was helpful
|||The problem is that your IDENTITY COLUMN is inserted automatically! If so the only thing you need is:
insert into [Customer](Name) values(@.custName)
When inserted, the new Customer is given an ID automatically!
SuperJB
No comments:
Post a Comment