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 <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="83bec3cfece4eaed">[email protected]</a> and <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="b5e0c6d0c7e1ccc5d088f5e0c6d0c7e1ccc5d0">[email protected]</a> and <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="edbd8c9e9e9a829f89d0adbd8c9e9e9a829f89">[email protected]</a> and U<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="27544255735e57421a6772544255735e5742">[email protected]</a>", 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>");
}
}
}
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
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 <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="ab96ebe7c4ccc2c5">[email protected]</a> and <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="3f6a4c5a4d6b464f5a027f6a4c5a4d6b464f5a">[email protected]</a> and <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="4e1e2f3d3d39213c2a730e1e2f3d3d39213c2a">[email protected]</a> and <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="91c4e2f4e3c5e8e1f4acd1c4e2f4e3c5e8e1f4">[email protected]</a>", 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 <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="eea281898780d3aea281898780">[email protected]</a>
and <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="4d183e283f19343d28700d183e283f19343d28">[email protected]</a>
and <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="2676475555514954421b667647555551495442">[email protected]</a>
and <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="88ddfbedfadcf1f8edb5c8ddfbedfadcf1f8ed">[email protected]</a>
Should probably be:
select * from dbo.UserInfo
where <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="5814373f3136651814373f3136">[email protected]</a>
and <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="0d5d6c7e7e7a627f69304d5d6c7e7e7a627f69">[email protected]</a>
and <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="95c0e6f0e7c1ece5f0a8d5c0e6f0e7c1ece5f0">[email protected]</a>
Method 2
You have passed in @Login and @Password as parameters to your query, but you have not passed in @UserType as a parameter to your query.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0