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