I was having some trouble with this error, but i added the command, and now i receive another error (Connection property has not been initialized.). Please, check out my code:
protected void btnAdd_Client(object sender, System.EventArgs e)
{
SqlConnection con = new SqlConnection(@"Data Source=JOHN-PC\SQLEXPRESS;Initial Catalog=subiect1;Integrated Security=True");
try
{
con.Open();
SqlCommand command = new SqlCommand("SET IDENTITY_INSERT Clienti ON");
command.ExecuteNonQuery();
SqlCommand cmd = new SqlCommand("insert into Clienti(ID, Nume, Prenume, Data_Nasterii, Adresa_mail) values(@ID, @Nume, @Prenume, @Data_Nasterii, @Adresa_mail)", con);
cmd.Parameters.AddWithValue("@ID", txtID.Text);
cmd.Parameters.AddWithValue("@Nume", txtNume.Text);
cmd.Parameters.AddWithValue("@Prenume", txtPrenume.Text);
cmd.Parameters.AddWithValue("@Data_Nasterii", txtDataNastere.Text);
cmd.Parameters.AddWithValue("@Adresa_mail", txtAdresamail.Text);
int count = cmd.ExecuteNonQuery();
if (count == 1)
lblMsg.Text = "Clientul [" + txtID.Text + "] a fost adaugat!";
else
lblMsg.Text = "Clientul nu a putut fi adaugat!";
}
catch (Exception ex)
{
lblMsg.Text = "Error --> " + ex.Message;
}
finally
{
SqlCommand comm = new SqlCommand("SET IDENTITY_INSERT Clienti OFF");
comm.ExecuteNonQuery();
con.Close();
}
}
This is a function which adds new clients in my local database. As you can see, i placed the set_identity commands before and after the actual adding client code. What do i miss here?
2 Replies
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Javeed M ShaikhPosted Jan 9, 2014, 4:22 PM
you have opened the connection, but did not specify the command on the connection to use. As you can see from your code the following line is missing the connection to use:
SqlCommand command = new SqlCommand("SET IDENTITY_INSERT Clienti ON");
Please add the connection to the SQLCommand constructor or add the following lines:
command.Connection = con;
command.ExecuteNonQuery();
Regards,
Javeed
Dhirendra MisraPosted Jan 9, 2014, 11:12 PM
As Javeed mentioned is correct you should assign connection to command object. Javeed catch well your issue.
But as being developer I would suggest this is not good practice to keep DB statements such as set Identity, inserts etc. at code level. Better to move db statements to stored procedure and call SP from code behind. It will boost up performance as well.
Rest depends on your application.