Learn12 Learn12 - 7 months ago 12
SQL Question

Not sure how to pull the the value of a second select statement in sql command C#

I'm trying to pull a value from the second select statement in the SQL command that sums the total points possible and awarded from the first table. I was using the TOTALPOSSIBLE_SUM variable to try and pull the 5th element but it threw an exception.

Any ideas on how I would go about grabbing those 2 SUM values? Thanks

const int COL_NAME = 1;
const int COL_DUE_DATE = 2;
const int COL_POINTS_POSSIBLE = 3;
const int COL_POINTS_AWARDED = 4;
const int TOTALPOSSIBLE_SUM = 5;
const int TOTALAWARDED_SUM = 6;


// SQL statement to select the columns from the table
command.CommandText =
"SELECT * FROM " +
"[dbo].[Assignments_Table] ORDER BY [PkID] DESC;" +
"SELECT SUM(PointsPossible), SUM(PointsAwarded) " +
"FROM [dbo].[Assignments_Table]";

assignments_view.Items.Clear();

// SQL reader to read the data from the database into the list view
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{

Assignment newAssignment = new Assignment();
ListViewItem assignmentItem = new ListViewItem();
// ID
newAssignment.ID = reader.GetInt32(COL_PKID);
// Name
newAssignment.Name = reader.GetString(COL_NAME);
// Due Date
newAssignment.DueDate = reader.GetDateTime(COL_DUE_DATE);
// Possible Points
newAssignment.PointsPossible = reader.GetInt32(COL_POINTS_POSSIBLE);
// Awarded Points
newAssignment.PointsAwarded = reader.GetInt32(COL_POINTS_AWARDED);

newAssignment.TotalPointsPossible = reader.GetInt32(TOTALPOSSIBLE_SUM);
newAssignment.TotalPointsAwarded = reader.GetInt32(TOTALAWARDED_SUM);

Answer

When you read the results of a batch SQL statement, you can use the NextResult method to position the DataReader at the next result in the resultset.

while (reader.Read())
{
    //Write logic to process data for the first result.     
}


reader.NextResult(); // next resultset

while (reader.Read())
{
    //Write logic to process data for the second result.
}

NextResult returns true if there are more result sets; otherwise false, so I would suggesting using this.

if(reader.NextResult()) // next resultset
{
     while (reader.Read())
     {
          //Write logic to process data for the second result.
     }
}
Comments