将char数据类型转换为日期时间数据类型会导致超出范围的datetime值。 SQLEXCEPTION

I am a beginner in asp.net. I am using a query :

我是asp.net的初学者。我正在使用查询:

 string num = ("SELECT count(*) from booking WHERE date='" + dt + "' AND start_time='" + stime + "' AND end_time='" + etime + "' AND lid='" + hostloc + "'");

SqlCommand cmd = new SqlCommand(num, con);

con.Open();

int count = (int)cmd.ExecuteScalar();

con.Close();

Sometimes when i submit my web form it gives me an SqlException :

有时,当我提交我的Web表单时,它会给我一个SqlException:

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

“将char数据类型转换为日期时间数据类型会导致日期时间值超出范围。”

This does not happen always!! Please, any help is appreciated.. Thank You in advance

这不会永远发生!!请,任何帮助表示赞赏..谢谢你提前

2 个解决方案

#1

string num = "SELECT count(*) from booking WHERE date=@dt AND start_time=@stime AND end_time=@etime AND lid=@hostloc";

using(SqlCommand cmd = new SqlCommand(num, con))
{
    cmd.Parameters.AddWithValue("dt", dt);
    // etc for all params
    con.Open();

    int count = (int)cmd.ExecuteScalar();
}

Note this assumes dt is a DateTime etc.

请注意,这假设dt是DateTime等。

This solves multiple problems, including formatting, localisation and SQL injection.

这解决了多个问题,包括格式化,本地化和SQL注入。

#2

First of all use parameterized queries for your SQL calls or you might get SQL injected soon enough.

首先,为SQL调用使用参数化查询,否则可能很快就会注入SQL。

Also you need to convert dt to datetime before you send the variable to the SQL Server in order to avoid such errors, like Convert.ToDatetime(dt).

此外,您需要在将变量发送到SQL Server之前将dt转换为datetime,以避免此类错误,例如Convert.ToDatetime(dt)。

#1

string num = "SELECT count(*) from booking WHERE date=@dt AND start_time=@stime AND end_time=@etime AND lid=@hostloc";

using(SqlCommand cmd = new SqlCommand(num, con))
{
    cmd.Parameters.AddWithValue("dt", dt);
    // etc for all params
    con.Open();

    int count = (int)cmd.ExecuteScalar();
}