locked
Must declare the scalar variable @Username RRS feed

  • 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! 

    Wednesday, April 11, 2018 5:44 PM

Answers

  • 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...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 11, 2018 6:05 PM

All replies

  • 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...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 11, 2018 6:05 PM
  • 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? 

    Wednesday, April 11, 2018 7:11 PM
  • 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)

    Thursday, April 12, 2018 12:55 AM