Help with Record Set filtering

slakker · Mar 29, 2005

Help with Record Set filtering

Hi all,

I have a page with a record set built that displays the data from my releases database.
The purpose of this page is to show people all the applications that are being released within each release.
an example would be: we may have a release on 3/21, and then another one on 4/25, and so on..

Please see my MS Access DB structure below:

------------------------------
(DB) releases.mdb

(tbl) ReleaseDates
(fld) rlsDatesID (linked)
(fld) ReleaseDate

(tbl) Releases
(fld) id
(fld) Application
(fld) Time Created
(fld) Description
(fld) Contact Info
(fld) releaseDate (linked)
-------------------------------

currently, this the record set listed below display ALL applications in the DB, and I would like it to filter based on a release date (3/21 or 4/25) selected using a dropdown form on the page.
The dropdown will pull all available release dates from the 'ReleaseDates' table.

---------------------------------------------
<%
Dim releasesRS
Dim releasesRS_numRows

Set releasesRS = Server.CreateObject("ADODB.Recordset")
releasesRS.ActiveConnection = MM_releases_STRING
releasesRS.Source = "SELECT Releases.*, Resource.Resource FROM Releases LEFT JOIN Resource ON Releases.Resource = Resource.resourceID ORDER BY Application ASC"
releasesRS.CursorType = 0
releasesRS.CursorLocation = 2
releasesRS.LockType = 1
releasesRS.Open()

releasesRS_numRows = 0
%>
---------------------------------------------

Any help on this would be much appreciated, guys.
If you have any ideas, I will try them.

Thanks in advance!
George

henslecd · Mar 29, 2005

Wouldn't a WHERE clause at the end of your sql statement work? WHERE releasedate = '3/21'

If it would, try this to make it flexible.

sDate = "event that changes the possible release date"

Then change the where clause to

WHERE releasedate = '" & sDate & "'

slakker · Mar 29, 2005

henslecd, thanks.. that sounds good.
I added the WHERE statement to my record set, but i'm a beginner and not sure what has to go into the actual sData variable..

sDate = "event that changes the possible release date"

This is the dropdown form I use that pulls release dates from a table, i'm trying to make this form filter the page results:

------------------------------------------
<select name="frmRlsDates" class="dropdowntable" id="frmRlsDates">
<%
While (NOT rlsDatesRS.EOF)
%>
<option value="<%=(rlsDatesRS.Fields.Item("rlsDatesID").Value)%>"><%=(rlsDatesRS.Fields.Item("ReleaseDate").Value)%></option>
<%
rlsDatesRS.MoveNext()
Wend
If (rlsDatesRS.CursorType > 0) Then
rlsDatesRS.MoveFirst
Else
rlsDatesRS.Requery
End If
%>
</select>
------------------------------------------

Thanks again

bslintx · Mar 29, 2005

slakker,

like any other form value, asp needs its name value to get the data; therefore, if you are posting the data from your form use Request.Form and if using get use request.Querysting or Request as a shortcut:

Hope this helps...good luck

BSL

Code:

<html>
 <head>
 </head>
<body>

' db connection for access database change to sql if nec
<%
  Set objConn = Server.CreateObject("ADODB.Connection")
  Set objRS = Server.CreateObject("ADODB.Recordset")
  FilePath = Server.MapPath("your_database.mdb")
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=" & FilePath & ";"  

strSQL="SELECT Releases.*, Resource.Resource FROM Releases LEFT JOIN Resource ON Releases.Resource = Resource.resourceID  ORDER BY Application ASC"
objRS.Open strSQL, objConn

' made a bogus page to post to
%>

<form action="show_select_result.asp" method="post">
<select name="frmRlsDates" class="dropdowntable" id="frmRlsDates">

' make sure to error check in case use tries to submit this null option
<option value="null"></option>

<%
  If objRS.EOF Then
  ' no records are in database
   Else 
  ' get records in databse
   Do While Not objRS.EOF
%>
    <option value="<%=(objRS("rlsDatesID")%>"><%=(objRS("ReleaseDate"))%></option>

<%
  objRS.MoveNext
  Loop
  End If

  objRS.Close
%>
  </select>

<input type ="submit" name="show_query" value="Submit">
</form>

<% 
  ' using post method and submitting to itself for simplcilty

  ' assign variable to form value posted by user n selectbx
  frmRlsDates = Request.Form("frmRlsDates")
  show_query = Request.Form("show_query")

  ' If the selectbox has a value show results
  ' Make sure in database Release date field is set to date/time
  ' for Access database needs to have ## around the date NOT '' that would be a string and cause a type mismatch
  'Also set the correct date format in database
  'for example general date/short date...etc 

  If submit <> "" AND frmRlsDates <> "" Then
    strSQL="SELECT Releases.*, Resource.Resource  FROM Releases LEFT JOIN Resource ON Releases.Resource = Resource.resourceID  WHERE ReleaseDate=#" & frmRlsDates & "# ORDER BY Application ASC"

  objRS.Open strSQL, objConn

  If objRS.EOF Then 
     Response.Write " No Records Found"

  Else
  Do While Not objRS.EOF

  ' normally a table would be used here..i used <br>
  Response.Write objRS("ReleaseDate") & "<br>"

  objRS.MoveNext
  Loop
  objRS.Close
  End If

  SET objRS = Nothing
  objConn.Close
  SET objConn = Nothing
%>

</body>
</html>

henslecd · Mar 29, 2005

Try

sDate = request.form("frmRlsDates") on the filtered page where the sql statement kicks off.

By the way, I wouldn't name your dropdowns with prefix frm. I would use cbo for combo, or dd for dropdown. frm is used for the Form tag.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Help with Record Set filtering

slakker

MIS

henslecd

MIS

slakker

MIS

bslintx

Technical User

henslecd

MIS

Similar threads

Part and Inventory Search

Sponsor