Sql Identity Inserting Value Only 0 in C#

Question

I try using Sql Identity in my table for No column. If I try insert value from table with c#, always insert "0" and Identity not working. How can I fix this?

SqlCommand Cmd_IdentityOpen;
string SqlIdentityOpen = "SET identity_insert " + cbbox_ltable.Text + " on";
SqlIdentityOpen += " INSERT INTO " + cbbox_ltable.Text + " ([No], [Computer ID], [Name Surname] ) Values ('" + txtbox_lno.Text + "' , '" + cbbox_lcid.Text + "' , '" + txtbox_lnamesurname.Text + "' )";
Cmd_IdentityOpen = new SqlCommand(SqlIdentityOpen, con);
con.Open();
Cmd_IdentityOpen.ExecuteNonQuery();
con.Close();

Table Code

SqlTableCreate += "CREATE TABLE " + txtbox_ltablename.Text + " ([No] int not null IDENTITY(1,1) PRIMARY KEY, [Computer ID] nvarchar(50), [Name Surname] nvarchar(50) )";

Answer 1

If users can and do supply the [No] value, then: it isn't an IDENTITY in the SQL Server sense (it could still be a primary key, though, etc); so decide:

• if you want it to be an IDENTITY, don't let the users provide it, don't include it in the INSERT, and use SCOPE_IDENTITY() to find out what you got after inserting
• if you don't need it to be an IDENTITY, don't declare it as one

Mixing and matching rarely works.

Unrelated, but you really, really need to look into SQL injection and parameterization; that code is actively dangerous. A safe command is something more like:

cmd.CommandText @"
INSERT INTO YourTable ([Computer ID], [Name Surname])
VALUES (@id, @name);
SELECT SCOPE_IDENTITY();");
cmd.Parameters.AddWithValue("@id", cbbox_lcid.Text);
cmd.Parameters.AddWithValue("@name", txtbox_lnamesurname.Text);
var newId = (int)cmd.ExecuteScalar();