CODEWITHSUNDEEP
vb.netms-access
Jonas
Jonas

Reputation: 25

Get data from database WHERE date is today VB.net

I want to get all the fields from a row where the date from column "Date" is today's date.

My code is

Dim today As Date = DateTime.Now
vandaag = Format(today, "dd/MM/yyyy")

"select * from tblPatients where PatientDate =" & today & ""

Can somebody help me please? It's for school...

Upvotes: 1

Views: 13243

Answers (5)

Ad Kahn
Ad Kahn

Reputation: 579

Try this

"select * from tblPatients where PatientDate =" & Now() & ""

or 

 "select * from tblPatients where PatientDate =" & Now().Date() & ""

Upvotes: 0

Steve
Steve

Reputation: 216293

Never use string concatenation to build a SQL Command to pass to your database engine.
Using Parameters, you avoid problems in text parsing (dates, string with special characters and so on) but, most important, you avoid Sql Injection Attacks.
This is an habit that every developer that works with database should acquire as fast as possible.
So, supposing you have a OleDbConnection already built and opened, you could write

Dim strSql As String = "select * from tblPatients where PatientDate = ?dt"
Using dadapter = New OleDbDataAdapter()
    dadapter.SelectCommand = New OleDbCommand(strSql, con)
    dadapter.SelectCommand.Parameters.AddWithValue("?dt", DateTime.Today)   
    Dim dset As DataSet = New DataSet()
    dadapter.Fill(dset, "Books")
End Using

Notice also the Using statement, this is another good practice to follow.
Using will take care of the disposing of objects like OleDbConnection and OleDbDataAdapter from memory thus releasing all system resources used by the object

Upvotes: 2

KekuSemau
KekuSemau

Reputation: 6856

Actually, you do not need parameters at all in your query:
SELECT * FROM tblPatients WHERE PatientDate = DATE()
If the PatientDate was a combined date-time, you could use:
SELECT * FROM tblPatients WHERE PatientDate BETWEEN DATE() AND DATEADD('d', 1, DATE())
The Date()-Function will have a time-part as 0:00, so this will give you the right results for the current day.

Upvotes: 2

pete
pete

Reputation: 25081

Use the DATEADD and DATEDIFF functions to remove the time from the date:

Dim dayPart As String = Chr(34) & "d" & Chr(34) 'Chr(34) = ", resulting string is "d" (enclosed in quotes)
Dim query As String = "SELECT * FROM tblPatients"
'Declared as separate variables for better readability
Dim patientDate As String = "DATEADD(" & dayPart & ",  DATEDIFF(" & dayPart & ", 0, PatientDate), 0)"
Dim todaysDate As String = "DATEADD(" & dayPart & ",  DATEDIFF(" & dayPart & ", 0, Now());"
'patientDate = 'DATEADD("d",  DATEDIFF("d", 0, PatientDate), 0)' which is the patientDate, stripped of a timevalue
'todaysDate = 'DATEADD("d",  DATEDIFF("d", 0, Now()), 0)' which is today's date, stripped of a timevalue
'This works because:
'DATEDIFF("d", 0, PatientDate) returns the number of days that have passed since the "zero" date and strips the time component
'DATEADD("d",  DATEDIFF("d", 0, PatientDate), 0) adds the DATEDIFF calculation to the "zero" date returning a date
'equal to PatientDate with no time component.
'This same principle is applied to the 'Now()' function to get today's date with no time component.
'
'Now that patientDate can equal today's date (because the time components have been stripped away), we can affix the where clause
query &= " WHERE " & patientDate & " = " & todaysDate
'and run the query

Upvotes: 0

andy
andy

Reputation: 6079

Dim strSql As String = "select * from tblPatients where PatientDate =#" & today & "#"
Dim dadapter As OleDbDataAdapter
dadapter = New OleDbDataAdapter()
dadapter.SelectCommand = New OleDbCommand(strSql, con)
Dim dset As DataSet = New DataSet()
dadapter.Fill(dset, "Books")

Upvotes: 0

Related Questions