SqlDataReader fails with multiple resultsets
-
Hi, I have a multiple resultset returned by the stored procedure (4 select statements returning different values). When I call SqlCommand's ExecuteReader on the following piece of code:
cn = new System.Data.SqlClient.SqlConnection(ConnectionString); cn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = "spResSupervisorSwitchboard"; cmd.CommandType = CommandType.StoredProcedure; SqlDataReader rdr = cmd.ExecuteReader(); while(rdr.Read()) { lblWaitingFirstEntry.Text = rdr[0].ToString(); // System.InvalidOperationException } rdr.NextResult();The connection is opened fine, the stored procedure name is correct, stored procedure returns values when executed in SQL Query Analyzer... but it doesn't work in my code. :mad: Cheers -
Hi, I have a multiple resultset returned by the stored procedure (4 select statements returning different values). When I call SqlCommand's ExecuteReader on the following piece of code:
cn = new System.Data.SqlClient.SqlConnection(ConnectionString); cn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = "spResSupervisorSwitchboard"; cmd.CommandType = CommandType.StoredProcedure; SqlDataReader rdr = cmd.ExecuteReader(); while(rdr.Read()) { lblWaitingFirstEntry.Text = rdr[0].ToString(); // System.InvalidOperationException } rdr.NextResult();The connection is opened fine, the stored procedure name is correct, stored procedure returns values when executed in SQL Query Analyzer... but it doesn't work in my code. :mad: Cheersfrom what i see, code is not wrapped in a try catch block. you also don't close the connection, and rdr.NextResult is not needed since rdr.Read() enumerates through set in a DataReader. plus ur opening the connection before ur setting cmd fields. try this cn = new System.Data.SqlClient.SqlConnection(ConnectionString); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = "spResSupervisorSwitchboard"; cmd.CommandType = CommandType.StoredProcedure; try{ SqlDataReader rdr = cmd.ExecuteReader(); while(rdr.Read()) { lblWaitingFirstEntry.Text = rdr[0].ToString(); } }catch(SqlException ex) { lblWaitingFirstEntry.Text = ex.ToString(); } finally { con.close(); }