Learn12 Learn12 - 2 years ago 69
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 =
"[dbo].[Assignments_Table] ORDER BY [PkID] DESC;" +
"SELECT SUM(PointsPossible), SUM(PointsAwarded) " +
"FROM [dbo].[Assignments_Table]";


// 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 Source

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.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download