can't get output parameters from a SqlParameter inside a query

Question

I'm doing a simple INSERT query into my database. The column id have auto-generated ID and i need it for another query. I read i can get the generated id using a parameter in output direction, so i wrote this code:

string id;
queryString = "INSERT INTO tr_text (form, item, enabled, programID) VALUES (@myForm, @myItem, 1, @myProgramID)";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(queryString, connection);
    connection.Open();
    command.CommandText = queryString;
    command.Parameters.Add("@myForm", SqlDbType.NVarChar, 50).Value = form;
    command.Parameters.Add("@myItem", SqlDbType.NVarChar, 50).Value = item;
    command.Parameters.Add("@myProgramID", SqlDbType.Int).Value = 1;
    SqlParameter param = new SqlParameter("@id", SqlDbType.Int, 0, "id");
    param.Direction = ParameterDirection.Output;
    command.Parameters.Add(param);
    command.ExecuteNonQuery();
    id = command.Parameters["@id"].Value.ToString();
 }

My query is executed correctly (i have the correct new record in my database) but my id variable is still empty! how can i make that code work?

CocLn · Answer 1 · 2013-06-13T08:40:42.273

3

Please change your query like that;

 INSERT INTO tr_text (form, item, enabled, programID) 
 OUTPUT INSERTED.ID
 VALUES (@myForm, @myItem, 1, @myProgramID)

and use ExecuteScalar() instead of ExecuteNonQuery() like that;

 Int32 id= (Int32) command.ExecuteScalar();

edited Jun 13 '13 at 08:40

answered Jun 13 '13 at 08:28

CocLn

742
10
15

score 1 · Accepted Answer · answered Jun 13 '13 at 08:28

1

Change your query, because you do not set @id to any value

INSERT INTO tr_text (form, item, enabled, programID) VALUES (@myForm, @myItem, 1, @myProgramID) 
set @id = SCOPE_IDENTITY();

answered Jun 13 '13 at 08:28

gzaxx

17,312
2
36
54

can't get output parameters from a SqlParameter inside a query

2 Answers2