ExecuteScalar SQLException:标量变量未声明 [英] ExecuteScalar SQLException: scalar variable not declared

问题描述

我正在尝试检查SQL Server的表中是否存在用户.我已经创建了连接字符串，打开了一个连接-

I'm trying to check if a user exists within a table in SQL-Server. I have created connection strings, opened a connection -

            var settings = ConfigurationManager.ConnectionStrings["BlogEngine"].ConnectionString;
            SqlConnection conn = new SqlConnection(settings);
            using (conn)
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                    var checkUser = "SELECT COUNT(*) FROM dbo.be_Users WHERE UserName = @UserName";
                    using (SqlCommand userquery = new SqlCommand(checkUser,conn))
                    {
                        var parma = userquery.Parameters;
                        parma.AddWithValue("@UserName", activedirectory.DisplayName);
                        int UserExist = (int)userquery.ExecuteScalar();

                        if (UserExist > 0)
                        {
                            //Username exist
                            AuthenticateUser(staffId, password, rmb);
                        }
                        else
                        {
                            //Username doesn't exist.
                            var sqlQuery = "INSERT INTO dbo.be_Users (BlogID, UserName, Password, LastLoginTime, EmailAddress, Department)" + "VALUES (@BlogID, @UserName, @Password, @LastLoginTime, @EmailAddress, @Department)";
                            using (SqlCommand qe = new SqlCommand(sqlQuery))
                            {

                                var parms = qe.Parameters;
                                parms.AddWithValue("@BlogID", Blog.CurrentInstance.Id.ToString());
                                parms.AddWithValue("@UserName", activedirectory.DisplayName); 
                                parms.AddWithValue("@Password", (passwordFormat == MembershipPasswordFormat.Hashed ? Utils.HashPassword(DEFAULT_PASSWORD) : DEFAULT_PASSWORD));
                                parms.AddWithValue("@LastLoginTime", DateTime.Now);
                                parms.AddWithValue("@EmailAddress", DEFAULT_EMAIL);
                                parms.AddWithValue("@Department", activedirectory.department);

                                qe.ExecuteNonQuery();
                            }
                            AuthenticateUser(staffId, password, rmb);
                        }
                    }conn.Close();
                }

我一直收到此错误-System.Data.dll中发生了类型为'System.Data.SqlClient.SqlException'的异常，但未在用户代码中处理

I keep getting this error - An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

其他信息:必须声明标量变量"@UserName".

Additional information: Must declare the scalar variable "@UserName".

该异常发生在 int UserExist =(int)userquery.ExecuteScalar(); 行.

请，我该如何解决?

推荐答案

由于您在此处添加了参数，

parma.AddWithValue("@UserName", activedirectory.DisplayName);

我的猜测是 activedirectory.DisplayName 是 null .ADO.NET中的一个奇怪的小问题是，未传递具有 null 值的参数.要将 null 传递给数据库，您需要传递 DBNull.Value .所以:

My guess would be that activedirectory.DisplayName is null. A curious glitch in ADO.NET is that parameters with null values are not passed. To pass a null down to the database, you need to pass DBNull.Value. So:

parma.AddWithValue("@UserName", ((object)activedirectory.DisplayName) ?? DBNull.Value);

或更长时间:

object displayName = activedirectory.DisplayName;
if(displayName == null) { displayName = DBNull.Value; }
parma.AddWithValue("@UserName", displayName);

但是！然后，这引发了一个问题:如果显示名称为<空> ，该代码应做什么 ?清楚显示的SQL查询不会以预期的方式运行.

However! This then invites the question: what should this code do if the display name is null? The SQL query shown clearly won't behave in the expected way.

这篇关于ExecuteScalar SQLException:标量变量未声明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！