检查用户名是否存在于SQLExpress中

本文关键字：SQLExpress 存在 是否 用户 检查 | 更新日期: 2023-09-27 18:19:52

错误显示：ExecuteScalar:Connection属性尚未已初始化并存在=（int）cmd.ExecuteScaler（）>0；

         bool exists = false;
         using (SqlCommand cmd = new SqlCommand("select * from [Users] where UserName = @UserName"))
         {
             cmd.Parameters.AddWithValue("UserName", tbUserName.Text);
             exists = (int)cmd.ExecuteScalar() > 0;
         }
         if (exists)
         {
             lblUserName.Text = "This username has been used by another user.";
         }
         else
         {
             SqlConnection connection = new SqlConnection(@"Data Source=.'SQLEXPRESS;AttachDbFilename=|DataDirectory|'Database.mdf;Integrated Security=True;User Instance=True");
             SqlCommand cmd;
             cmd = new SqlCommand("INSERT INTO Users (UserID,FName, LName, PhoneNo, Address, Email, UserName, Password, Points, Role) VALUES (@UserID,@FName, @LName, @PhoneNo, @Address, @Email, @UserName, @Password, @Points, @Role)");
             try
             {
                 cmd.Connection = connection;
                 cmd.Parameters.AddWithValue("@UserID", UserID);
                 cmd.Parameters.AddWithValue("@FName", tbFName.Text);
                 cmd.Parameters.AddWithValue("@LName", tbLName.Text);
                 cmd.Parameters.AddWithValue("@PhoneNo", tbPhoneNo.Text);
                 cmd.Parameters.AddWithValue("@Address", tbAddress.Text);
                 cmd.Parameters.AddWithValue("@Email", tbEmail.Text);
                 cmd.Parameters.AddWithValue("@UserName", tbUserName.Text);
                 cmd.Parameters.AddWithValue("@Password", tbPassword.Text);
                 cmd.Parameters.AddWithValue("@Points", Points);
                 cmd.Parameters.AddWithValue("@Role", Role);
                 connection.Open();
                 cmd.ExecuteNonQuery();
             }
             finally
             {
                 connection.Close();
                 //session
                 Session["UserName"] = tbUserName.Text;
                 Session["UserID"] = ("SELECT * FROM Users WHERE UserID = 'UserID'");
                 Session["Points"] = ("SELECT * FROM Users WHERE Points = 'Points'");
                 //pop out then redirect 
                 ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Thank you or signing up with us!');window.location='Home.aspx';</script>'");
            }         
      }

首先声明连接的正确方式应该是什么，因为我试着把它放在前面，但我的cmd有问题。

         SqlConnection connection = new SqlConnection(@"Data Source=.'SQLEXPRESS;AttachDbFilename=|DataDirectory|'Database.mdf;Integrated Security=True;User Instance=True");
         SqlCommand cmd;
         bool exists = false;
         using (SqlCommand cmd = new SqlCommand("select * from [Users] where UserName = @UserName"))
         {
             cmd.Parameters.AddWithValue("UserName", tbUserName.Text);
             exists = (int)cmd.ExecuteScalar() > 0;
         }

检查用户名是否存在于SQLExpress中

您需要将连接分配给上一个示例中的命令

因此，在您的使用声明中添加：

 cmd.Connection = connection;

此外，您不需要：

SqlCommand cmd;

因为该命令是在该CCD_ 1语句的上下文中创建的。

将Connection上下文封装在using语句中也被认为是一种很好的做法，以确保正确地处理/关闭实际连接。

试试这个，

using (SqlConnection connection = new SqlConnection(@"Data Source=.'SQLEXPRESS;AttachDbFilename=|DataDirectory|'Database.mdf;Integrated Security=True;User Instance=True"))
{
    connection .Open();
    SqlCommand cmd = new SqlCommand("select * from [Users] where UserName = @UserName", connection );
    cmd.Parameters.AddWithValue("UserName", tbUserName.Text);
    bool exists = (int)cmd.ExecuteScalar() > 0;
}

您没有指定connection到command。其次，你必须打开连接。

更新：完整代码

try
{
    string connectionString = @"Data Source=.'SQLEXPRESS;AttachDbFilename=|DataDirectory|'Database.mdf;Integrated Security=True;User Instance=True";
    bool exists = false;
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection .Open();
        SqlCommand cmd = new SqlCommand("select * from [Users] where UserName = @UserName", connection );
        cmd.Parameters.AddWithValue("UserName", tbUserName.Text);
        exists = (int)cmd.ExecuteScalar() > 0;
    }
    if (exists)
    {
        lblUserName.Text = "This username has been used by another user.";
    }
    else
    {
        using(SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            SqlCommand cmd = new SqlCommand("INSERT INTO Users (UserID,FName, LName, PhoneNo, Address, Email, UserName, Password, Points, Role) VALUES (@UserID,@FName, @LName, @PhoneNo, @Address, @Email, @UserName, @Password, @Points, @Role)", Connection);
            cmd.Parameters.AddWithValue("@UserID", UserID);
            cmd.Parameters.AddWithValue("@FName", tbFName.Text);
            cmd.Parameters.AddWithValue("@LName", tbLName.Text);
            cmd.Parameters.AddWithValue("@PhoneNo", tbPhoneNo.Text);
            cmd.Parameters.AddWithValue("@Address", tbAddress.Text);
            cmd.Parameters.AddWithValue("@Email", tbEmail.Text);
            cmd.Parameters.AddWithValue("@UserName", tbUserName.Text);
            cmd.Parameters.AddWithValue("@Password", tbPassword.Text);
            cmd.Parameters.AddWithValue("@Points", Points);
            cmd.Parameters.AddWithValue("@Role", Role);
            cmd.ExecuteNonQuery();
        }
    }
}
catch(Exception ex)
{
    //Do something
}
finally
{
    //session
    Session["UserName"] = tbUserName.Text;
    Session["UserID"] = ("SELECT * FROM Users WHERE UserID = 'UserID'");
    Session["Points"] = ("SELECT * FROM Users WHERE Points = 'Points'");
    //pop out then redirect 
    ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Thank you or signing up with us!');window.location='Home.aspx';</script>'");
}

SQL命令应该有一个重载，它接受一个启动的连接

使用SqlCommand构造函数，该构造函数将连接作为第二个参数。接下来你必须在执行命令之前打开它，然后关闭它

using (SqlCommand cmd = new SqlCommand("select * from [Users] where UserName = @UserName", connection))
{
   cmd.Parameters.AddWithValue("UserName", tbUserName.Text);
   connection.Open();
   exists = (int)cmd.ExecuteScalar() > 0;
   connection.Close();
}
相关文章：