CODEWITHSUNDEEP
c#mysqlsql
CR9191
CR9191

Reputation: 51

Why cannot I add the 2nd result to my listbox by executing 2nd query with table? SQL

I came across this error because I want to print out all 5 different rating integers from the same movie name by counting each of those. The first query executes like flawless, but why cannot I get the 2nd query to work due to the error as stated below?

Error: {"Column 'RatingNum2' does not belong to table Table."}

   private void EachRating_Click(object sender, EventArgs e)
    {
        string filename, connectionInfo;
        SqlConnection db;

        this.listBox1.Items.Clear();

        filename = "netflix.mdf";

        connectionInfo = String.Format(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=
        |DataDirectory|\{0};Integrated Security=True;", filename);

        string moviename = this.textBox1.Text;
        moviename = moviename.Replace("'", "''");

        string five = "5";
        string four = "4";

        db = new SqlConnection(connectionInfo);
        db.Open();

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = db;

        cmd.CommandText = string.Format(
            @"
                SELECT MovieName, RatingNum1 FROM Movies
                INNER JOIN
                (
                SELECT MovieID, Count(Rating) as RatingNum1 FROM Reviews
                WHERE Rating = 5
                GROUP BY MovieID
                )TEMP
                ON TEMP.MovieID = Movies.MovieID
                WHERE MovieName = '{0}';", moviename);

        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        adapter.Fill(ds);

        DataTable dt = ds.Tables["TABLE"];

        foreach (DataRow row in dt.Rows)
        {
            string msg = string.Format("{0}: {1}",
            five.ToString(),
            row["RatingNum1"].ToString());

            this.listBox1.Items.Add(msg);
        }

        SqlCommand cmd2 = new SqlCommand();
        cmd2.Connection = db;

        cmd2.CommandText = string.Format(
            @"
              SELECT MovieName, RatingNum2 FROM Movies
              INNER JOIN
              (
              SELECT MovieID, Count(Rating) as RatingNum2 FROM Reviews
              WHERE Rating = 4
              GROUP BY MovieID
              )TEMP
              ON TEMP.MovieID = Movies.MovieID
              WHERE MovieName = '{0}'", moviename);

        SqlDataAdapter adapter2 = new SqlDataAdapter(cmd2);
        DataSet ds2 = new DataSet();
        adapter.Fill(ds2);

        DataTable dt2 = ds2.Tables["TABLE"];

        foreach (DataRow row2 in dt2.Rows)
        {
            string msg = string.Format("{0}: {1}",
            four.ToString(),
            row2["RatingNum2"].ToString()); //gets the error

            this.listBox1.Items.Add(msg);
        }

Upvotes: 0

Views: 45

Answers (1)

nobody
nobody

Reputation: 11090

You are filling the second dataset using the first adapter and hence the datatable is not able to find the column RatingNum2.

adapter.Fill(ds2);

should be

adapter2.Fill(ds2);

Upvotes: 1

Related Questions