Returning a parameter from a stored proc
-
Hi, I have a stored proc that at the end executes the following line: Select @ID In my C# I have the following to try and grab it: SqlParameter AgentID = new SqlParameter("@ID", SqlDbType.Int, 4); AgentID.Direction = ParameterDirection.ReturnValue; sqlCmd.Parameters.Add(AgentID); int NewAgentID = 0; try { sqlCon.Open(); sqlCmd.ExecuteScalar(); sqlCon.Close(); NewAgentID = int.Parse(sqlCmd.Parameters["@ID"].Value.ToString()); return NewAgentID; } In debug this seems to set NewAgentID to -1, however it should be a legitimately returned variable from the stored proc, any ideas what is wrong?
-
Hi, I have a stored proc that at the end executes the following line: Select @ID In my C# I have the following to try and grab it: SqlParameter AgentID = new SqlParameter("@ID", SqlDbType.Int, 4); AgentID.Direction = ParameterDirection.ReturnValue; sqlCmd.Parameters.Add(AgentID); int NewAgentID = 0; try { sqlCon.Open(); sqlCmd.ExecuteScalar(); sqlCon.Close(); NewAgentID = int.Parse(sqlCmd.Parameters["@ID"].Value.ToString()); return NewAgentID; } In debug this seems to set NewAgentID to -1, however it should be a legitimately returned variable from the stored proc, any ideas what is wrong?
You need to get C# to excute your stored procedure then get the return variable and store it in NewAgentID. I use Microsoft Database access application block. So in c# the code would look somehting like this.
int NewAgentId = int.Parse(SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Call for Stored Procedure").ToString());
-
Hi, I have a stored proc that at the end executes the following line: Select @ID In my C# I have the following to try and grab it: SqlParameter AgentID = new SqlParameter("@ID", SqlDbType.Int, 4); AgentID.Direction = ParameterDirection.ReturnValue; sqlCmd.Parameters.Add(AgentID); int NewAgentID = 0; try { sqlCon.Open(); sqlCmd.ExecuteScalar(); sqlCon.Close(); NewAgentID = int.Parse(sqlCmd.Parameters["@ID"].Value.ToString()); return NewAgentID; } In debug this seems to set NewAgentID to -1, however it should be a legitimately returned variable from the stored proc, any ideas what is wrong?
jamesc69 wrote:
I have a stored proc that at the end executes the following line: Select @ID
This means that you are returning the value of @ID. try some thing like this
sqlCon.Open();
object result = sqlCmd.ExecuteScalar();
sqlCon.Close();
if (result != null)
NewAgentID = int.Parse(result.ToString()); -
jamesc69 wrote:
I have a stored proc that at the end executes the following line: Select @ID
This means that you are returning the value of @ID. try some thing like this
sqlCon.Open();
object result = sqlCmd.ExecuteScalar();
sqlCon.Close();
if (result != null)
NewAgentID = int.Parse(result.ToString());