Must declare the scalar variable "@Email"

Question

I am trying to pass values from a database table to a couple of labels where the email column matches the email entered.I have passed the email entered from login page to this page using a session.Like This :

    protected void btnLogin_Click(object sender, EventArgs e)
    {
    if (AuthenticateUser(txtEmail.Text, txtPassword.Text))
    {
        FormsAuthentication.RedirectFromLoginPage(txtEmail.Text, chkBoxRememberMe.Checked);

        Session["Email"] = txtEmail.Text;
        Response.Redirect("~/Account.aspx");
     }

then i am passing the session value to lblEmail on ACOUNTS page.and then i am trying to retrieve values 'Name' and 'Balance' from database tabel where Emails match the one in the table.Like This:

     protected void Page_Load(object sender, EventArgs e)
{
    lblEmail.Text = Session["Email"].ToString();
    string CS = ConfigurationManager.ConnectionStrings["ABCD"].ConnectionString;
    using (SqlConnection con = new SqlConnection(CS))
    {
        SqlCommand cmd = new SqlCommand("Select * from tblRegister where @Email = " + lblEmail.Text, con);
        con.Open();
        SqlDataReader rdr = cmd.ExecuteReader();

        rdr.Read();
        lblName.Text = rdr["Name"].ToString();
        lblBalance.Text = rdr["Balance"].ToString();


    }

But i get a error message stating 'must declare the scalar vraiable @Email' on the line below:

   SqlDataReader rdr = cmd.ExecuteReader();

What am i doing wrong?

Shouldn't you do something like `cmd.Parameters.AddWithValue("@email", lblEmail.Text);` before executing the query? — Niki, Apr 07 '14 at 16:55
And I think you meant something like `where Email = @Email`, where "email" is DB field, and "@email" is parameter u pass in — Yuriy Galanter, Apr 07 '14 at 16:56
Wait a second, I just realized you're building your query by concattenating strings. Don't! That's a huge security leak. — Niki, Apr 07 '14 at 16:56
The problem is exactly what the error says. You need to create the `@email` sql variable before you try to set it. — crthompson, Apr 07 '14 at 16:56
And if you're wandering why this is a big deal, Google "SQL injection". A malicious 12-year old could probably read or modify or delete your entire database with that. — Niki, Apr 07 '14 at 16:59

score 2 · Accepted Answer · answered Apr 07 '14 at 16:57

2

Try like this instead

  SqlCommand cmd = new SqlCommand("Select * from tblRegister where Email = @Email", con);
  cmd.Parameters.AddWithValue("@Email", lblEmail.Text);
  SqlDataReader rdr = cmd.ExecuteReader();
  //...

answered Apr 07 '14 at 16:57

meda

45,103
14
92
122

You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... – marc_s Jan 21 '18 at 08:01

score 1 · Answer 2 · edited May 23 '17 at 10:27

@Email indicates a variable, not a field name. If the field name in your table is @Email enclose it in square brackets... [@Email] but I would guess that the field name is Email.

Edit:

You also have an issue with the where statement, if you are sending text you need to enclose that in quotes:

SqlCommand cmd = new SqlCommand("Select * from tblRegister where Email = '" + lblEmail.Text + "'", con);

This type of query, simply passing a value entered by the user will open you up to SQL injection.

More information

score 1 · Answer 3 · answered Apr 07 '14 at 16:56

1

You are using the Email param in the wrong place.

Try using this instead:

SqlCommand cmd = new SqlCommand("Select * from tblRegister where Email = @Email", con);
cmd.Parameters.AddWithValue("@Email", lblEmail.Text);

answered Apr 07 '14 at 16:56

Siyual

16,415
8
44
58

score 1 · Answer 4 · edited May 23 '17 at 12:03

Other answers have already answered your initial problem regarding @Email.

Here is what I found

Since you are using FormsAuthentication, you should not save Email in Session state. (Normally, FormsAuthentication uses username, but email address is ok since you are retrieving user information by email address.)

It defeats the purpose of using FormsAuthentication, because it already saves the Email in FormsAuthenticationTicket.

protected void btnLogin_Click(object sender, EventArgs e)
{
   if (AuthenticateUser(txtEmail.Text, txtPassword.Text))
   {
      // Thus all you need
      FormsAuthentication.RedirectFromLoginPage(username, true);
   }
}

Global.asax.cs

You need this in order to retrieve the Email from cookie, and save the email in IPrincipal Object.

public class Global : HttpApplication
{
    private void Application_AuthenticateRequest(object sender, EventArgs e)
    {
        HttpCookie decryptedCookie =
            Context.Request.Cookies[FormsAuthentication.FormsCookieName];

        FormsAuthenticationTicket ticket =
            FormsAuthentication.Decrypt(decryptedCookie.Value);

        var identity = new GenericIdentity(ticket.Name);
        var principal = new GenericPrincipal(identity, null);

        HttpContext.Current.User = principal;
        Thread.CurrentPrincipal = HttpContext.Current.User;
    }
}

Usage

protected void Page_Load(object sender, EventArgs e)
{
    var email = User.Identity.Name;
}

More information here.

Must declare the scalar variable "@Email"

4 Answers4

Here is what I found

Global.asax.cs

Usage

Linked