Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
menu search
person
Welcome To Ask or Share your Answers For Others

Categories

I have a problem with my login system. I made a simple system which worked well, but I wanted to make it more advanced and display a different start page depending on a user's UserType. However, I'm encountering the following error: "Must declare the scalar variable @UserType."

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class LoginwithEncryption : System.Web.UI.Page
{

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
        con.Open();
        SqlCommand cmd = new SqlCommand(
            "select * from dbo.UserInfo where Login =@Login and UserType=@UserType and Password=@Password and UserType=@UserType", con);
        cmd.Parameters.AddWithValue("@Login", txtUserName.Text);
        cmd.Parameters.AddWithValue("@Password", txtPWD.Text+".123");


        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);

        if (dt.Rows.Count > 0)
        {
            int usertype = Convert.ToInt32(dt.Rows[0]["UserType"]);


            if (usertype == 1)
            {
                Response.Redirect("StartPage.aspx");
            }
            else if (usertype == 2)
            {
                Response.Redirect("DiferentStartPage.aspx");
            }

        }
        else
        {
            ClientScript.RegisterStartupScript(Page.GetType(), "validation",
                "<script language='javascript'>alert('Invalid UserName and Password')</script>");
        }

    }
}
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
390 views
Welcome To Ask or Share your Answers For Others

1 Answer

As the error states: Must declare the scalar variable "@UserType"

In other words, the SQL command has a parameter for @UserType, but there are no parameters declared in the parameters collection.

SqlCommand cmd = new SqlCommand(
"select * from dbo.UserInfo where Login =@Login and UserType=@UserType and Password=@Password and UserType=@UserType", con);
cmd.Parameters.AddWithValue("@Login", txtUserName.Text);
cmd.Parameters.AddWithValue("@Password", txtPWD.Text+".123");

Add another parameter for the @UserType parameter:

cmd.Parameters.AddWithValue("@UserType", "User Type");

Also, the SQL contains a duplicate reference to for @UserType:

select * from dbo.UserInfo 
where Login=@Login 
      and UserType=@UserType 
      and Password=@Password 
      and UserType=@UserType

Should probably be:

select * from dbo.UserInfo 
where Login=@Login 
      and Password=@Password
      and UserType=@UserType 

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
...