How to insert value into primary key identity column in SQL through C#?

(My problem solved. As almost everyone said I had to remove @OB_ID and left it to SQL to assign value.)

I want to insert a new record into a table in SQL database through C# code. The primary key is identity. How can I make Visual Studio understood that the column is identity so SQL Server must set the value. Here’s the related code:

SqlConnection sqlc = new SqlConnection();
sqlc.ConnectionString = "Data Source=. ; Database=LDatabase; Integrated Security=true;";

SqlCommand cmd2 = new SqlCommand("INSERT INTO Order_Book VALUES(@OB_ID, @OB_Title, @OB_Author, @OB_TranslatedBy, @OB_Publisher)", sqlc);//@OB_ID is indentity primary key
            cmd2.Parameters.AddWithValue("@OB_ID", );//What should I assign to it?
            cmd2.Parameters.AddWithValue("@OB_Title", "%" + txtboxbook.Text + "%");
            cmd2.Parameters.AddWithValue("@OB_Author", "%" + txtboxAuthor.Text + "%");
            cmd2.Parameters.AddWithValue("@OB_TranslatedBy", "%" + txtboxTranslator.Text + "%");
            cmd2.Parameters.AddWithValue("@OB_Publisher", "%" + txtboxPublisher.Text + "%");
            sqlc.Open();
            cmd2.ExecuteNonQuery();
            sqlc.Close();

Any help would be appreciated.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

you can ignore IDENTITY column as it will be inserted by default.

Try This

SqlConnection sqlc = new SqlConnection();
sqlc.ConnectionString = "Data Source=. ; Database=LDatabase; Integrated Security=true;";
SqlCommand cmd2 = new SqlCommand("INSERT INTO Order_Book VALUES(@OB_Title, @OB_Author, @OB_TranslatedBy, @OB_Publisher)", sqlc);//@OB_ID is indentity primary key

cmd2.Parameters.AddWithValue("@OB_Title", "%" + txtboxbook.Text + "%");
cmd2.Parameters.AddWithValue("@OB_Author", "%" + txtboxAuthor.Text + "%");
cmd2.Parameters.AddWithValue("@OB_TranslatedBy", "%" + txtboxTranslator.Text + "%");
cmd2.Parameters.AddWithValue("@OB_Publisher", "%" + txtboxPublisher.Text + "%");
sqlc.Open();
cmd2.ExecuteNonQuery();
sqlc.Close();

Method 2

You don’t need to add anything as SQL Server will automatically increment and assign the Identity field. So, simply remove @OB_ID and SQL Server will know to add the next key in sequence, so…

SqlConnection sqlc = new SqlConnection();
sqlc.ConnectionString = "Data Source=. ; Database=LDatabase; Integrated Security=true;";

SqlCommand cmd2 = new SqlCommand("INSERT INTO Order_Book VALUES(@OB_Title, @OB_Author,     @OB_TranslatedBy, @OB_Publisher)", sqlc);
        cmd2.Parameters.AddWithValue("@OB_Title", "%" + txtboxbook.Text + "%");
        cmd2.Parameters.AddWithValue("@OB_Author", "%" + txtboxAuthor.Text + "%");
        cmd2.Parameters.AddWithValue("@OB_TranslatedBy", "%" + txtboxTranslator.Text + "%");
        cmd2.Parameters.AddWithValue("@OB_Publisher", "%" + txtboxPublisher.Text + "%");
        sqlc.Open();
        cmd2.ExecuteNonQuery();
        sqlc.Close();

Method 3

You souldn’t put the id in your request. SQLServer will add the good identity at insertion.

SqlConnection sqlc = new SqlConnection();
sqlc.ConnectionString = "Data Source=. ; Database=LDatabase; Integrated Security=true;";

SqlCommand cmd2 = new SqlCommand("INSERT INTO Order_Book VALUES( @OB_Title,    @OB_Author, @OB_TranslatedBy, @OB_Publisher)", sqlc);//@OB_ID is indentity primary key
        cmd2.Parameters.AddWithValue("@OB_Title", "%" + txtboxbook.Text + "%");
        cmd2.Parameters.AddWithValue("@OB_Author", "%" + txtboxAuthor.Text + "%");
        cmd2.Parameters.AddWithValue("@OB_TranslatedBy", "%" + txtboxTranslator.Text +  "%");
        cmd2.Parameters.AddWithValue("@OB_Publisher", "%" + txtboxPublisher.Text + "%");
        sqlc.Open();
        cmd2.ExecuteNonQuery();
        sqlc.Close();

Method 4

Replace Following

SqlCommand cmd2 = new SqlCommand("INSERT INTO Order_Book VALUES( @OB_Title,    @OB_Author, @OB_TranslatedBy, @OB_Publisher)", sqlc);
//@OB_ID is indentity primary key

With

SqlCommand cmd2 = new SqlCommand("SET IDENTITY_INSERT Order_Book ON;INSERT INTO Order_Book VALUES( @OB_Title,    @OB_Author, @OB_TranslatedBy, @OB_Publisher);SET IDENTITY_INSERT Order_Book OFF;", sqlc);
 //@OB_ID is indentity primary key

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0