must declare the scalar variable @pp

Question

I try to get the quantity with a SQL query but it is not working;

     string selectrow;
        int rowindex = dataGridView1.CurrentCell.RowIndex;
        int columnindex = dataGridView1.CurrentCell.ColumnIndex;
        selectrow = dataGridView1.Rows[rowindex].Cells[columnindex].Value.ToString();
        string query = @"select quantite_pdt from produits where ref_pdt = @pp";
        try
        {
            using (var conn = loaddatabaseconnexion.connexion_BDD())
            using (var cmd = new SqlCommand(query, conn))
            {
                cmd.Parameters.AddWithValue("@pp", int.Parse(selectrow));
                SqlCommand command = new SqlCommand(query, loaddatabaseconnexion.connexion_BDD());
                string nbstr = command.ExecuteScalar().ToString();
            }
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }

I don't understand because my variable @pp is been declared.

You are setting `command` again in the inner `using` and not adding the parameter. — Dan Wilson, Jan 31 '19 at 16:08
Look at the line where you instantiate command. You should pass conn as the second parameter, not a new instance of your connection. Why do you need a second command? You should use cmd. — Sean Lange, Jan 31 '19 at 16:08
Are you getting an exception? Are you getting null? Is column in database for ref_pdt a string or integer? — jdweng, Jan 31 '19 at 16:23
I see. The conn is dispose outside the using so connection is null. But then the error message make absolutely no sense. The error message should indicate the command is not valid. — jdweng, Jan 31 '19 at 16:36
You've already demonstrated that you know [how to use parameters correctly in code](https://stackoverflow.com/questions/54409342/update-datagrid-with-dataset-c-sharp) so why do you use the very problematic approach of addwithvalue? — SMor, Jan 31 '19 at 18:09

score 4 · Accepted Answer · answered Jan 31 '19 at 16:18

You should be using a single command object here instead of two. Your code should look like this. I would suggest not using AddWithValue. It can get datatypes incorrect sometimes. http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/

using (var cmd = new SqlCommand(query, conn))
            {
                cmd.Parameters.AddWithValue("@pp", int.Parse(selectrow));
                string nbstr = cmd.ExecuteScalar().ToString();
            }

must declare the scalar variable @pp

1 Answers1