Retrieving single value from query

ado.net, c#, sql, sql-server, t-sql

Solution

You are not referencing your parameter correctly. If you are adding a parameter named USUsername then in the command text you should use @USUsername:

public int GetUserRole(string CUSER)
{
    try
    {
        SQLCON = new SqlConnection(connectionString);
        SQLCON.Open();
        SQLCommand = new SqlCommand();
        SQLCommand.CommandType = CommandType.Text;
        SQLCommand.Parameters.Add("USUsername", SqlDbType.VarChar).Value = CUSER;
        SQLCommand.CommandText = "SELECT USRole FROM tblUser WHERE USUsername = @USUsername";
        Int32 USRole = (Int32) SQLCommand.ExecuteScalar();

        return USRole;

    }
    catch (Exception)
    {
        HttpContext.Current.Response.Redirect("~/ErrorRedirect.aspx", false);
        return 0;
    }
}

Your stored procedure will also need updating as the parameter name here should also match and you don't need the return variable.

ALTER PROCEDURE [dbo].[spGetUserRole]
-- Add the parameters for the stored procedure here
@USUsername VARCHAR(50)

AS
BEGIN

-- Add the T-SQL statements to compute the return value here
SELECT tblUser.USRole FROM tblUser WHERE USUsername = @USUsername

END

You should also look at using the "using" syntax to automatically close your database connections. See Scott Hanselman's example here - http://www.hanselman.com/blog/WhyTheUsingStatementIsBetterThanASharpStickInTheEyeAndASqlConnectionRefactoringExample.aspx

Problem

I'm attempting to retrieve an integer value from a single table, based on the string field username. I've tried it using a stored proc, and direct text. When I execute the stored proc, I get the proper return value; however, the proper result doesn't come through. Here are both sets of code - Direct text - ``` public int GetUserRole(string CUSER) { try { SQLCON = new SqlConnection(connectionString); SQLCON.Open(); SQLCommand = new SqlCommand(); SQLCommand.CommandType = CommandType.Text; SQLCommand.Parameters.Add("USUsername", SqlDbType.VarChar).Value = CUSER; SQLCommand.CommandText = "SELECT USRole FROM tblUser WHERE USUsername = CUSER"; Int32 USRole = (Int32) SQLCommand.ExecuteScalar(); return USRole; } catch { HttpContext.Current.Response.Redirect("~/ErrorRedirect.aspx", false); return 0; } } ``` SQL query: ``` ALTER PROCEDURE [dbo].[spGetUserRole] -- Add the parameters for the stored procedure here @username VARCHAR(50) AS BEGIN -- Declare the return variable here DECLARE @USRole as int -- Add the T-SQL statements to compute the return value here SELECT @USRole = tblUser.USRole FROM tblUser WHERE USUsername = @username -- Return the result of the function RETURN @USRole END ```

Original source