查询未填充数据表 [英] the query is not filling the datatable

问题描述

  Dim  sql  As   String  =   SELECT DISTINCT tbl_campaign.cmp_user_id FROM tbl_campaign WHERE（（tbl_campaign.cmp_date）=＃ & datenow&  ＃） 
 
 < span class =code-keyword> Dim  dt  As  DataTable = Codes.ExecuteSelect（sql）

方法是

 公共 共享 功能 ExecuteSelect（ ByVal  query  As   String ） As  DataTable 
  Dim  xx  A s  新 DataTable（）
 使用 a1 作为 新 OleDbDataAdapter（查询，ConnectionString）
 a1.Fill（xx）
 结束 使用 
 返回 xx 
 结束 功能 
 

如果它返回1条记录它正常工作但有多个它不工作...任何建议



Th OP将此添加到评论中：

  Dim  datenow 正如 日期 = 日期 .Now.AddDays（-2）
  Dim  sql  As  字符串 =   SELECT DISTINCT tbl_campaign.cmp_user_id FROM tbl_campaign WHERE（（tbl_campaign .cmp_date）=＃&日期和时间 ＃） 
 
  Dim  dt  As  DataTable = Codes.ExecuteSelect（sql）
 
  If  dt.Rows.Count>  0  然后 
 对于 i 作为 整数 =  0    dt.Rows.Count  -   1  
  Dim  cmpID 作为 整数 = dt.Rows（i）.Item（ 0 ）
 下一步 
 结束 如果 

这看起来非常相关，表格中还包含3行他希望获得的行，但它返回0。 div class =h2_lin>解决方案

你的问题是：

   .... WHERE （（tbl_campaign.cmp_date）=＃& datenow &   ＃） 

假设您的餐桌有以下日期：



2013-08-12 13：36：12.522

2013-08-12 13：32：14.620

2013-08-12 13：02：44.212





您的 datenow 变量（令人困惑地命名，您将其设置为两天前）必须与完全匹配到毫秒，非常不可能。



您需要重新编写SQL。我不能告诉你你需要什么，因为我没有要求。但是平等不会起作用。



您可以使用多种功能，我最好的猜测是你要使用 datediff [ ^ ]并查找值= 2天（或-2天，具体取决于您作为开始/结束的位置）。





最后构建这样的查询会打开SQL注入攻击，你应该google参数化查询。我认为您根本不需要参数，数据库可以通过 http://www.w3schools.com获取今天的日期/sql/func_getdate.asp [ ^ ] ，这又取决于你的要求。









您的查询类似于：

  SELECT  
  DISTINCT  tbl_campaign.cmp_user_id 
  FROM  
 tbl_campaign 
 在哪里 
 DATEDIFF（DAY，GETDATE（），tbl_campaign.cmp_date）=  2  

这样可以在未来两天获得所有日期。请注意，因为您可以在SQL中使用GetDate，所以不必传递它，关闭潜在的SQL注入漏洞。以上工作在SQL Server，YMMV，如果使用其他东西。

Dim sql As String = "SELECT DISTINCT tbl_campaign.cmp_user_id FROM tbl_campaign WHERE ((tbl_campaign.cmp_date)=#" & datenow & "#)"

        Dim dt As DataTable = Codes.ExecuteSelect(sql)

the method is

Public Shared Function ExecuteSelect(ByVal query As String) As DataTable
       Dim xx As New DataTable()
       Using a1 As New OleDbDataAdapter(query, ConnectionString)
           a1.Fill(xx)
       End Using
       Return xx
   End Function

if it is returning 1 record it work correctly but with more than one it isnt working ... any suggestions

Th OP added this to the comments:

Dim datenow As Date = Date.Now.AddDays(-2)
Dim sql As String = "SELECT DISTINCT tbl_campaign.cmp_user_id FROM tbl_campaign WHERE ((tbl_campaign.cmp_date)=#" & datenow & "#)"

Dim dt As DataTable = Codes.ExecuteSelect(sql)

If dt.Rows.Count > 0 Then
    For i As Integer = 0 To dt.Rows.Count - 1
        Dim cmpID As Integer = dt.Rows(i).Item(0)
    Next
End If

This looks pretty relevant, also the table contains 3 rows which he expects to get, but it is returning 0.

解决方案

Your problem is this:

".... WHERE ((tbl_campaign.cmp_date)=#" & datenow & "#)"

let's say your table has the following dates:

2013-08-12 13:36:12.522
2013-08-12 13:32:14.620
2013-08-12 13:02:44.212


your datenow variable (which is confusingly named, you've set it to two days ago) has to match exactly down to the millisecond, very unlikely.

You need to re-work your SQL. I can't tell you what you need exactly as I don't have the requirement. But the equals isn't going to work.

You can use several functions, my best guess is you want to use datediff[^] and find values = 2 days (or -2 days, depending which you put as start/end).


Finally building the query like this opens you up to SQL injection attacks, you should google "parametrised query". I think you don't need the parameter at all, the database can get today's date via http://www.w3schools.com/sql/func_getdate.asp[^], again this depends on your requirements.


[Edit]

You a query along the lines of:

SELECT 
    DISTINCT tbl_campaign.cmp_user_id 
FROM 
     tbl_campaign
WHERE
    DATEDIFF(DAY, GETDATE(), tbl_campaign.cmp_date) = 2

This will get everything with a date two days into the future. Note, as you can use GetDate in the SQL, you don't have to pass it, closing the potential SQL Injection hole. The above works in SQL Server, YMMV if using something else.

这篇关于查询未填充数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！