How to use results from 2 SqlDataReader in an expression

Marius Vasile

I get results from a stored procedure that is working but I can't get to yValues. Error message The name 'acc1' does not exist in the current context, valid for all values. How it should be done?

using (SqlDataReader rdr = cmd.ExecuteReader())
{
    // Handle first result set (M1, M2, etc.)
    if (rdr.HasRows)
    {
        while (rdr.Read())
        {
            double acc1 = double.Parse(rdr["M1"].ToString());
            double acc2 = double.Parse(rdr["M2"].ToString());
            double acc3 = double.Parse(rdr["M3"].ToString());
            double acc4 = double.Parse(rdr["M4"].ToString());
            double acc5 = double.Parse(rdr["M5"].ToString());
            double acc6 = double.Parse(rdr["M6"].ToString());
            double acc7 = double.Parse(rdr["M7"].ToString());
            double acc8 = double.Parse(rdr["M8"].ToString());
            double acc9 = double.Parse(rdr["M9"].ToString());
            double acc10 = double.Parse(rdr["M10"].ToString());
            double acc11 = double.Parse(rdr["M11"].ToString());
            double acc12 = double.Parse(rdr["M12"].ToString());
        }
    }

    if (rdr.NextResult() && rdr.HasRows)
    {
        while (rdr.Read())
        {
            double val1 = double.Parse(rdr["V1"].ToString());
            double val2 = double.Parse(rdr["V2"].ToString());
            double val3 = double.Parse(rdr["V3"].ToString());
            double val4 = double.Parse(rdr["V4"].ToString());
            double val5 = double.Parse(rdr["V5"].ToString());
            double val6 = double.Parse(rdr["V6"].ToString());
            double val7 = double.Parse(rdr["V7"].ToString());
            double val8 = double.Parse(rdr["V8"].ToString());
            double val9 = double.Parse(rdr["V9"].ToString());
            double val10 = double.Parse(rdr["V10"].ToString());
            double val11 = double.Parse(rdr["V11"].ToString());
            double val12 = double.Parse(rdr["V12"].ToString());
        }
    }
}
double[] yValues =
{
    (acc1*200000)/val1,
    ((acc1+acc2)*200000)/(val1+val2),
    ((acc1+acc2+acc3)*200000)/(val1+val2+val3),
    ((acc1+acc2+acc3+acc4)*200000)/(val1+val2+val3+val4),
    ((acc1+acc2+acc3+acc4+acc5)*200000)/(val1+val2+val3+val4+val5),
    ((acc1+acc2+acc3+acc4+acc5+acc6)*200000)/(val1+val2+val3+val4+val5+val6),
    ((acc1+acc2+acc3+acc4+acc5+acc6+acc7)*200000)/(val1+val2+val3+val4+val5+val6+val7),
    ((acc1+acc2+acc3+acc4+acc5+acc6+acc7+acc8)*200000)/(val1+val2+val3+val4+val5+val6+val7+val8),
    ((acc1+acc2+acc3+acc4+acc5+acc6+acc7+acc8+acc9)*200000)/(val1+val2+val3+val4+val5+val6+val7+val8+val9),
    ((acc1+acc2+acc3+acc4+acc5+acc6+acc7+acc8+acc9+acc10)*200000)/(val1+val2+val3+val4+val5+val6+val7+val8+val9+val10),
    ((acc1+acc2+acc3+acc4+acc5+acc6+acc7+acc8+acc9+acc10+acc11)*200000)/(val1+val2+val3+val4+val5+val6+val7+val8+val9+val10+val11),
    ((acc1+acc2+acc3+acc4+acc5+acc6+acc7+acc8+acc9+acc10+acc11+acc12)*200000)/(val1+val2+val3+val4+val5+val6+val7+val8+val9+val10+val11+val12)
};

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vijay Pratap Singh1y ago

Accepted answer

The error occurs because acc1, val1, and similar variables are scoped within the while loops inside the SqlDataReader code blocks. Once the loops finish, these variables are no longer accessible in the outer scope where you're trying to use them to calculate yValues.

using (SqlDataReader rdr = cmd.ExecuteReader())
{
    // Create arrays to store acc and val values
    double[] acc = new double[12];
    double[] val = new double[12];

    // Handle first result set (acc1, acc2, ...)
    if (rdr.HasRows)
    {
        int index = 0;
        while (rdr.Read() && index < 12)  // Assuming there are 12 values
        {
            acc[index] = double.Parse(rdr["M" + (index + 1)].ToString());
            index++;
        }
    }

    // Handle second result set (val1, val2, ...)
    if (rdr.NextResult() && rdr.HasRows)
    {
        int index = 0;
        while (rdr.Read() && index < 12)  // Assuming there are 12 values
        {
            val[index] = double.Parse(rdr["V" + (index + 1)].ToString());
            index++;
        }
    }

    // Calculate yValues using the arrays
    double[] yValues = new double[12];
    for (int i = 0; i < 12; i++)
    {
        double accSum = 0;
        double valSum = 0;
        for (int j = 0; j <= i; j++)
        {
            accSum += acc[j];
            valSum += val[j];
        }
        yValues[i] = (accSum * 200000) / valSum;
    }
}

you can store the values from both result sets in arrays or lists, which you can then use in the outer scope for your calculations.

Hope this will help.

Marius Vasile1y ago

There is one issue, the

double[] yValues = new double[12];
    for (int i = 0; i < 12; i++)
    {
        double accSum = 0;
        double valSum = 0;
        for (int j = 0; j <= i; j++)
        {
            accSum += acc[j];
            valSum += val[j];
        }
        yValues[i] = (accSum * 200000) / valSum;
    }

has the same value, should be different

Marius Vasile1y ago
Really apreciated Vijay, that was a nicely solution.
+2