Stored Procedure returns a 0 only when on the server - not when run locally

Question

I have the following code to return an id number from a database. It works fine when I run it locally, but when I put the files onto the server, while it does log the data into the DB, but it only returns a 0 and not the ID number.

HHID is an ID that autoincrements when I log the info to the DB.

SqlConnection conn = new SqlConnection("DB connection stuff");
SqlCommand cmd = conn.CreateCommand();
SqlDataReader reader;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "insert_workshop_requests";
cmd.Parameters.AddWithValue("@HHID", 1);
cmd.Parameters.AddWithValue("@username", usernameData.Text);
cmd.Parameters.AddWithValue("@name", nameData.Text);
cmd.Parameters.AddWithValue("@email", emailData.Text);

cmd.Parameters["@HHID"].Direction = System.Data.ParameterDirection.Output;

try
{
    conn.Open();
    reader = cmd.ExecuteReader();
    HHID = System.Convert.ToInt32(cmd.Parameters["@HHID"].Value);
}
catch (Exception exc)
{

}
finally
{
    if (conn.State != ConnectionState.Closed)
        conn.Close();
}

This is driving me crazy! Hoping someone can help, because while I've found loads of useful articles about SP that store a 0 rather than an ID, the issues are always when running locally as well. Mine working locally is making it very difficult to debug!

Thanks!

How does your stored procedure look like? How do you check that it returns 0 instead of your desired Id number? — Ian, May 10 '17 at 09:41
By "locally" do you mean both the app and the database are local? If so I think you stored procedure is different on the server and does not set the output variable. — Crowcoder, May 10 '17 at 09:50

score 1 · Accepted Answer · edited May 23 '17 at 12:18

You are using ExecuteReader for something that is not a SELECT. This has an interesting side effect on return parameters. Until you close the reader the values inside parameters of Direction = Output are not available.

SQL OUTPUT Stored Procedures not working with ExecuteReader

So you could close the reader, but really you should use ExecuteNonQuery for INSERT/UPDATE/DELETE queries

conn.Open();
reader = cmd.ExecuteReader();
reader.Close();
HHID = System.Convert.ToInt32(cmd.Parameters["@HHID"].Value);

or better

conn.Open();
int records = cmd.ExecuteNonQuery();
HHID = System.Convert.ToInt32(cmd.Parameters["@HHID"].Value);

score 0 · Answer 2 · answered May 10 '17 at 09:46

0

as far as i can understand, you are trying to return the id of the last entry, for that you can use this and bind the result to a variable and return it

SELECT SCOPE_IDENTITY()

i hope this will work out

answered May 10 '17 at 09:46

Chetan Talwar

111
1
6

Stored Procedure returns a 0 only when on the server - not when run locally

2 Answers2