dcsimg
Results 1 to 5 of 5

Thread: Must declare the scalar variable error

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    4,880

    Must declare the scalar variable error

    I'm getting the "Must declare the scalar variable..." error in my C# program. The code is below. Not sure why it's happening in the highlighted line of code;

    Code:
            private void ShowSelectedAnimalInTextBox()
            {
                try
                {
                    if (OpenDatabaseConnection(conn))
                    {
                        string sql = "SELECT Name FROM Animal WHERE Id = @Id";
    
                        cmd = new SqlCommand(sql, conn);
                        cmd.Parameters.AddWithValue("@Id", lbAllAnimals.SelectedValue);
                        cmd.ExecuteScalar();
                        adp = new SqlDataAdapter(sql, conn);
    
                        using (adp)
                        {
                            DataTable dt = new DataTable();
                            adp.Fill(dt);  ===========> Exception is thrown on this line 
                            txtZooOrAnimal.Text = dt.Rows[0]["Name"].ToString();
                        }
                    }
                }
    
                catch (Exception e)
                {
                    MessageBox.Show("ShowSelectedAnimalInTextBox() - " + e.Message);
                }
            }
    Blake

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,733

    Re: Must declare the scalar variable error

    Your code doesn't make much sense. You have a SQL query that retrieves a single value, so it is definitely a candidate for ExecuteScalar, but when you call that method you don't actually keep the value it returns. You then try to execute the very same query again using a data adapter and then get the single value from a DataTable, which is extremely inefficient.

    The specific reason for your issue is that you are not adding a parameter to the SelectCommand of your data adapter. You're adding one to the command referred to by 'cmd' but that's not the same command executed by the data adapter. To make that code work, you could do this:
    Code:
            private void ShowSelectedAnimalInTextBox()
            {
                try
                {
                    if (OpenDatabaseConnection(conn))
                    {
                        string sql = "SELECT Name FROM Animal WHERE Id = @Id";
    
                        cmd = new SqlCommand(sql, conn);
                        cmd.Parameters.AddWithValue("@Id", lbAllAnimals.SelectedValue);
                        cmd.ExecuteScalar();
                        adp = new SqlDataAdapter(sql, conn);
    
                        using (adp)
                        {
                            adp.SelectCommand.Parameters.AddWithValue("@Id", lbAllAnimals.SelectedValue);
                            DataTable dt = new DataTable();
                            adp.Fill(dt);  ===========> Exception is thrown on this line 
                            txtZooOrAnimal.Text = dt.Rows[0]["Name"].ToString();
                        }
                    }
                }
    
                catch (Exception e)
                {
                    MessageBox.Show("ShowSelectedAnimalInTextBox() - " + e.Message);
                }
            }
    That would be a silly thing to do though. The proper solution is to get rid of the data adapter altogether and use the result of ExecuteScalar:
    Code:
            private void ShowSelectedAnimalInTextBox()
            {
                try
                {
                    if (OpenDatabaseConnection(conn))
                    {
                        string sql = "SELECT Name FROM Animal WHERE Id = @Id";
    
                        cmd = new SqlCommand(sql, conn);
                        cmd.Parameters.AddWithValue("@Id", lbAllAnimals.SelectedValue);
                        txtZooOrAnimal.Text = cmd.ExecuteScalar() as string;
                    }
                }
    
                catch (Exception e)
                {
                    MessageBox.Show("ShowSelectedAnimalInTextBox() - " + e.Message);
                }
            }

  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    4,880

    Re: Must declare the scalar variable error

    Thanks JMC,

    Your solution worked. I got screwed up from the video I was watching on this functionality.
    Blake

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,733

    Re: Must declare the scalar variable error

    Quote Originally Posted by blakemckenna View Post
    I got screwed up from the video I was watching on this functionality.
    Was it about cats being cute?

  5. #5

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    4,880

    Re: Must declare the scalar variable error

    How did you know I was a cat lover? LOL
    Blake

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width