Count the number of rows with sqldatareader

[Genbox]

As the subject says, how do i count the number of rows with sqldatareader ?

[J4amieC]

Return the @@ROWCOUNT from the query as an output parameter

[Genbox]

I new to C# and mssql. this is my code: SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); SqlDataReader rdr = null; conn.Open(); SqlCommand cmd = new SqlCommand("SELECT count(*) FROM training WHERE userid='genbox'", conn); rdr = cmd.ExecuteReader(); while (rdr.Read()) { Response.Write(#WANT COUNTER HERE#); } rdr.Close(); conn.Close(); I figured that something like SELECT count(*) FROM... could be used, but if @@ROWCOUNT is better, i will use that instead.

[J4amieC]

Oh, ok two ways - both exactly the same, but one is a tonne more readable. You are returning the count as the first column in a select list so you can just go: while (rdr.Read()) { Response.Write(rdr[0].ToString()); // rdr[0] refers to the first column in the resultset } However, you can make this much more readable by aliasing the column as a name SqlCommand cmd = new SqlCommand("SELECT count(*) AS TrainingCount FROM training WHERE userid='genbox'", conn); now: while (rdr.Read()) { Response.Write(rdr["TrainingCount"].ToString()); // no longer need to know which column number has the count - we refer by name } HTH! -- modified at 7:22 Friday 16th December, 2005

[Genbox]

Thanks, it sort of works. :S Cant i use the same SqlCommand to extract data from the tabel ? if i exec this: while (rdr.Read()) { Response.Write(rdr["TrainingCount"].ToString()); } then it returns the number of rows like it should, but if i read any data from the table, then it returns an error. - this is the code: while (rdr.Read()) { Response.Write(rdr["TrainingCount"].ToString()); Response.Write(rdr[1]); } And this is the error: System.IndexOutOfRangeException: Index was outside the bounds of the array. Do i really have to make 2 SqlCommands, one that counts and one that i can get data from? that dosent seem right to me.