Retrieving single value from query

Question

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

Answer 1

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