Click here to Skip to main content
11,430,708 members (69,807 online)
Click here to Skip to main content

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

, 31 Jan 2007
Rate this:
Please Sign up or sign in to vote.
SQL Query - Dates
  • 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


Sample image

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

Share

About the Author

Member 2128730

United Kingdom United Kingdom
No Biography provided

Comments and Discussions

 
GeneralGood Work Ryan Pin
lervy131-Jan-07 22:54
memberlervy131-Jan-07 22:54 
GeneralSqlServer Pin
Kevin I31-Jan-07 7:02
memberKevin I31-Jan-07 7:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150428.2 | Last Updated 31 Jan 2007
Article Copyright 2007 by Member 2128730
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid