the query is not filling the datatable

Question

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 14-Aug-13 0:47am

Hidhoud1991

Updated 14-Aug-13 2:27am

Keith Barrow

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Keith Barrow · Answer 1 · 2013-08-14T02:46:00

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.