Must declare the scalar variable @Username

Question

User1234927972 posted

I keep getting the error message System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@Username".' - I have a Gridview that captures what the user inputs into texts boxes and submits them to the database on a click of a button. When the user clicks the button I am also trying to store the current user logged in, into the databse. Can someone help me as to why this message keeps occurring? 

Here is the C# code for my Button click event 

protected void btnSubmit_Click(object sender, EventArgs e)
            {
            string query = "insert into Stock_Take_Item(ItemID, BarQuantity, StorageQuantity) values(@ID, @BAR, @STORAGE); insert into Stock_Take(Username, StockDate)" +
                " values(@Username, GetDate())";
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(query, con);
            con.Open();
            cmd.Parameters.AddWithValue("@Username", Session["username"].ToString());
            foreach (GridViewRow row in gvStockTake.Rows)
            {
                Label ID = row.FindControl("itemId") as Label;
                TextBox BAR = row.FindControl("txtBar") as TextBox;
                TextBox STORAGE = row.FindControl("txtStorage") as TextBox;
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = ID.Text;
                cmd.Parameters.Add("@BAR", SqlDbType.Int).Value = BAR.Text;
                cmd.Parameters.Add("@STORAGE", SqlDbType.Int).Value = STORAGE.Text;
                cmd.ExecuteNonQuery(); //query execution
            }
            con.Close();
            Response.Write("Successfully inserted stock take items.");
        }

And here is the code from my log in c# page 

protected void btnLogin_Click(object sender, EventArgs e)
        {
            using (SqlConnection sqlCon = new SqlConnection(@"Data Source=(local)\;Initial Catalog=SmallBatch;Integrated Security=True;"))
            {
                sqlCon.Open();
                string query = "SELECT COUNT(1) FROM Site WHERE Username=@username AND Password=@password";
                SqlCommand sqlCmd = new SqlCommand(query, sqlCon);
                sqlCmd.Parameters.AddWithValue("@username", txtUsername.Text.Trim());
                sqlCmd.Parameters.AddWithValue("@password", txtPassword.Text.Trim());
                int count = Convert.ToInt32(sqlCmd.ExecuteScalar());
                
                if (count == 1)
                {
                    Session["username"] = txtUsername.Text.Trim();
                    Response.Redirect("Dashboard.aspx");
                }

                else { lblErrorMessage.Visible = true; }

              
                        
            }
        }

I am still new to ASP.NET and C#, so any reccomendations are more than appreciated! 

Answer 1

User753101303 posted

Hi,

You are clearing the parameters collection inside the loop and never add back @username. The easiest fix is to have :

foreach (GridViewRow row in gvStockTake.Rows)
{
   Label ID = row.FindControl("itemId") as Label;
   TextBox BAR = row.FindControl("txtBar") as TextBox;
   TextBox STORAGE = row.FindControl("txtStorage") as TextBox;
   cmd.Parameters.Clear();
   // Moved from its earlier location
   cmd.Parameters.AddWithValue("@Username", Session["username"].ToString());

I noticed also you are suing your own authentication mechanism. You have a learning curve but you may in the coming weeks or motnhs to see what ASP.NET offers and reuse that...

Answer 2

User1234927972 posted

Hey, Thanks for your reply. 

I've changed the code as to what you have suggested, and now have the error : 

System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@ID".'

Any idea why this is happened now? 

Answer 3

User-1716253493 posted

Must declare the scalar variable

usualy it's because

• you forget to declare
• the value is null, Session["username"] is null
• the control is null, wrong referent, ensure "itemId", "txtBar", "txtStorage" are correct

You can avoid the error message by checking above value or control before next code

i.e

if(Session["username"]!=null) ...

if(id!=null)