用日期搜索数据库 [英] Searching database with date

问题描述

在我的Windows窗体中，我有一个文本框，用户以16/02/2013格式输入日期，以搜索该特定日期的所有条目。
在数据库中，我有一列以这种格式存储日期。16/02/2013 02:47:36 AM。

有人可以用sql来建议我查询从数据库中提取该特定日期的所有条目并将其放在数据集上。

我正在使用它，但它不起作用。

  public DataSet OrderByDate（string date）
 {
 // string connString =Provider = Microsoft.ACE.OLEDB.12.0;数据Source = C：\\Users\\Amrit\\Desktop\\Database.accdb; Persist Security Info = False;; 
 DataSet dataSet = new DataSet（）; 
 OleDbConnection oleConn = new OleDbConnection（connString）; 
 
 try 
 {
 oleConn.Open（）; $客户[客户名称]，客户[电子邮件地址]，客户[电话号码]，客户名称，客户名称，客户名称，客户名称，客户[地址1] +空格（2）+客户[地址2] +空格（2）+客户[城市] +空格（2）+客户[邮政编码] +空格（2）+客户[国家]作为地址，客户[购买项目]，客户[购买日期]客户[总价]从客户[购买日期] LIKE'++日期+％; 
 OleDbDataAdapter dataAdapter = new OleDbDataAdapter（sql，oleConn）; 
 dataAdapter.Fill（dataSet，Customer）; 
} 
 catch（Exception ex）
 {
 Console.WriteLine（ex.ToString（））; 
} 
 finally 
 {
 oleConn.Close（）; 
} 
 if（dataSet.Tables.Count< = 0）
返回null; 
 else 
 return dataSet; 
} 
  

在datbase中，datetime存储为日期/时间格式。

解决方案

以这种方式传递日期值不起作用，因为.NET中的默认格式化日期和时间值无法被SQL引擎识别。 / p>

要将任何数据传递给您的查询，最好始终使用参数。向您添加一个参数命令字符串：

  string sql =SELECT * FROM Customer WHERE PurchaseDate = @pdate; 
 
 OleDbDataAdapter adapter = new OleDbDataAdapter（sql，connection）; 
 adapter.SelectCommand.Parameters.AddWithValue（pdate，date）; 
 adapter.Fill（dataSet，Customer）; 
  

@pdate 在命令文本中是参数。必须在执行命令之前提供该参数的值，如下例所示。

您还可以使用简单的字符串连接将您的值填充到SQL语句中，但只能使用简单的整数或字符串值，并且通常不推荐使用，因为它会受到SQL注入攻击。

In my windows form, i have one text box where users enters the date in the format 16/02/2013 to search for all the entries on that particular date. In database i have one column which stores date in this format.16/02/2013 02:47:36 AM.

Can somebody advise me with sql query to extract all the entries from database for that particular date and put it on dataset.

I am using this but it is not working.

public DataSet OrderByDate(string date)
{
    //  string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Amrit\\Desktop\\Database.accdb ;Persist Security Info=False;";
    DataSet dataSet = new DataSet();
    OleDbConnection oleConn = new OleDbConnection(connString);

    try
    {
        oleConn.Open();
        string sql = "SELECT  Customer.[Title] + SPACE(2)  + Customer.[Customer's Name] as CustomerName, Customer.[Customer's Ebayname], Customer.[Email Address], Customer.[Phone Number], Customer.[Address 1] + SPACE(2)  +Customer.[Address 2] + SPACE(2)  + Customer.[City] + SPACE(2)  + Customer.[Post Code]+  SPACE(2)  + Customer.[Country] as Address, Customer.[Item Purchased], Customer.[Purchased Date], Customer.[Total Price] FROM Customer WHERE [Purchased Date] LIKE '" + "'" + date + "%'";
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql, oleConn);
        dataAdapter.Fill(dataSet, "Customer");
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
    }
    finally
    {
        oleConn.Close();
    }
    if (dataSet.Tables.Count <= 0)
        return null;
    else
        return dataSet;
}

In datbase the datetime is stored as Date/Time format.

解决方案

Passing date values in this way does not work, because default formatted date and time value in .NET is not recognized by your SQL engine.

To pass any data to your query, it is best to always use parameters. Add a parameter to you command string:

string sql = "SELECT * FROM Customer WHERE PurchaseDate = @pdate";

OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connection);
adapter.SelectCommand.Parameters.AddWithValue("pdate", date);
adapter.Fill(dataSet, "Customer");

@pdate in the command text is a parameter. Values for the parameter must be supplied before executing the command, as you see in the example.

You can also use simple string concatenation to fill in your values into your SQL statement, but that's only possible with simple integer or string values, and is generally not recommended because it is subject to SQL injection attack.

这篇关于用日期搜索数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！