3

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) )";
Destiny
  • 55
  • 4
  • `SET IDENTITY_INSERT ... ON` says to the system that *you* are taking responsibility for inserting identity values, rather than relying on the system to do so. – Damien_The_Unbeliever Mar 02 '20 at 08:17
  • Set column No int IDENTITY(1,1) PRIMARY KEY, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.https://stackoverflow.com/questions/10991894/auto-increment-primary-key-in-sql-server-management-studio-2012 – Joel Dharansingh Mar 02 '20 at 08:20
  • I need use that code, because if I can't use I get error like this : "Cannot insert explicit value for identity column in table 'TableName' when IDENTITY_INSERT is set to OFF." – Destiny Mar 02 '20 at 08:31
  • If you want SQL Server to use identity for the `No` column, why are you *also* trying to insert a value yourself into that column? – Damien_The_Unbeliever Mar 02 '20 at 08:36
  • This code's main problem is not identity but SQL injection attack which is a much bigger problem. And the two problems might be connected, parse the values using parameters. – Cetin Basoz Mar 02 '20 at 08:37

1 Answers1

0

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();
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • Thanks for help :) I wanna Ask one more question. If I delete Identity number like 8, its start insert value from 9. How can I start Identity number 8? – Destiny Mar 02 '20 at 10:16
  • @Destiny you can't and shouldn't; that isn't how an identity works; you don't reuse them - otherwise when someone asks for "record 8", you don't know whether they mean "the old record 8, that was deleted, and I should report 'not found'", vs "the new record 8"; the *really* fun bit is: an identity can be issued but rolled back in a transaction, so it is entirely normal and expected for there to be gaps occasionally – Marc Gravell Mar 02 '20 at 10:18