SQL: Populate a datagrid from a set or specified date range

Download source code

Contains access database and .aspx file built in Microsoft's Webmatrix program.

SQL: Populate a datagrid from a specified date range.

Introduction

This article demonstrates how a SQL query can populate a datagrid from a specified date range. When I first encountered this problem I found too much useless information and examples that just did not work so I have created this example to help out others with the same problems I had. The article shows how to select data from a database and how to display the results in a datagrid based on two dates; "startdate" and "enddate".

Screen Layout

Code for button Click (Press to select entries for last week)

Sub Button1_Click(sender As Object, e As EventArgs) Dim dbconnection As New OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0;data source=c:\dates.mdb")
comment: Selects all data from table [nameoftable] where [date in database] >= to [current date] AND <= to [current date]+ 7 days
Dim connectionstring As String = "SELECT * From date1 where DATEVALUE(bdate) >= date() and DATEVALUE(bdate) <= date()+7 order by bdate asc " '
Dim dbadapter As New OleDbDataAdapter(connectionstring, dbconnection)
Dim clientdataset As New DataSet()

dbadapter.Fill(clientdataset, "date1")
Dim clientview As New DataView(clientdataset.Tables("date1"))
dggrid.DataSource = clientview
comment: binds the results to the data grid.
dggrid.DataBind()

comment: this puts the currents date into lbl1 and end date into lbl2
Dim dateTimeInfo as DateTime = DateTime.Now
Dim dateTimeInfo2 as DateTime = DateTime.Now.AddDays(7)
lbl1.text=dateTimeInfo.ToString("MMM dd, yyyy")
lbl2.text=dateTimeInfo2.ToString("MMM dd, yyyy")
End Sub

Results

On the 31st Jan 2007 the results from querry:

Results

id	bdate
2	02/02/2007 08:33:28

Code for button Click (Custom dates)
More than likely the information required will be based on a range of dates: for example show all information stored in database between "01/01/2007 and 01/01/2007"

The SQL statement:

SELECT * From date1 where (DATEVALUE(bdate) >= DATEVALUE('" & txtdate1.Text & "') and DATEVALUE(bdate) <= DATEVALUE('" & txtdate2.Text & "')) order by bdate asc "

DATEVALUE(bdate) converts the value in the database into a date, it ignores any time that is included automatically.
DATEVALUE('" & txtdate1.Text & "') This converts the value in the textbox into a date.

Dim dbconnection As New OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0;data source=c:\dates.mdb")
comment: Selects all data from table [nameoftable] where [date in database] >= to [textbox1] AND <= to [textbox2].
Dim connectionstring As String = "SELECT * From date1 where (DATEVALUE(bdate) >= DATEVALUE('" & txtdate1.Text & "') and DATEVALUE(bdate) <= DATEVALUE('" & txtdate2.Text & "')) order by bdate asc "
Dim dbadapter As New OleDbDataAdapter(connectionstring, dbconnection)
Dim clientdataset As New DataSet()

dbadapter.Fill(clientdataset, "date1")
Dim clientview As New DataView(clientdataset.Tables("date1"))
dggrid.DataSource = clientview
comment: binds the results to the data grid.
dggrid.DataBind()

comment: this converts the dates entered into the textboxes and shows them in the labels.
Dim dateTimeInfo as DateTime = txtdate1.text
Dim dateTimeInfo2 as DateTime = txtdate2.text
lbl1.text=dateTimeInfo.ToString("MMM dd, yyyy")
lbl2.text=dateTimeInfo2.ToString("MMM dd, yyyy")

Results:

Select Between Jan 01, 2007 and Feb 02, 2007

(REMOVED BUTTONS IN EXAMPLE)

Results

id	bdate
1	01/01/2007 08:33:28
3	03/01/2007 08:33:28
4	04/01/2007 08:33:28
5	05/01/2007 08:33:28
6	06/01/2007 08:33:28
7	20/01/2007 08:33:28
2	02/02/2007 08:33:28

Summary
DATEVALUE() converts string to date
DATEVALUE('" & txtdate1.Text & "') used in SQL querry, will read in a date from a textbox
Dim dateTimeInfo as DateTime = txtdate1.text sets variable 'dateTimeInfo' to date type and reads in value from testbox
lbl1.text=dateTimeInfo.ToString("MMM dd, yyyy") sets label to value of 'dateTimeInfo and formats the date to 'Jan 01, 2007"

By Ryan Tyler

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here