Get Date from Database Access

[Dorayaki]

Hi Can anyone please check my code ?
I'm trying to get today's date from database
I tried many ways and I'm stuck now..

at my database access
the transdate appears like this

11/29/2009 2:02:23 AM

and this is my query code from VB.NET

"SELECT TransDate, Deposit, Withdrawal, TotalAmount FROM Transactions " & _
                             "Where UserID =" & AccID & " and TransDate = " & DateTime.Today() & " " & _
                             "ORDER BY TransID"

is there anything wrong ? I debug and I got the date correct but the data won't show up
please help..
thanks

[TomW]

Database's hold a DateTime value so its important to include the time such as using a between Todays date at 12:00am and tomorrows date

[TomW]

Also dont forget to include the # signs surrounding your date

[Dorayaki]

uhm sorry i don't get it..

anyway i had tried using # for the date, but still i can't get the value

i only want to get today's date not including the time
is it possible ?

[TomW]

Time will automatically be applied as 12am thats why its better to use a between statement to give you all records for the specified day.

[Dorayaki]

any example you would like to give for the date and time ?

thanks

[CasperG]

Where do you want the data to appear?

you have selected the data, but you havnt said what you want to do with it?

[Dorayaki]

I want to show it on the listview

Here is my full code

Sub loadtranstoday()
        Try
            Dim current As DateTime = DateSerial(Now.Year, Now.Month, Now.Day)
            Dim sstatement = "SELECT TransDate, Deposit, Withdrawal, TotalAmount FROM Transactions " & _
                             "Where UserID = " & AccID & " and TransDate = #" & current & "# " & _
                             "ORDER BY TransID"
            MsgBox(sstatement)
            Dim sCommand As OleDbCommand = New OleDbCommand(sstatement, sConnection)
            sConnection.Open()
            Dim sReader As OleDbDataReader = sCommand.ExecuteReader()

            lstViewDWToday.Items.Clear()
            While sReader.Read()
                lstViewDWToday.Items.Add(Format(sReader(0), "dd/MM/yyyy"))
                lstViewDWToday.Items(lstViewDWToday.Items.Count - 1).SubItems.Add(sReader(1).ToString)
                lstViewDWToday.Items(lstViewDWToday.Items.Count - 1).SubItems.Add(sReader(2).ToString)
                lstViewDWToday.Items(lstViewDWToday.Items.Count - 1).SubItems.Add(sReader(3).ToString)
            End While
            sConnection.Close()
        Catch sException As System.Exception
            MsgBox(sException.Message, MsgBoxStyle.OkOnly Or MsgBoxStyle.Critical)
        End Try
        If sConnection.State <> ConnectionState.Closed Then sConnection.Close()
        If lstViewDWToday.Items.Count > 0 Then lstViewDWToday.Items(0).Selected = True
        lstViewDWToday.Focus()
    End Sub

When I set breakpoint, it run until While sReader.Read() and then Jump to End while , it skip the whole thing inside..
but If I delete the "transdate = current" then it works
but what I want is to show the current date...
so any idea ?

thanks

[GeekByChoiCe]

Dim current As Date = Date.Now
        Dim sstatement = "SELECT TransDate, Deposit, Withdrawal, TotalAmount FROM Transactions " & _
                            "Where UserID = " & AccID & " and TransDate LIKE '" & current.ToShortDateString & "' " & _
                            "ORDER BY TransID"

current = 11/30/2009 7:32:14 AM
current.ToShortDateString= 11/30/2009

so you search in your query for every entry thats like 11/30/2009 which should solve the problem.

the # that one of the posters mentioned is only visible in debugging to mark it as date, but never shows up anywhere.

[Dorayaki]

Not working, anyway I found out a way to do it..
Thanks

you might want to know how to do it..
so here we go..

"Where UserID = " & AccID & " and Day(TransDate) = " & Now.Day & " and Month(TransDate) = " & Now.Month & " and Year(TransDate) = " & Now.Year & " " & _