PDA

View Full Version : Get Results from Dynamic List menu


garybrett
04-01-2009, 01:01 PM
Hi,
Struggling with this. MX2004 connected to SQL2005 using VBScript. Created a recordset and dynamic table to show data but I need my users to be able to filter down on some columns, like dates, company etc. I inserted a form - list/menu and can populate this list with the data from sql. Is it possible to then have the selection from this list filter the data on the page, have tried loads of methods with no joy. I can do this by text box or even hyperlinking the relevant field but not really useful in this instance..

Thanx

davidj
04-01-2009, 01:56 PM
sorry mate but you need to learn the language. Using dreamweaver to write your code is like dancing blindfolded and naked around a bear trap

In my time on this forum [check my register date] i have never seen any solution provided by a member regarding dreamweaver code. Maybe someone will pop by and sort this out for you. I hope so

After this project please consider taking the path to enlightenment by learning to code in which ever language. You will never look back.

{{{subliminal message: PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP PHP}}}

garybrett
04-01-2009, 02:16 PM
Hmm,
I thought that was what Dreamweaver was for? If only I had time learn code that would be great but I am a System Admin trying to help my company launch a website in 3 weeks and credit crunch says no to hiring developers...

Oh well..

mangofreak
04-01-2009, 03:31 PM
You can filter the information by date, name or whatever you want.

The idea is to pass a value from your display page, say ByDate either by a link or pull down menu with then in your sql query done on DW in advanced mode you will see something like:

SELECT *
FROM tblyourtable
WHERE Date = Param
ORDER BY ASC

There you have to establish that you want to see your records ByDate.

I am in a rush but here is a link that may help you understand it fairly quickly:

http://www.webthang.co.uk/goto/tutorials/000173/?cat=2&tut=173

Good luck.

garybrett
04-01-2009, 04:39 PM
Hi,
Thanks for your help it is appreciated a lot. I have read the link and indeed I have this working with text fields or text links, but I simply cant get it going with a dynamic menu/list taken from sql table. I select a value from menu and it does nothing.. So far I have created a 3 pages, 1 search page with all data present, 1 results page for userID and another results page for provider, seesm long winded but its the only way I can get to work. It seems I need to creat a results page for every column I filter on..

Code below...please dont laugh..


