Click here to Skip to main content
15,922,696 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
VB
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

VB
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:

VB
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.
Posted
Updated 14-Aug-13 2:27am
v2
Comments
Maciej Los 14-Aug-13 7:02am    
What you mean: it isnt working...?
What error? Which line?
[no name] 14-Aug-13 7:06am    
if the query returns 3 record for example the datatable returns 0 rows ..as if no data were found but actually their exist 3 .. if the qery returns one value all work normaly.. thanks
Maciej Los 14-Aug-13 7:11am    
Replace your code: Dim xx As New DataTable() with Dim xx As DataTable = New DataTable().
[no name] 14-Aug-13 7:16am    
didnt work
ZurdoDev 14-Aug-13 7:41am    
How do you know there are 3 records. It sounds like the way you are sending the SQL is not the way you are actually verifying it. Is this an Access db?

1 solution

Your problem is this:

VB
".... 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:
SQL
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.
 
Share this answer
 
v3
Comments
[no name] 14-Aug-13 12:26pm    
can u help me in that ?
the database is build in access when i insert a record i insert the date and time it is requested ..
i want to select all the records that pass on it 2 days from today and put all the unique results on a datatable so i can take each record and continue my work on it
thanks'
Keith Barrow 15-Aug-13 9:05am    
Hi, I've updated my solution. Hopefully this is what you need.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900