Member Avatar for hwoarang69

User will enter a date, for example "7/1/2015". Than I will check in database and return some result if match. Note in database there is data/Time but user will enter only date.

Below I have a following database set up

Table = "workStartTable"
|----|------------------------|
| ID | StartBy                |
|----|------------------------|
| 1  | 7/1/2015 2:30:00 PM    |
| 2  | 1/22/2013 8:00:00 AM   |
| 3  | 10/22/2014 10:25:00 AM |
| 4  | 4/5/2010 5:00:00 PM    |
| 5  | 7/3/2015               |
|----|------------------------|

I have created the following query which kind of work. This below query will work becuase there is not time for this record in database.
but "07/01/2015" will not work! this is becuase "07/01/2015" has the time too.

Dim query As String = "SELECT DISTINCT * FROM workStartTable WHERE StartBy = #" & "07/03/2015" & "#;"

Please note I already have tried the following queries and they dont return any result:

Dim query As String = "SELECT DISTINCT * FROM workStartTable WHERE StartBy = " & "07/03/2015" & ";"
Dim query As String = "SELECT DISTINCT * FROM workStartTable WHERE StartBy LIKE % " & "07/03/2015" & " %;"
Dim query As String = "SELECT DISTINCT * FROM workStartTable WHERE StartBy LIKE " & "07/03/2015" & " %;"
  • 2 Contributors
  • 3 Replies
  • 215 Views
  • 4 Hours Discussion Span
  • Latest Post Latest Post by Hardz
Member Avatar for hwoarang69

Ops forgot to write this:

Below I have a following database set up in Access. and Col "StartBy" type is Date/Time ***

Member Avatar for Hardz

Hi,

I think the better way to query a date from MS access database is to use a range or between operator.

Dim idate As String = "07/03/2015"
        'using range
        Dim query As String = "SELECT DISTINCT * FROM workStartTable WHERE StartBy Between #" & idate & "# And #" & idate & " 23:59:59" & "# "

Or you can also use this query:

Dim query As String = "SELECT DISTINCT * FROM workStartTable WHERE StartBy >= #" & idate & "# And StartBy <= #" & idate & " 23:59:59" & "# "

Hardz

Edited by Hardz because: StartDate to StartBy
Member Avatar for Hardz

Hi,

The above sample code is not safe and prone to sql injection. By using a parameterized query:

Dim idate As String = "07/03/2015"
        Dim query As String = "SELECT DISTINCT * FROM workStartTable WHERE StartBy Between @ And @ + ' 23:59:59' "

'your code initialization for connection string, Oledbcommand, etc.
'
'
'assuming that cmd = oledbcommand so:
cmd.Parameters.AddWithValue("StartBy", idate)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.