Insert data into table and get ID from increased primary key

Question

In my ASP.NET application I have a need to do something like this: First, get data from a user, second insert this data into SQL Server database table, then get the ID value from that table (which is autoincremented) and then use it in another insert into another table.

Now, I already found out that in order to do this, I need to use T-SQL OUTPUT clause. However, when I try to do this I get this error:

I tried to follow the advice from those posts: Get output parameter value in ADO.NET and Use SQL Server Stored procedure output in asp.net c# but I couldn't make it work.

I'm not using stored procedure, but the single SQL statement instead. Here it is:

INSERT INTO BS_product_sets (User_id, Set_name, Set_id, Products_count) 
OUTPUT inserted.ID INTO @NEWID 
VALUES (@UID, @SNAME, @SPREFIX, @PCOUNT)

I also tried this:

DECLARE @NEWID int; 

INSERT INTO BS_product_sets (User_id, Set_name, Set_id, Products_count) 
OUTPUT inserted.ID INTO @NEWID 
VALUES (@UID, @SNAME, @SPREFIX, @PCOUNT)

But then it gets even weirder because the error I get is:

Does anyone of you know how to resolve it? Here is the rest of my code in C#:

public void InsertProductAutoBinds(int user_id, int parameters_count, List<int> cat_ids, List<string> names, List<string> values, string set_name, string set_id)
{
    SqlConnection connection = new SqlConnection(DatabaseConstants.ConnectionString);

    // Adding set to database
    SqlCommand command = new SqlCommand(DatabaseConstants.InsertSet, connection);
    command.Parameters.AddWithValue("@UID", user_id);
    command.Parameters.AddWithValue("@SNAME", set_name);
    command.Parameters.AddWithValue("@SPREFIX", set_id);
    command.Parameters.AddWithValue("@PCOUNT", 0);
    command.Parameters.Add("@NEWID", SqlDbType.Int).Direction = ParameterDirection.Output;

    connection.Open();
    command.ExecuteNonQuery();

    int new_inserted_id = Convert.ToInt32(command.Parameters["@NEWID"].Value);

    connection.Close();

    // Adding binds
    command = new SqlCommand(DatabaseConstants.InsertAutoBinds1, connection);
    command.Parameters.AddWithValue("@UID", user_id);
    command.Parameters.AddWithValue("@SID", new_inserted_id);
    command.Parameters.AddWithValue("@P1ID", cat_ids[0]);
    command.Parameters.AddWithValue("@VN1", names[0]);
    command.Parameters.AddWithValue("@VS1", values[0]);

    if (parameters_count > 1)
    {
        command.CommandText = DatabaseConstants.InsertAutoBinds2;
        command.Parameters.AddWithValue("@P2ID", cat_ids[1]);
        command.Parameters.AddWithValue("@VN2", names[1]);
        command.Parameters.AddWithValue("@VS2", values[1]);
    }

    if (parameters_count > 2)
    {
        command.CommandText = DatabaseConstants.InsertAutoBinds3;
        command.Parameters.AddWithValue("@P3ID", cat_ids[2]);
        command.Parameters.AddWithValue("@VN3", names[2]);
        command.Parameters.AddWithValue("@VS3", values[2]);
    }

    connection.Open();
    command.ExecuteNonQuery();
    connection.Close();
}

score 3 · Accepted Answer · edited Feb 16 '20 at 19:05

If you look at the official MS documentation for INSERT INTO, you'd see:

OUTPUT INTO { @table_variable | output_table }

That means: the OUTPUT ... INTO .... clause only works with a table or table variable as its target - you cannot do an OUTPUT .... INTO @variable - that's just plain not supported.

So you need to

either do an OUTPUT ... INTO @tableVar and then select the first value from that table variable and return it
just use OUTPUT inserted.ID (without any INTO ....) and just grab the value being returned from the SQL command in your C# code - in this case, since data is being returned, you need to use .ExecuteScalar() instead of .ExecuteNonQuery() in your C# code

score 0 · Answer 2 · edited Feb 17 '20 at 00:58

0

If you have:

CREATE TABLE tab (Id Int IDENTITY, User_Id Int, ...)

DECLARE @NewID Int

INSERT INTO tab (User_Id,...) VALUES (123, ...)

SET @NewId = @@Identity

edited Feb 17 '20 at 00:58

Dale K

25,246
15
42
71

answered Feb 16 '20 at 23:28

ANDREI FOMITCHEV

84
3

Insert data into table and get ID from increased primary key

2 Answers2