[Solved] ASP.Net Error: Must declare the scalar variable

After learning how to authenticate user login, I am tried to make changes according to my design and it seems not to work.

I have these two tables (Users and UserActivation): Users table stores user data, while UserACtivation stores UserId(int) and Activation Code(unique identifier).

Users

UserActivation

When new user registers, data is inserted into both tables, and activation link is sent to user email. When user clicks on the email link, it redirects to activation page and deletes the data in the UserActivation code.

Then on the click of login, it should check if data provided exists in UserActivation table and if record exists, it should display error message “Account not activated” and if record don’t exist, it should go ahead and update Users Table columns (LastLogin and IsActive).

At first, when I clicked on the login, I got this error: “Account has not been activated”

Then I changed from this:

if (!string.IsNullOrEmpty(users))

To this:

if (string.IsNullOrEmpty(users))

Now I am getting this error:

Server Error in '/' Application.

Must declare the scalar variable"@dateandtime".

protected void ValidateUser(object sender, EventArgs e)
{
    if (!string.IsNullOrEmpty(textUser.Text) & !string.IsNullOrEmpty(txtPassword.Text))
    {
        using (SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\AuthenticationTestDatabase.mdf;Integrated Security = True"))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT Uid FROM Users WHERE email = @email AND pass = @pass", con))
            {
                con.Open();
                cmd.Parameters.AddWithValue("@email", textUser.Text.Trim());
                cmd.Parameters.AddWithValue("@pass", Encrypt(txtPassword.Text.Trim()));
                string Uid = Convert.ToString(cmd.ExecuteScalar());
                con.Close();
 
                if (!string.IsNullOrEmpty(Uid))
                {
                    string users = "";
                    using (SqlCommand cmd1 = new SqlCommand("SELECT Uid FROM UserActivation WHERE Uid = @Uid"))
                    {
                        cmd1.CommandType = CommandType.Text;
                        cmd1.Parameters.AddWithValue("@Uid", Uid);
                        cmd1.Connection = con;
                        con.Open();
                        users = Convert.ToString(cmd1.ExecuteScalar());
                        con.Close();
                    }
                    if (string.IsNullOrEmpty(users))
                    {
                        int user = 0;
                        using (SqlCommand cmd2 = new SqlCommand("SELECT Uid FROM Users WHERE pass = @pass COLLATE SQL_Latin1_General_CP1_CS_AS AND email = @email AND pass = @pass"))
                        {
                            cmd2.CommandType = CommandType.Text;
                            cmd2.Parameters.AddWithValue("@email", textUser.Text.Trim());
                            cmd2.Parameters.AddWithValue("@pass", Encrypt(txtPassword.Text.Trim()));
                            cmd2.Connection = con;
                            con.Open();
                            user = Convert.ToInt32(cmd2.ExecuteScalar());
                            con.Close();
                        }
                        if (user > 0)
                        {
                            Session["user"] = Uid;
                            con.Open();
                            string query = "SELECT LastLogin, IsActive from Users WHERE Uid = @Uid";
                            using (SqlCommand cmd3 = new SqlCommand(query, con))
                            {
                               cmd3.Parameters.AddWithValue("@Uid", Session["user"]);
                               Session["LastLogin"] = Convert.ToDateTime(cmd3.ExecuteScalar());
                            }
                            string UpdateLog = @"UPDATE Users SET LastLogin=@dateandtime, IsActive=@IsActive WHERE Uid = @Uid";
                            using (SqlCommand cmd4 = new SqlCommand(UpdateLog, con))
                             {
                                cmd.Parameters.AddWithValue("@dateandtime", DateTime.UtcNow);
                                cmd.Parameters.AddWithValue("@IsActive", "1");
                                cmd4.Parameters.AddWithValue("@Uid", Session["user"]);
                               cmd4.ExecuteNonQuery();
                             }
                            con.Close();
                        }
                        Session["user"] = Uid;
                        FormsAuthentication.RedirectFromLoginPage(Uid, true);
                    }
                    else
                    {
                        dvMessage.Visible = true;
                        lblMessage.Visible = true;
                        lblMessage.ForeColor = System.Drawing.Color.Red;
                        lblMessage.Text = "Account has not been activated";
                        txtPassword.Text = "";
                        txtPassword.Focus();
                    }
                }
                else
                {
                    dvMessage.Visible = true;
                    lblMessage.Visible = true;
                    lblMessage.ForeColor = System.Drawing.Color.Red;
                    lblMessage.Text = "Invalid Login Details";
                    txtPassword.Text = "";
                    txtPassword.Focus();
                }
            }
        }
    }
    else
    {
        dvMessage.Visible = true;
        lblMessage.Visible = true;
        lblMessage.ForeColor = System.Drawing.Color.Red;
        lblMessage.Text = "All Fields are Required";
    }
}