<%
Dim rsCRID
Dim rsCRID_numRows
Set rsCRID = Server.CreateObject("ADODB.Recordset")
rsCRID.ActiveConnection = MM_GHLNS_STRING
rsCRID.Source = "SELECT CRID, Provider, OWPaidDate, CommissionPaid FROM dbo.TblCommissionPayment"
rsCRID.CursorType = 0
rsCRID.CursorLocation = 2
rsCRID.LockType = 1
rsCRID.Open()
rsCRID_numRows = 0
%>
<%
Dim rsProvider
Dim rsProvider_numRows
Set rsProvider = Server.CreateObject("ADODB.Recordset")
rsProvider.ActiveConnection = MM_GHLNS_STRING
rsProvider.Source = "SELECT CRID, Provider, OWPaidDate, CommissionPaid FROM dbo.TblCommissionPayment"
rsProvider.CursorType = 0
rsProvider.CursorLocation = 2
rsProvider.LockType = 1
rsProvider.Open()
rsProvider_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
rsCRID_numRows = rsCRID_numRows + Repeat1__numRows
%>
<%
Dim Repeat2__numRows
Dim Repeat2__index
Repeat2__numRows = -1
Repeat2__index = 0
rsProvider_numRows = rsProvider_numRows + Repeat2__numRows
%>
<%
Dim MM_paramName
%>
<%
' *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters
Dim MM_keepNone
Dim MM_keepURL
Dim MM_keepForm
Dim MM_keepBoth
Dim MM_removeList
Dim MM_item
Dim MM_nextItem
' create the list of parameters which should not be maintained
MM_removeList = "&index="
If (MM_paramName <> "") Then
MM_removeList = MM_removeList & "&" & MM_paramName & "="
End If
MM_keepURL=""
MM_keepForm=""
MM_keepBoth=""
MM_keepNone=""
' add the URL parameters to the MM_keepURL string
For Each MM_item In Request.QueryString
MM_nextItem = "&" & MM_item & "="
If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then
MM_keepURL = MM_keepURL & MM_nextItem & Server.URLencode(Request.QueryString(MM_item))
End If
Next
' add the Form variables to the MM_keepForm string
For Each MM_item In Request.Form
MM_nextItem = "&" & MM_item & "="
If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then
MM_keepForm = MM_keepForm & MM_nextItem & Server.URLencode(Request.Form(MM_item))
End If
Next
' create the Form + URL string and remove the intial '&' from each of the strings
MM_keepBoth = MM_keepURL & MM_keepForm
If (MM_keepBoth <> "") Then
MM_keepBoth = Right(MM_keepBoth, Len(MM_keepBoth) - 1)
End If
If (MM_keepURL <> "") Then
MM_keepURL = Right(MM_keepURL, Len(MM_keepURL) - 1)
End If
If (MM_keepForm <> "") Then
MM_keepForm = Right(MM_keepForm, Len(MM_keepForm) - 1)
End If
' a utility function used for adding additional parameters to these strings
Function MM_joinChar(firstItem)
If (firstItem <> "") Then
MM_joinChar = "&"
Else
MM_joinChar = ""
End If
End Function
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
.style4 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10px; }
-->
</style>
</head>
<body>
<p>&nbsp;</p>
<table border="1" cellpadding="1" cellspacing="1">
<tr>
<td>CRID</td>
<td>Provider</td>
<td>OWPaidDate</td>
<td>&nbsp;</td>
<td>CommissionPaid</td>
</tr>
<% While ((Repeat1__numRows <> 0) AND (NOT rsCRID.EOF)) %>
<tr>
<td><A HREF="testfilterCRID1.asp?<%= Server.HTMLEncode(MM_keepURL) & MM_joinChar(MM_keepURL) & "CRID=" & rsCRID.Fields.Item("CRID").Value %>"><%=(rsCRID.Fields.Item("CRID").Value)%></A></td>
<td><A HREF="testfilterCRID2.asp?<%= Server.HTMLEncode(MM_keepURL) & MM_joinChar(MM_keepURL) & "Provider=" & rsCRID.Fields.Item("Provider").Value %>"><%=(rsCRID.Fields.Item("Provider").Value)%></A></td>
<td><%=(rsCRID.Fields.Item("OWPaidDate").Value)%></td>
<td>&nbsp;</td>
<td><%=(rsCRID.Fields.Item("CommissionPaid").Value)%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsCRID.MoveNext()
Wend
%>
</table>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
</body>
</html>
<%
rsCRID.Close()
Set rsCRID = Nothing
%>
<%
rsCRID.Close()
Set rsCRID = Nothing
%>

mangofreak
04-01-2009, 06:52 PM
Ok. on the page that you have your dynamic table, above that insert a form and a dynamic menu. assign an ID name to the menu, say byDate. That ID will be passed in your query. Lets do this by date.

Your menu will show various dates, perhaps years.

in your recordset you have to add:
SELECT CRID, Provider, OWPaidDate, CommissionPaid FROM dbo.TblCommissionPayment
WHERE OWPaidDate LIKE Param

And under your Parameter insertion you add the
name Param
Type will be DATE
the value willl be Request.QueryString("byDate")
The default value will be: I'm not sure maybe 0 or a date that you have in mind as default

Also remember that dates have various ways to handle as they require sometimes #Param# etc.

on your form you put on action the name of the same page and when it return the page it should have arranged it by date.

Hope it helps.

Javier

mangofreak
04-02-2009, 01:35 AM
WTF... what's up with that? Is that even allowed?

garybrett
04-02-2009, 01:36 PM
Hi Javier, thankyou for your patience, I must be so thick I just cant get it to work!!

On the search.asp page I have a recordset is created no drama. I then add a form with a list.menu in and give id of byDate as you suggest. I then create a new recordset name rsbyDate as follows:

SELECT CRID, Provider, OWPaidDate, CommissionPaid FROM dbo.TblCommissionPayment
WHERE OWPaidDate LIKE Param

