SQL DateTime范围搜索 [英] SQL DateTime range search

问题描述

运行SQL以在月份开始和结束之间搜索一系列记录时出错。

SqlDataSource1是为网页上的GridView定义的。

--- -------------------------------------------------- ----------------------

Getting error when running SQL to search for a range of records between month start and end.
SqlDataSource1 is defined for a GridView on web page.
---------------------------------------------------------------------------

protected void Page_Load(object sender, EventArgs e)
  {
      string sqltmp;
      if (!IsPostBack)
      {
          DateTime now = DateTime.Now;
          DateTime firstDate = new DateTime(now.Year, now.Month, 1);
          monthNumber = Now.Month;
          yearNumber = Now.Year;
          if (monthNumber == 12)
          {
                monthNumber = 1;
                yearNumber = yearNumber + 1;
          }
          else
          {
                monthNumber++;
          }
          DateTime lastDate = new DateTime(yearNumber, monthNumber, 1);

          sqltmp = "SELECT TOP 10 [Trans_Date], [CaseLast] ";
          sqltmp = sqltmp + "FROM Transactions ";
          sqltmp = sqltmp + "WHERE ";
          sqltmp = sqltmp + "( [Trans_Date] >= " + firstDate + " AND [Trans_Date] < " + lastDate + ")";

          SqlDataSource1.SelectCommand = sqltmp;
      }
  }

--------------------- -------------------------------------------------- --------------

内置SQL如下所示：

-------------------------------------------------------------------------------------
Built SQL looks like this:

"SELECT TOP 10 [Trans_Date], [CaseLast] FROM Transactions WHERE ( [Trans_Date] >= 5/1/2014 12:00:00 AM AND [Trans_Date] < 6/1/2014 12:00:00 AM)"

------------------------------------------ --------------------------------------------

错误：'12'附近的语法不正确。

描述：执行当前Web请求期间发生了未处理的异常。请查看堆栈跟踪以获取有关错误及其源自代码的位置的更多信息。



异常详细信息：System.Data.SqlClient.SqlException：'12'附近的语法不正确。

- -------------------------------------------------- ------------------------------------

我忘了什么？

--------------------------------------------------------------------------------------
Error: Incorrect syntax near '12'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '12'.
---------------------------------------------------------------------------------------
What did I forget?

推荐答案

RyanDev的解决方案1很好，但需要一些额外的信息。



首先：由于 SQL Injection <，你不应该在代码隐藏中直接使用查询/ a> [ ^ ]

如何：保护ASP.NET中的SQL注入 [ ^ ]

停止SQL注射攻击在他们阻止你之前 [ ^ ]

SQL注入及其如何避免 [ ^ ]



其次，使用存储过程（SP） [ ^ ]。

演练：使用GridView Web服务器中的存储过程显示数据控制 [ ^ ]

如何在ASP.NET中调用SQL Server存储过程使用Visual Basic .NET [ ^ ]

如何：执行返回行的存储过程 [< a href =http://msdn.microsoft.com/en-us/library/d7125bke.aspxtarget =_ blanktitle =新窗口> ^ ]



最后，使用 BETWEEN [ ^ ]声明：

Solution 1 by RyanDev is good, but it needs some extra information.

First of all: you shoudn't use query direct in code-behind because of SQL Injection[^]
How To: Protect From SQL Injection in ASP.NET[^]
Stop SQL Injection Attacks Before They Stop You[^]
SQL Injection and how to avoid it[^]

Secondly, use Stored Procedure (SP)[^] instead.
Walkthrough: Displaying Data Using a Stored Procedure in the GridView Web Server Control[^]
How to call SQL Server stored procedures in ASP.NET by using Visual Basic .NET[^]
How to: Execute a Stored Procedure that Returns Rows[^]

Finally, use BETWEEN[^] statement:

CREATE PROCEDURE GetSampleData
    @datefrom DATETIME,
    @dateto DATETIME
AS
BEGIN
    SELECT <FieldList>
    FROM <TableName>
    WHERE <DateField> BETWEEN @datefrom AND @dateto

这是因为您的日期周围没有单引号。但是，这是编写代码的一种非常危险的方法。你需要使用参数。

It is because you do not have single quotes around your dates. However, this is a very dangerous way to write your code. You need to use parameters.

String sql = "SELECT .. FROM ... WHERE [Trans_Date] BETWEEN @firstDate AND @lastDate
...
cmd.Parameters.AddWithValue(@firstDate, firstDate);
cmd.Parameters.AddWithValue(@lastDate, lastDate);
...

这篇关于SQL DateTime范围搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！