CODEWITHSUNDEEP
c#sql-server
Tony
Tony

Reputation: 1869

Inserting a row into a sql table in c# with autoincrement primary key

How do I execute a table insert command when the ID column is the primary key and set to autoincrement?

I've tried the following:

      using (SqlConnection conn = new SqlConnection(connstring))
        {
            SqlCommand identChange = conn.CreateCommand();
            identChange.CommandText = "SET IDENTITY_INSERT table1 ON";
            conn.Open();
            string commtext = @"Insert INTO table1 (ID, Username)
                       SELECT @ID, @User";
            SqlCommand comm = new SqlCommand(commtext, conn);
            comm.Parameters.AddWithValue("@ID", -1);
            comm.Parameters.AddWithValue("@User", loggedinuser);
            identChange.ExecuteNonQuery();
            comm.ExecuteNonQuery();
            conn.Close();
        }

but regardless of what value is put into ID (i've tried 0 and -1), it creates a new row with that value under the ID column. As a result, when I try to insert the next row, it gives me an error since now I have two rows with the same ID.

Upvotes: 1

Views: 12617

Answers (3)

lolol
lolol

Reputation: 4390

You just don't have to mention the column if it is a autoincrement column.

string commtext = @"Insert INTO table1 (Username) SELECT @User";
SqlCommand comm = new SqlCommand(commtext, conn);
comm.Parameters.AddWithValue("@User", loggedinuser);

Upvotes: 5

Nicholas Carey
Nicholas Carey

Reputation: 74375

You say something like this:

int addUser( string userName )
{
  if ( string.IsNullOrWhiteSpace(userName) throw new ArgumentException("invalid user name" , "userName") ;
  int user_id ;
  string connectString = GetSomeConnectString() ;

  using ( SqlConnection connection = new SqlConnection( connectString ) )
  using ( SqlCommand    cmd        = connection.CreateCommand() )
  {

    cmd.CommandType = CommandType.Text ;
    cmd.CommandText = @"
insert dbo.user ( user_name ) values ( @user_Name )
select user_id = @@SCOPE_IDENTITY
" ;

    cmd.Parameters.AddWithValue( "@user_name" , userName ) ;

    connection.Open() ;
    user_id = (int) cmd.ExecuteScalar() ;
    connection.Close() ;
  }

  return user_id ;
}

any column with an identity property (or autoincrementing column) can't be specified on the insert. You have to specify the columns whose value you are supplying. Any columns not supplied must be nullable, have a default constraint or be an auto-incrementing column.

Upvotes: 1

Gratzy
Gratzy

Reputation: 9389

Unless you want to explicitly set the ID you don't set IDENTITY_INSERT ON otherwise use the auto incremented value.

 using (SqlConnection conn = new SqlConnection(connstring))
    {           
        conn.Open();
        string commtext = @"Insert INTO table1 (Username)
                   SELECT @User";
        SqlCommand comm = new SqlCommand(commtext, conn);
        comm.Parameters.AddWithValue("@User", loggedinuser);
        identChange.ExecuteNonQuery();
        comm.ExecuteNonQuery();
        conn.Close();
    }

Upvotes: 0

Related Questions