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

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

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 ?

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.

Where do you want the data to appear?

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

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

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.

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 & " " & _
This question has already been answered. Start a new discussion instead.