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;

You don't need Using(conn) this method...just edit this query according to your application. You need to pass time from datetimepicker....tell me if you have probs. — bluetoothfx
– bluetoothfx, Commented Mar 31, 2016 at 19:30
You need 4 datetime picker because you want to check 1st on date range and second time range — bluetoothfx
– bluetoothfx, Commented Mar 31, 2016 at 19:40

bluetoothfx · Accepted Answer · 2016-03-31 19:42:38Z

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;

edited Mar 31, 2016 at 19:42

answered Mar 31, 2016 at 19:24

bluetoothfx

63310 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2461687 Over a year ago

it doesn't give me the error now but I'm not getting data

bluetoothfx Over a year ago

I think the input param value and db value not matching. So check input param and db value...

user2461687 Over a year ago

well I'll check it

user2461687 Over a year ago

I found the error is the string date format (hh:mm:ss) and I need in 24 hours format (HH:mm:ss)

bluetoothfx Over a year ago

Set you datetimepicker as like image (click on link). and to get time write like=>> string time = dateTimePicker1.Value.ToString("HH:mm:ss"); Click here to see DateTimePicker as TimePicker

Collectives™ on Stack Overflow

C# Get data between date and hour from MySQL table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related