Click here to Skip to main content
14,635,966 members
Rate this:
Please Sign up or sign in to vote.
See more:
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.
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?
[no name] 14-Aug-13 7:55am
   
yes access and i generate the query in access it returns 3 records thats y i know that it return these values
ZurdoDev 14-Aug-13 8:02am
   
Then perhaps your date is not getting passed in the way you think it is. I would put a breakpoint and examine it.
Smanish87 14-Aug-13 7:03am
   
Show me your full code so that i can understand
[no name] 14-Aug-13 7:08am
   
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 is all the code that i have till now ...the method executeselect i posted it above ...
Keith Barrow 14-Aug-13 8:29am
   
Hi, I've improved your question ("Improve question" in green under and to the right of your question). This information is relevant. You should do this (so everyone can see) and reply in the comments that you've updated the question.
[no name] 14-Aug-13 7:12am
   
the column cmp_date is declared as date/time it has values such as 8/11/2013 12:00:00 AM
Maciej Los 14-Aug-13 7:37am
   
Please, post example data and expected output. Use "Improve question" widget.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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.
   
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100