C# : SQL Server query issue

Question

I create a table in SQL Server Management Studio with this code:

CREATE TABLE contact(
ID INT IDENTITY NOT NULL,
FIRSTNAME VARCHAR(100),
LASTNAME VARCHAR(100)
)

and in C# I used this code:

SqlConnection sc = new SqlConnection("Data Source=.\\SQLSERVER; Initial Catalog=BOSS; Integrated Security=TRUE");

SqlDataAdapter sd = new SqlDataAdapter();

sd.InsertCommand = new SqlCommand("INSERT INTO contact VALUES(@ID, @FIRSTNAME, @LASTNAME)");

sd.InsertCommand.Parameters.Add("@ID", SqlDbType.Int).Value = textBox1.Text;
sd.InsertCommand.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value = textBox2.Text;
sd.InsertCommand.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value = textBox3.Text;

sc.Open();

sd.InsertCommand.ExecuteNonQuery();

sc.Close();

but when I add the values to the database I get the error:

"ExecuteNonQuery: Connection property has not been initialized"

and I fixed it by adding sc to my first insertcommand, but when I run the program I got another error :

An explicit value for the identity column in table 'contact' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Answer 1

Do it this way:

using(SqlConnection sc = new SqlConnection("Data Source=.\\SQLSERVER; Initial Catalog=BOSS; Integrated Security=TRUE"))
{

  using(SqlCommand command = new SqlCommand())
  {
    command.CommandText = "INSERT INTO contact (FirstName, LastName) VALUES(@FIRSTNAME , @LASTNAME"); 
    command.CommandType = CommandType.Text;
    command.Connection = sc;

    command.Parameters.AddWithValue("@FIRSTNAME", textBox2.Text); 
    command.Parameters.AddWithValue("@LASTNAME", textBox3.Text); 

    sc.Open();

    command.ExecuteNonQuery();

  }

}

Important things to note:

1) Set your table up to have the Id column as an identity column and set autoincrement to true. This will automatically generate a numeric id when you insert

2) You are trying to insert into an identity column - you can't actually do this unless you enable identity inserts. I wouldn't bother - just use an autoincrement column and let the database control the id generation step.

You can generate your table this way:

CREATE TABLE Contact
 (
 Id int PRIMARY KEY IDENTITY,
 FirstName varchar(100),
 LastName varchar(100)
 )

to get an autoincrementing primary key.

3) You don't need the SqlDataAdapter.

Answer 2

You need to pass the connection you intend to use to the SqlCommand

InsertCommand = new SqlCommand("INSERT INTO contact VALUES(@ID , @FIRSTNAME , @LASTNAME)", sc);

You need to dispose of your Connection and command as well. The standard pattern for doing this is:

using (SqlConnection conn = new SqlConnection("Data Source=.\\SQLSERVER; Initial Catalog=BOSS; Integrated Security=TRUE")){
  conn.Open();  
  using (SqlCommand command = new SqlCommand(sqlString, conn)){
     //stuff...
     command.ExecuteNonQuery();
  }
}