searching with date value

[ninjaimp]

now i am doing something that i thought would be real straight forward but for some reason is not.

Im trying to lookup records in a DB based on todays date. ow i know the db contains records for these dates but nothing ever gets returned. My code is below and i wondered if anyone had any ideas?

Dim sql As String
        Dim Command As OleDbCommand
        Dim con As System.Data.OleDb.OleDbConnection
        Dim Searchds As New DataSet
        Dim Searchda As OleDb.OleDbDataAdapter
        Dim TodaysDate As Date

        Dim RecordCount As Integer

        con = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\cdr\ReferenceData.mdb")
        con.Open()

        TodaysDate = Format(Date.Today, "dd/MM/yyyy")

        sql = "SELECT * FROM tblStay WHERE tblStay.ArriveDate = #" & TodaysDate & "#"
        Command = New OleDbCommand(sql, con)
        Command.ExecuteNonQuery()

        Searchda = New OleDb.OleDbDataAdapter(sql, con)
        Searchda.Fill(Searchds)

        RecordCount = Searchds.Tables(0).Rows.Count

        If RecordCount > 0 Then
            For i = 0 To RecordCount - 1
                lst1.Items.Add(Searchds.Tables(0).Rows(i).Item(1) & " : " & Searchds.Tables(0).Rows(i).Item(3))
            Next
        End If

        con.Close()

 

[cjard]

probablly because dates also contain a time component and youre not realising that a date is stored as the number of decimal days since some "big bang"

i.e. if the "big bang" is 01 jan 1901, then a date of midnight 01 jan 1901 is stored as 0 (big bang plus 0 days)

If you store a date of SIX OCLOCK IN THE EVENING on the 02 JAN 1901 then this is stored as 1.75 i.e. 1 day + 1800hours/2400 hours
= 1 + 18/24
= 1.75

so if you then go and search for 02 jan 1901 (witha time component defaulted to MIDNIGHT) you are actually searching for big bang + 1,
-> and because 1 is not equal to 1.75, you dont get your row


I recommend that you do a range:

SELECT * FROM tblTabl WHERE dateCol >= 02-jan-1901 AND dateCol < 03-jan-1901

 

[cjard]

the other thing I could mention ,is that youre doing your data access the hard/wrong way. take a read of DW2 link in my sig, section "Creating a simple data app"

 

[ninjaimp]

sorry i dont really follow - it is still quite early ish!

in my db are dates stored as '19/06/2008 09:37:41' and im querying this database with a value of '19/06/2008' - how with your method above do i get it to return a record?

many thanks for your help

 

[ninjaimp]

hi

ok looked at your walkthrough and can see how much easier it is to reference any data in the db. one question though some of the records have more than one date associated with them but i am only able to drag across a date field (cos this is what it is on the DB) is there any way i can bind it to a datagrid or something to display all dates associated with the current record

any thanks

 

[cjard]

sorry i dont really follow - it is still quite early ish!

OK, i'll ask you an analogical question.

Suppose we arrange to meet at starbucks at 9pm on 20 June 2008. I go there at MIDNIGHT on 20 June 2008. Being the patient guy I am, I wait for you for 5 minutes, then go home
9 hours later you show up but i'm not there.

When we next meet you say "hey, howcome you weren't at starbucks that day we were suppsoed to meet?"

I say "i dont know; i went to starbucks on that day!"

Neither of us is lying, just like your database is not lying when it tells you "sorry, there are no records having a date of MIDNIGHT on 19/06/2008"


Now go and re-read my post and let it sink in

 

[cjard]

hi

ok looked at your walkthrough and can see how much easier it is to reference any data in the db. one question though some of the records have more than one date associated with them but i am only able to drag across a date field (cos this is what it is on the DB) is there any way i can bind it to a datagrid or something to display all dates associated with the current record

any thanks

Time for you to move onto the section "Creating a Form to Search Data", understand how dates are stored, and then ponder on how to select a range of values

 

[ninjaimp]

hi

am progressing through the walkthroughs which are very good but i have a hit a small issue.

I have created a query on my database for a simple search by name: The sql that is being used is:

SELECT CustID, CustName, CustAddress, CustPostcode, CustTel, CustEmail, CustNotes, CustRoom, CustStayArrive, CustStayLeave
FROM tblCustData
WHERE CustName = '" & SearchBox.Text & "'


but it never returns anything? what have i done wrong this time?!

 

[cjard]

Can you show me exactly what page of Microsoft's tutorial you got that SQL, or the instruction to write an SQL like that, from?

 

[ninjaimp]

the final part of the sql i changed based on some sql i have done to query databases taking the value from a text box.

the code that was on the tutorial was this:

SELECT CustID, CustName, CustAddress, CustPostcode, CustTel, CustEmail, CustNotes, CustRoom, CustStayArrive, CustStayLeave
FROM tblCustData
WHERE CustName = ?


but how do i make a reference to the ? so that it contains the search term?

regards

 

[cjard]

At the risk of repeating myself

Can you show me exactly what page of Microsoft's tutorial you got that SQL, or the instruction to write an SQL like that, from?

Really, I dont ask these questions for the good of my health. When I ask a question, please answer it regardless of how much other peripheral discussion you choose to provide

 

[ninjaimp]

apologies for the delay and i appreciate your help but have not been able to get near a machine for awhile and besides, ive been busy trying to solve things by myself as to dont bother fellow forumees like yourself!

the link which i followed is http://msdn.microsoft.com/en-us/library/hbsty6z7(VS.80).aspx

no doubt i've been doing it wrong, but a bit of trial and error never hurt anyone!

Cheers

 

[cjard]

And exactly what part of that tutorial are you having a problem with? As far as I can see here, you didnt actually do as it says?!

 

[ninjaimp]

i said i may have done it wrong.

I created a new form, added the datasource to the form and then on the datagrid i chose 'Add Query'.

my question is that on the sql that they suggest, what does the ? relate to? how does it know what control to take the search value from?

 

[Hikidar]

SELECT CustID, CustName, CustAddress, CustPostcode, CustTel, CustEmail, CustNotes, CustRoom, CustStayArrive, CustStayLeave
FROM tblCustData
WHERE CustName = '" & SearchBox.Text & "'

Change the last line to

WHERE CustName like '%" & SearchBox.Text & "%'

where the % is the wild card character. For a "starts with" query, you would only put the % after the text, while a "ends with" query would use the % at the beginning of the text.