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();
  }
}

Answer 3

There's no relation between SqlConnection and SqlDataAdapter.

In fact, you don't need SqlDataAdapter. You only need SqlConnection and SqlCommand, for which you must use the constructor overload that accepts a connection as well.

Answer 4

Since ID is an INT IDENTITY field, you shouldn't (and can't) insert values into it. But if you use your "generic" INSERT statement without explicitly specifying which columns to insert values into, your INSERT statement will attempt to insert data into all columns - including ID, which you cannot insert anything into.

The solution (which I recommend for use always) is to explicitly define which columns to insert values into:

INSERT INTO dbo.contact(FirstName, LastName) VALUES(@FIRSTNAME, @LASTNAME)

This also works if you need to change your table and add another column - your original statement will fail, since the table suddenly now would have four columns, but your statement would only provide three values. If you explicitly define which columns you want to provide values for, your statement is more robust and works better.

So your complete code should look like this:

using(SqlConnection sc = new SqlConnection("Data Source=.\\SQLSERVER; Initial Catalog=BOSS; Integrated Security=TRUE"))
using(SqlCommand cmdInsert = new SqlCommand("INSERT INTO dbo.Contact(FirstName, LastName) VALUES(@FIRSTNAME, @LASTNAME)", sc))
{
   cmdInsert.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar, 100).Value = textBox2.Text;
   cmdInsert.Parameters.Add("@LASTNAME", SqlDbType.VarChar, 100).Value = textBox3.Text;

   sc.Open();
   cmdInsert.ExecuteNonQuery();
   sc.Close();
}

Answer 5

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

Answer 6

You can do either ways:

a. Set you connection object to the adapter's insertcommand connection:

sd.InsertCommand.Connection = sc; 

Or

b. Pass your connection object while initializing insert command as below:

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