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 · Accepted Answer · 2013-06-13 08:40:42Z

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, 2013 at 8:40

answered Jun 13, 2013 at 8:28

CocLn

74210 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gzaxx · Accepted Answer · 2013-06-13 08:28:17Z

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, 2013 at 8:28

gzaxx

17.6k2 gold badges40 silver badges55 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related