Juan M Juan M - 4 months ago 35
SQL Question

MySql Data Reader not retrieving rows

I've come across some strange behaviour when using a MySql DataReader. The query evaluates correctly in Mysql Explorer (that is to say, it returns the expected records). However, when debugging the application, the error reads as:

Enumeration yielded no results

Disclaimer: I am aware of the following questions asked on the site

MySQL Data Reader not entering the loop

mysql datareader not finding rows c#

mysql datareader not finding rows c#

But I'm closing the reader, not referring to it multiple times, opening and closing the connection properly, and using Mysql instead of SQL Server, so there you go.

And now, here is my C# code:

MySqlConnection conn = this.conectar(); // this method performs Conn.Open();
List<Clase> listaClases = new List<Clase>();


string query = "SELECT c.id_clase, c.descripcion, e.nombre, e.calle, e.numero, e.telefono, e.email, "
+ "e.id_localidad, l.d_localidad, p.d_provincia, c.id_profesor, u.apellido, u.nombre, u.email "
+ "FROM CLASE c "
+ "JOIN USUARIO u ON c.id_profesor = u.id_usuario "
+ "JOIN CLASE_ESTABLECIMIENTO ce ON c.id_clase = ce.id_clase "
+ "JOIN ESTABLECIMIENTO e ON ce.id_establecimiento = e.id_establecimiento "
+ "JOIN LOCALIDAD l ON e.id_localidad = l.id_localidad "
+ "JOIN PROVINCIA p ON l.id_provincia = p.id_provincia "
+ "WHERE e.id_localidad = ?idLocalidad AND c.id_clase = ?idClase ";

MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.Add("?idLocalidad", MySqlDbType.Int32).Value = b.idLocalidad;
cmd.Parameters.Add("?idClase", MySqlDbType.Int32).Value = c.idClase;
MySqlDataReader dr = cmd.ExecuteReader();


while (dr.Read())
{
var idClase = dr.GetInt32(0);
var description = dr.GetString(1);
var nombreEst = dr.GetString(2);
var calleEst = dr.GetString(3);
var altura = dr.GetInt32(4);
var domExtra = dr.GetString(5);
var telefono = dr.GetString(6);
var emailEst = dr.GetString(7);
var idLoc = dr.GetInt32(8);
var descLoc = dr.GetString(9);
var prov = dr.GetString(10);
var idProf = dr.GetInt32(11);
var ape = dr.GetString(12);
var nom = dr.GetString(13);
var emailProf = dr.GetString(14);

listaClases.Add(/* construct class, code does not reach this point */);
}
dr.Close(); //close data reader
conn.Close(); //dispose of connection


Suggestions are greatly appreciated, thanks!

Answer

After taking a closer look, seems like your missing a column, you're assigning 0-14 when only 0-13 exist in your select statement.

SELECT 
0 c.id_clase,
1 c.descripcion,
2 e.nombre,
3 e.calle,
4 e.numero,
5 e.telefono,
6 e.email,
7 e.id_localidad,
8 l.d_localidad,
9 p.d_provincia,
10 c.id_profesor,
11 u.apellido,
12 u.nombre,
13 u.email