CODEWITHSUNDEEP
c#asp.netsql-serverado.net
Behnaz_Mardanzadeh
Behnaz_Mardanzadeh

Reputation: 35

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.

Upvotes: 0

Views: 11825

Answers (4)

Kamlesh
Kamlesh

Reputation: 529

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

Upvotes: 0

binard
binard

Reputation: 1794

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

Upvotes: 0

Belogix
Belogix

Reputation: 8147

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

Upvotes: 0

Sudhakar Tillapudi
Sudhakar Tillapudi

Reputation: 26209

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

Upvotes: 1

Related Questions