Now, I add a variable in the advanced window as follows:
Name= PARAM, Default Value=0, Run-Time Value=Request.QueryString("byDate")

I then add search.asp to the action of the form with either POST or GET but when I test there is nothing filling the drop down box. I must be getting confused over the PARAM issue I think, Have I done something wrong?

Again sorry and thanks for helping.
Gary

mangofreak
04-02-2009, 04:39 PM
Your html menu element's id = "byDate" - That's clear, right

On the Recordset that you already have, the one that paints the dynamic table, using the advanced mode add the following:

WHERE OWPaidDate LIKE Param

Then

below goes:
Name= PARAM, Default Value=0, Run-Time Value=Request.QueryString("byDate")

The Action is the same page and for method use Get.

garybrett
04-02-2009, 07:09 PM
Hi there again, yes the form id is byDate. Can I add another where clause to the existing recordset then? I already have one doing another job that you helped me with earlier? I tried to add WHERE OWPaidDate LIKE Param
to the end with the variable but it errored saying problem with WHERE..

SELECT SLUsers.D2Custom1, SLUsers.D2ID, TblCommissionPayment.Provider, TblCommissionPayment.PaymentID, TblCommissionPayment.OWPaidDate, TblCommissionPayment.PolicyNumber, TblCommissionPayment.CommissionPaid, TblCommissionPayment.CRPayment, SLUsers.D2Username
FROM TblCommissionPayment INNER JOIN SLUsers ON TblCommissionPayment.CRID COLLATE SQL_Latin1_General_CP1_CI_AS = SLUsers.D2Custom1
WHERE SLUsers.D2ID = Param

I created a new recordset with same query but with new param

SELECT SLUsers.D2Custom1, SLUsers.D2ID, TblCommissionPayment.Provider, TblCommissionPayment.PaymentID, TblCommissionPayment.OWPaidDate, TblCommissionPayment.PolicyNumber, TblCommissionPayment.CommissionPaid, TblCommissionPayment.CRPayment, SLUsers.D2Username
FROM TblCommissionPayment INNER JOIN SLUsers ON TblCommissionPayment.CRID COLLATE SQL_Latin1_General_CP1_CI_AS = SLUsers.D2Custom1
WHERE OWPaidDate LIKE byDate

This doesnt return any dates into drop down field when viewing in browser?

Arggggh Im so thick..

garybrett
04-02-2009, 07:11 PM
If it helps hers the code for the form?

<form name="byDate" id="byDate" method="get" action="filcrcommissions.asp">
<select name="select">
<%
While (NOT rsFilterDate.EOF)
%>
<option value="<%=(rsFilterDate.Fields.Item("OWPaidDate").Value)%>"><%=(rsFilterDate.Fields.Item("OWPaidDate").Value)%></option>
<%
rsFilterDate.MoveNext()
Wend
If (rsFilterDate.CursorType > 0) Then
rsFilterDate.MoveFirst
Else
rsFilterDate.Requery
End If
%>
</select>
</form>

And part of recordset in code:
<%
Dim rsFilterDate__Param
rsFilterDate__Param = "0"
If (Request.QueryString("byDate") <> "") Then
rsFilterDate__Param = Request.QueryString("byDate")
End If
%>
<%
Dim rsFilterDate
Dim rsFilterDate_numRows
Set rsFilterDate = Server.CreateObject("ADODB.Recordset")
rsFilterDate.ActiveConnection = MM_GHLNS_STRING
rsFilterDate.Source = "SELECT SLUsers.D2Custom1, SLUsers.D2ID, TblCommissionPayment.Provider, TblCommissionPayment.PaymentID, TblCommissionPayment.OWPaidDate, TblCommissionPayment.PolicyNumber, TblCommissionPayment.CommissionPaid, TblCommissionPayment.CRPayment, SLUsers.D2Username FROM TblCommissionPayment INNER JOIN SLUsers ON TblCommissionPayment.CRID COLLATE SQL_Latin1_General_CP1_CI_AS = SLUsers.D2Custom1 WHERE OWPaidDate LIKE " + Replace(rsFilterDate__Param, "'", "''") + ""