Friday, March 9, 2012

problem when inserting

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

here is the code :

protectedvoid Button2_Click(object sender,EventArgs e)

{

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 ?

Tongue Tied

|||modify your command definition line to:SqlCommand comm = new SqlCommand("insert into [Customer](Name) values(@.custName)", conn); and pass only customer name parametercomm.Parameters.AddWithValue("@.custName", TextBox1.Text)IF you do this ID value will be assigned by database itselfIf you would like to use you logic you have to allow to insert identities into table by running:SqlCommand comm = new SqlCommand("SET IDENTITY_INSERT dbo.[customer] ON insert into [Customer](Name,Id) values(@.custName,@.custId) SET IDENTITY_INSERT dbo.[customer] OFF", conn); but you have to be sure that Identity you try to insert does not exists in table before you do insert.|||

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)"

|||

Yes

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