我正在尝试从数据库中打印一张表，但是我想使用此阻止代码对其进行过滤，我要做的是在两个小时之间打印数据，但我不知道小时的输入格式是否正确，所以这是代码：
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);
}
但是我得到了并且出错：MySql.Data.dll中发生了'MySql.Data.MySqlClient.MySqlException'类型的异常，但未在用户代码中处理
附加信息：命令执行期间遇到致命错误。
我尝试填充MySqlAdapter对象时收到错误消息：
dscmd.Fill（dtable）;
我以为这是我输入时间的格式，但是正如您在用于表单的代码中所看到的那样，但这两种方法都不起作用，并且返回相同的错误代码。
我在MySQL数据库中的列设置为保存时间类型，因此问题不在表中。
像这样保存数据库中的小时，该列是时间类型：
12:03:00
21:34:00
先谢谢了。
表结构
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;
最佳答案
像这样的代码：
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;
关于c# - C#从MySQL表获取日期和小时之间的数据，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36341277/