need SQLDataReader help

[Mercvapor]

Here the code I've been having problems with.

When my Reader[count] goes through the second loop which is Reader[1].
It says index out of bounds, but there is suppose to be 3 rows of data with a single column each.

anyone knows what's the problem?



public int getEvalRows(string UserID)
{
int rows = 0;
string evalID;
int count = 0;
SqlConnection dBCon = new SqlConnection(strConstr);
dBCon.Open();
string sqlCmd = "SELECT enrollmentID FROM [EES].[dbo].[EnrollmentStudent] Where userID = '"+UserID+"' and (doneLecture = 'False' or doneTutor = 'False')";
SqlCommand strCmd = new SqlCommand(sqlCmd, dBCon);
SqlDataReader reader = strCmd.ExecuteReader();
while (reader.Read())
{
sqlCmd = "SELECT [evaluationID] FROM [EES].[dbo].[Enrollment]where enrollmentID = '" + reader[count] + "'";
SqlConnection dBCon1 = new SqlConnection(strConstr);
dBCon1.Open();
SqlCommand strCmd1 = new SqlCommand(sqlCmd, dBCon1);
evalID = (string)strCmd1.ExecuteScalar();

sqlCmd = "SELECT evaluationID FROM [EES].[dbo].[Evaluation] where evaluationID = '" + evalID + "' and startDate < '2010-01-2' and endDate > '2010-01-2'";
SqlConnection dBCon2 = new SqlConnection(strConstr);
dBCon2.Open();
SqlCommand strCmd2 = new SqlCommand(sqlCmd, dBCon2);
SqlDataReader reader1 = strCmd2.ExecuteReader();
while (reader1.Read())
{
rows++;
}
count++;
}
return rows;
}

[Mercvapor]

*sorry about the code area, i couldn't edit so i posted a reply

Here the code I've been having problems with.

When my Reader[count] goes through the second loop which is Reader[1].
It says index out of bounds, but there is suppose to be 3 rows of data with a single column each.

anyone knows what's the problem?



public int getEvalRows(string UserID)
{
int rows = 0;
string evalID;
int count = 0;
SqlConnection dBCon = new SqlConnection(strConstr);
dBCon.Open();
string sqlCmd = "SELECT enrollmentID FROM [EES].[dbo].[EnrollmentStudent] Where userID = '"+UserID+"' and (doneLecture = 'False' or doneTutor = 'False')";
SqlCommand strCmd = new SqlCommand(sqlCmd, dBCon);
SqlDataReader reader = strCmd.ExecuteReader();
while (reader.Read())
{
sqlCmd = "SELECT [evaluationID] FROM [EES].[dbo].[Enrollment]where enrollmentID = '" + reader[count] + "'";
SqlConnection dBCon1 = new SqlConnection(strConstr);
dBCon1.Open();
SqlCommand strCmd1 = new SqlCommand(sqlCmd, dBCon1);
evalID = (string)strCmd1.ExecuteScalar();

sqlCmd = "SELECT evaluationID FROM [EES].[dbo].[Evaluation] where evaluationID = '" + evalID + "' and startDate < '2010-01-2' and endDate > '2010-01-2'";
SqlConnection dBCon2 = new SqlConnection(strConstr);
dBCon2.Open();
SqlCommand strCmd2 = new SqlCommand(sqlCmd, dBCon2);
SqlDataReader reader1 = strCmd2.ExecuteReader();
while (reader1.Read())
{
rows++;
}
count++;
}
return rows;
}

[dglienna]

What are you trying to get from the queries? You execute 3 queries without closing anything.

[Alsvha]

The obvious problem with lack of closing your readers (you'll get connection errors) and that you properly could structure your queries better - then you increment your "count" and use it as an item index.
So it expect your reader to have 1 item in the first iteration, 2 items in the second iteration and 3 items in your third iteration.

My guess is that you increment your "count" variable wrong or misuse it to something you did not intend to use it for.

So your use

sqlCmd = "SELECT [evaluationID] FROM [EES].[dbo].[Enrollment]where enrollmentID = '" + reader[count] + "'";

seems to conflict with your increment:

count++;


But, just FYI, if all you're interested in is your "rows" variable, then you should make one query to return the result to you instead of doing something like this.

[Mercvapor]

Ah ok, thanks for all your help. I managed to solve the problem.