Sorting database by datetime date

[NateBOIT]

Hi. I have never used ASP before, but I have been given the task of creating a ASP Web interface to display the results of a database query. I need to have a form that allows the user to sort the results by date range. The database has the date and time stored in a 'datetime' data type field called "eventtime".

The first thing i need to do is create a variable, lets say "eventdate", that takes just the date out of the datetime field so i can compare it to the user input dates. However, i assume i need to somehow do this after i make the 'rs' connection to the database, but before i construct and execute the query.

I then need to have the form i create post the user date values into variables that i can also use in the query. Let's say "startdate" and "enddate". Should i use POST or GET to retrieve the the user input?

I then need to construct a query with all variables in it that searches for the correct date range. Something like:

"select * from table where" & eventdate & ">" & startdate & " and " & eventdate & "<" & enddate & ";"

I'm sure my syntax might be a little off, but please correct it as you see fit.

Also, a simple bounds checking procedure would be nice to make sure the user actually entered a date.

Any help with any and all of this would be greatly appreciated.

 

[xwb]

Dates have always been a problem because there is no standard storage for dates. Easiest way is to pass the date as a string and use the 'd' value as described in

http://www.astro.princeton.edu/~esirko/idl_html_help/using11.html

Another simplification, you can use the between clause. Basically

"select * from table where" & eventdate & " between " & startdate & " and " & enddate & ";"

 

[Tarwn]

1) Get/Post: Which of these you pick for your form determines several factors:
Get: Passes form data through the querystring (URL), more limited in length than POST (arbitrary, based on browser), retrieved using the Request.Querystring collection
Post: passes form data as a form post (ie, attached data in HTTP response header), less limited in length, retrieved using the Request.Form collection

This decision should not affect your logic much at all.

2) Logic Issues
Firstly, you cannot compare the user-input values to a value in the database without somehow querying the database. So your comment on taking the eventdate value out of the database before using your rs (recordset?) is flawed.
Additionally, if you create a SQL statement like so:
"SELECT * FROM Table WHERE " & someAspVar & " = " &otherAspVar"
your not going to get what your expecting. In fact you will either get all of the records from the table or none of the records, as you are not comparing a field in each record to a value, but instead comparing two values that will evaluate exactly the same for each row that is examined in the table. Ifthe condition evaluates to true, you will get every row, if it evaluates to fale you will get none of the records. basically it is the same as doing one of these:
SELECT * FROM Table WHERE 1 = 1
SELECT * FROM Table WHERE 1 = 0

---

Ok, so based on your input it looks like you want to do the following (I changed the order a little)
- Check the user inputs to make sure they are both dates
- Query a database table for all records that have an "eventdate" that is between the two posted dates

Checking the user inputs is fairly simple, as there is a built-in function to validate dates: IsDate()

[b]If you used POST[/b]
Dim startdate, enddate
If Not (IsDate(Request.Form("startdate")) AND IsDate(Request.Form("enddate"))) Then
   Response.Write "Sorry, one of those wasn't a date"
End If
startdate = Request.Form("startdate")
enddate = Request.Form("enddate")

[b]If you used GET[/b]
Dim startdate, enddate
If Not (IsDate(Request.Querystring("startdate")) AND IsDate(Request.Querystring("enddate"))) Then
   Response.Write "Sorry, one of those wasn't a date"
End If
startdate = Request.Querystring("startdate")
enddate = Request.Querystring("enddate")

Now we know they are both dates. Next to build the SQL string:

Dim strSQL
strSQL = "SELECT * FROM MyTable WHERE eventDateFieldName BETWEEN '" & startdate & "' AND '" & enddate & "'"

Dates need to have a delimiter around them in a SQL query. In many SQL variants this means single quotes aorund them, in some it will be other characters (like #'s). Note that this is justa string, there is nothing special about it. ASP will not check the syntax magically because it has no understanging of any special subset of strings that is meant to be a SQL query. This is often overlooked early on.

Executing the query

Dim conn, rs
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "your connection string or DSN"

Set rs = conn.Execute(strSQL)

That last line is where you will find out if the syntax of your SQL string was correct or not. The first way to troubleshoot things if it gives you an error there is to try and Response.Write the SQL string before that point (followed by a Response.Flush to flush the buffer to the browser) and then paste it into the database query tool of your choice to see why it's not working.

Once you execute it's just a matter of outputtingthe results or doing something with them.

'check if there are results in the recordset
If rs.EOF Then
   'recordset pointer only points to EOF when returned if recordset is empty
   Response.Write "Sorry, no events n your time range"
   Response.End
End If

'queue up to the first record, just in case
rs.MoveFirst

'loop through records
Do Until rs.EOF
   Response.Write "Event: " & rs("eventnameField") & "<br/>"
   Response.Write "Date: " & rs("eventdate") & "<br/>"
   Response.Write "<hr/>"

   'move to next record
   rs.MoveNext
Loop

The last thing you should always do is cleanup your recordset and connection objects. this frees up the resources a little quicker:

Set rs = Nothing
conn.Close
Set conn = Nothing

-T