Must Declare Scalar Variable using sql string

Question

This is my first attempt at parameterized queries. I'm getting the error

Must Declare Scalar Variable @Email

Any help is appreciated!

string sql = "INSERT INTO Upload VALUES (@Email, @TimeStamp, @EmployeeId, @Name, @Title, @Department, @Race, @Gender, @AnnualizedBase, @AnnualizedTCC);";

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
using (SqlCommand command = new SqlCommand(sql, con))
{
   decimal num;
   var emailparam = new SqlParameter("@Email", SqlDbType.Text);
   emailparam.Value = System.Web.HttpContext.Current.User.Identity.GetUserId();

   // some more parameters following same format
   var baseparam = new SqlParameter("@AnnualizedBase", SqlDbType.Money);

   if (decimal.TryParse(result.Tables[0].Rows[i][6].ToString(), out num)) 
   {
       baseparam.Value = num;
   }
   else  
   {
       ViewBag.Error = "not money format";
   }

   var tccparam = new SqlParameter("@AnnualizedTCC", SqlDbType.Money);

   if (decimal.TryParse(result.Tables[0].Rows[i][7].ToString(), out num)) 
   {
       tccparam.Value = num;
   }
   else
   {
       ViewBag.Error = "not money format";
   }   

   con.Open();
   command.ExecuteNonQuery();
   con.Close();
}

You are also not doing anything to assure those values are mapped to the correct columns — Ňɏssa Pøngjǣrdenlarp, Jan 21 '18 at 02:08
https://stackoverflow.com/questions/22918508/must-declare-the-scalar-variable-email — Ankit Kumar, Jan 21 '18 at 02:09
Possible duplicate of [Must declare the scalar variable "@Email"](https://stackoverflow.com/questions/22918508/must-declare-the-scalar-variable-email) — ASh, Jan 21 '18 at 07:09
tip: tools like "Dapper" (on NuGet) make this kind of thing a breeze: `con.Execute(sql, new { AnnualizedBase = annBase, AnnualizedTCC = annTcc });` - *done*! also: I worry about any code that talks to the DB and the UI in the same method; probably best to separate these so that you do one or the other - i.e. create a DB-focused method that *only* takes the parsed inputs (`annBase`, `annTcc` etc), and doesn't even know about the UI (`ViewBag`, `HttpContext`, etc) — Marc Gravell, Jan 21 '18 at 09:56

score 1 · Accepted Answer · answered Jan 21 '18 at 08:02

You are defining your parameters - so far, so good.

But now, before you execute the query, you must .Add() those parameters to the SqlCommand object!

// Add parameters to SqlCommand
command.Parameters.Add(emailparam);
command.Parameters.Add(baseparam);
command.Parameters.Add(tccparam);

// *NOW* you can open connection, execute query, close connection
con.Open();
command.ExecuteNonQuery();
con.Close();

score 0 · Answer 2 · answered Jan 21 '18 at 06:30

0

You Should try this if you will not understand it ask me further

string mail="abc@hotmail.com"; 
string name="Abdul Aleem";
//This is In Lin Query
string sql = "INSERT INTO Upload VALUES (@Email, @Name)" //and so on
SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString)

SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.AddWithValue("@Email", mail);
cmd.Parameters.AddWithValue("@Name", name);
//And same as add other paramters according to your in line query
cmd.ExecuteNonQuery();

answered Jan 21 '18 at 06:30

Abdul Aleem

13
3

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:00
Well Its depend on your requirement i think thats good practice thats why i share with you in this you sure that which parameter accept what value – Abdul Aleem Jan 22 '18 at 21:40

Must Declare Scalar Variable using sql string

2 Answers2