C# Get data between date and hour from MySQL table

Question

I'm trying to print a table from my database but I want to filter it using this block code, what I want to do is print the data between two hours but I don't know is the input format of the hour is correct, so here's the code:

string horaI=null;
string horaF=null;
string[] hr1 = null;
string[] hr2 = null;

// on load....
dateTimePicker1.CustomFormat = "HH:mm tt"; // Only use hours and minutes
horaI = dateTimePicker1.Value.ToString("HH:mm tt");
hr1 = horaI.Split();

string connectionstring = null;
string sql = null;
string data = null;
        
connectionstring = "server=127.0.0.1; database=gimnasio5; uid=root; pwd=0000000000;";

sql = "SELECT IdMembresia, Nombre, Apellido, Tipo, Fecha_Inicio,
       Fecha_Vencimiento, Inscripcion, Total,Impreso_Corte FROM membresia where
       Impreso_Corte='No impreso' or (Fecha_Membresia between @d1 and @d2 and
       Hora_Membresia between @d3 and @d4) order by gimnasio5.membresia.IdMembresia;";

var dtable = new DataTable("membresia");
var conn = new MySql.Data.MySqlClient.MySqlConnection(connectionstring);
var cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn);
var dscmd = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);

using (conn)
{
  var param = new MySql.Data.MySqlClient.MySqlParameter("@d1", MySql.Data.MySqlClient.MySqlDbType.Date);
  param.Direction = ParameterDirection.Input;
  param.Value = DateTime.Today;
  cmd.Parameters.Add(param);
  param = new MySql.Data.MySqlClient.MySqlParameter("@d2", MySql.Data.MySqlClient.MySqlDbType.Date);
  param.Direction = ParameterDirection.Input;
  param.Value = DateTime.Today;
  cmd.Parameters.Add(param);

  //The error can be here because when I use it with dates only it works fine
  //but when I add this part of code, fails. 

  param = new MySql.Data.MySqlClient.MySqlParameter("@d3", MySql.Data.MySqlClient.MySqlDbType.Time);
  param.Direction = ParameterDirection.Input;
  param.Value = hr1[0];
  //Convert.ToDateTime(hr1[0]).ToString("HH:mm");
  cmd.Parameters.Add(param);
  param = new MySql.Data.MySqlClient.MySqlParameter("@d4", MySql.Data.MySqlClient.MySqlDbType.Time);
  param.Direction = ParameterDirection.Input;
  param.Value = hr2[0];
  //Convert.ToDateTime(hr2[0]).ToString("HH:mm");
  cmd.Parameters.Add(param);

  conn.Open();
  dscmd.Fill(dtable);
}

But I'm getting and error:

An exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll but was not handled in user code Additional information: Fatal error encountered during command execution.

I get the error when I try to fill the MySqlAdapter Object:

dscmd.Fill(dtable);

I thought it was the format I input the time, but as you can see in the code I use to forms for it, but neither of both works, and returns the same error code. My column in the MySQL database is set to save time type, so the problem isn't in the table.

The hour in the database is saved like this, the column is time type:

12:03:00

21:34:00

Table structure

CREATE TABLE `membresia` (
  `IdMembresia` int(11) NOT NULL AUTO_INCREMENT,
  `Nombre` varchar(100) NOT NULL,
  `Apellido` varchar(100) NOT NULL,
  `Tipo` varchar(100) NOT NULL,
  `Fecha_Inicio` date NOT NULL,
  `Fecha_Vencimiento` date NOT NULL,
  `Inscripcion` varchar(20) DEFAULT NULL,
  `Estado_membresia` varchar(15) NOT NULL,
  `Fecha_modificacion` date NOT NULL,
  `Total` decimal(10,2) NOT NULL,
  `Nota` varchar(200) DEFAULT NULL,
  `Fecha_Membresia` date NOT NULL,
  `Impreso_Corte` varchar(20) NOT NULL,
  `IdSocio` int(11) DEFAULT NULL,
  `Hora_Membresia` time NOT NULL,
  PRIMARY KEY (`IdMembresia`),
  KEY `L_Id2` (`IdSocio`),
  KEY `F_Nombre` (`Nombre`),
  KEY `F_Apelli` (`Apellido`),
  CONSTRAINT `F_Apelli` FOREIGN KEY (`Apellido`) REFERENCES `socios` (`Apellido`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `F_Nombre` FOREIGN KEY (`Nombre`) REFERENCES `socios` (`Nombre`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `L_Id2` FOREIGN KEY (`IdSocio`) REFERENCES `socios` (`IdSocio`) ON DELETE CASCADE ON UPDATE CASCADE)
  ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

Code like this way:

        SqlConnection conn = new SqlConnection("server=127.0.0.1; database=gimnasio5; uid=root; pwd=0000000000;");

        conn.Open();

        string query = string.Format(@"SELECT IdMembresia, Nombre, Apellido, Tipo, Fecha_Inicio,
                       Fecha_Vencimiento, Inscripcion, Total, Impreso_Corte FROM membresia where
                       Impreso_Corte = 'No impreso' or(Fecha_Membresia between '{0}' and '{1}' and
                       Hora_Membresia between '{2}' and '{3}') order by gimnasio5.membresia.IdMembresia", dateTimePicker1.Value.ToShortDateString(), dateTimePicker2.Value.ToShortDateString(), dateTimePicker3.Value.ToString("hh:mm:ss"), dateTimePicker4.Value.ToString("hh:mm:ss"));

        SqlCommand cmd = new SqlCommand(query, conn);

        DataTable dt = new DataTable();
        dt.Load(cmd.ExecuteReader());
        conn.Close();
        return dt;