从查询中检索单个值 [英] Retrieving single value from query

问题描述

我正在尝试根据字符串字段用户名从单个表中检索整数值。我已经尝试过使用存储的proc和直接文本进行尝试。当我执行存储的proc时，我得到正确的返回值；

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查询：

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

推荐答案

您没有正确引用参数。如果要添加名为USUsername的参数，则应在命令文本中使用@USUsername：

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

您还应该查看使用 using语法自动关闭数据库连接的情况。请在此处查看Scott Hanselman的示例-> http://www.hanselman.com/basp/WhyTheUsingStatementIsStBetterTs a>

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

这篇关于从查询中检索单个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！