We help IT Professionals succeed at work.

Column name as variable in radio button search

LZ1
LZ1 asked
on
239 Views
Last Modified: 2010-03-20
Hey Experts!!

 What I have is basically 3 radio buttons choosing which column in the DB I want to search and two date fields searching between them.
What I want to know is how do I pass the radio button value to the results page so that it returns the values of that selected column?
EX) EXP is the column name and the query searches the column between the two dates(date 1 & date2) provided.  
Problem is that I am not getting any results on my query at all.  Either in Access or on the server.  

The code below is my search function and my recordset on one results page. The code is passing and appending to the URL for the AED_Master."exp", however I am still not receiving results in the recordset.  Now this same query does work as long as I have a different WHERE clause (i.e. searching simple text or numerics with the column name embedded in the query)

Someone please help!!


**SQL/Query**
SELECT DISTINCT (AED_Master.ACT_no) AS Expr1, Client.ACT_no, * 
FROM ((ACT_Master RIGHT JOIN Client ON ACT_Master.ACT_No=Client.ACT_no) 
INNER JOIN AED_master ON Client.ACT_no=AED_master.ACT_No) 
INNER JOIN Models ON AED_master.Model_ID=Models.Model_ID 
WHERE exp > date1 AND exp < date2
AND Client.ACT_no = AED_Master.ACT_no;
 
**Results Page**
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%session.timeout=540 'time in minutes %>
<%
' *** Restrict Access To Page: Grant or deny access to this page
MM_authorizedUsers="1"
MM_authFailedURL="/login_failed.asp"
MM_grantAccess=false
If Session("MM_Username") <> "" Then
  If (true Or CStr(Session("MM_UserAuthorization"))="") Or _
         (InStr(1,MM_authorizedUsers,Session("MM_UserAuthorization"))>=1) Then
    MM_grantAccess = true
  End If
End If
If Not MM_grantAccess Then
  MM_qsChar = "?"
  If (InStr(1,MM_authFailedURL,"?") >= 1) Then MM_qsChar = "&"
  MM_referrer = Request.ServerVariables("URL")
  if (Len(Request.QueryString()) > 0) Then MM_referrer = MM_referrer & "?" & Request.QueryString()
  MM_authFailedURL = MM_authFailedURL & MM_qsChar & "accessdenied=" & Server.URLEncode(MM_referrer)
  Response.Redirect(MM_authFailedURL)
End If
%>
<!--#include virtual="/Connections/aed_1.asp" -->
<%
Dim rsUser__MMColParam
rsUser__MMColParam = "1"
If (Session("MM_username") <> "") Then 
  rsUser__MMColParam = Session("MM_username")
End If
%>
<%
Dim rsUser
Dim rsUser_cmd
Dim rsUser_numRows
 
Set rsUser_cmd = Server.CreateObject ("ADODB.Command")
rsUser_cmd.ActiveConnection = MM_aed_1_STRING
rsUser_cmd.CommandText = "SELECT * FROM login WHERE user_name = ?" 
rsUser_cmd.Prepared = true
rsUser_cmd.Parameters.Append rsUser_cmd.CreateParameter("param1", 200, 1, 255, rsUser__MMColParam) ' adVarChar
 
Set rsUser = rsUser_cmd.Execute
rsUser_numRows = 0
%>
<%
Dim rs_contact__exp
rs_contact__exp = "1"
If (Request.QueryString("exp")   <> "") Then 
  rs_contact__exp = Request.QueryString("exp")  
End If
%>
<%
Dim rs_contact__date1
rs_contact__date1 = "1"
If (Request.QueryString("date1")     <> "") Then 
  rs_contact__date1 = Request.QueryString("date1")    
End If
%>
<%
Dim rs_contact__date2
rs_contact__date2 = "1"
If (Request.QueryString("date2") <> "") Then 
  rs_contact__date2 = Request.QueryString("date2")
End If
%>
 
<%
Dim rs_contact
Dim rs_contact_cmd
Dim rs_contact_numRows
 
Set rs_contact_cmd = Server.CreateObject ("ADODB.Command")
rs_contact_cmd.ActiveConnection = MM_aed_1_STRING
rs_contact_cmd.CommandText = "SELECT DISTINCT (AED_Master.ACT_no) AS Expr1, Client.ACT_no, * FROM ((ACT_Master RIGHT JOIN Client ON ACT_Master.ACT_No=Client.ACT_no)  INNER JOIN AED_master ON Client.ACT_no=AED_master.ACT_No)  INNER JOIN Models ON AED_master.Model_ID=Models.Model_ID
WHERE exp> date1 AND exp < date2  AND Client.ACT_no = AED_Master.ACT_no;" 
rs_contact_cmd.Prepared = true
rs_contact_cmd.Parameters.Append rs_contact_cmd.CreateParameter("param1", 200, 1, 255, rs_contact__exp) ' adVarChar
rs_contact_cmd.Parameters.Append rs_contact_cmd.CreateParameter("param2", 135, 1, -1, rs_contact__date1) ' adDBTimeStamp
rs_contact_cmd.Parameters.Append rs_contact_cmd.CreateParameter("param3", 135, 1, -1, rs_contact__date2) ' adDBTimeStamp
 
Set rs_contact = rs_contact_cmd.Execute
rs_contact_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
 
Repeat1__numRows = 10
Repeat1__index = 0
rs_contact_numRows = rs_contact_numRows + Repeat1__numRows
%>
<%
Dim MM_paramName 
%>
<%
'  *** Recordset Stats, Move To Record, and Go To Record: declare stats variables
 
Dim rs_contact_total
Dim rs_contact_first
Dim rs_contact_last
 
' set the record count
rs_contact_total = rs_contact.RecordCount
 
' set the number of rows displayed on this page
If (rs_contact_numRows < 0) Then
  rs_contact_numRows = rs_contact_total
Elseif (rs_contact_numRows = 0) Then
  rs_contact_numRows = 1
End If
 
' set the first and last displayed record
rs_contact_first = 1
rs_contact_last  = rs_contact_first + rs_contact_numRows - 1
 
' if we have the correct record count, check the other stats
If (rs_contact_total <> -1) Then
  If (rs_contact_first > rs_contact_total) Then
    rs_contact_first = rs_contact_total
  End If
  If (rs_contact_last > rs_contact_total) Then
    rs_contact_last = rs_contact_total
  End If
  If (rs_contact_numRows > rs_contact_total) Then
    rs_contact_numRows = rs_contact_total
  End If
End If
%>
<%
' *** Recordset Stats: if we don't know the record count, manually count them
 
If (rs_contact_total = -1) Then
 
  ' count the total records by iterating through the recordset
  rs_contact_total=0
  While (Not rs_contact.EOF)
    rs_contact_total = rs_contact_total + 1
    rs_contact.MoveNext
  Wend
 
  ' reset the cursor to the beginning
  If (rs_contact.CursorType > 0) Then
    rs_contact.MoveFirst
  Else
    rs_contact.Requery
  End If
 
  ' set the number of rows displayed on this page
  If (rs_contact_numRows < 0 Or rs_contact_numRows > rs_contact_total) Then
    rs_contact_numRows = rs_contact_total
  End If
 
  ' set the first and last displayed record
  rs_contact_first = 1
  rs_contact_last = rs_contact_first + rs_contact_numRows - 1
  
  If (rs_contact_first > rs_contact_total) Then
    rs_contact_first = rs_contact_total
  End If
  If (rs_contact_last > rs_contact_total) Then
    rs_contact_last = rs_contact_total
  End If
 
End If
%>
<%
' *** Move To Record and Go To Record: declare variables
 
Dim MM_rs
Dim MM_rsCount
Dim MM_size
Dim MM_uniqueCol
Dim MM_offset
Dim MM_atTotal
Dim MM_paramIsDefined
 
Dim MM_param
Dim MM_index
 
Set MM_rs    = rs_contact
MM_rsCount   = rs_contact_total
MM_size      = rs_contact_numRows
MM_uniqueCol = ""
MM_paramName = ""
MM_offset = 0
MM_atTotal = false
MM_paramIsDefined = false
If (MM_paramName <> "") Then
  MM_paramIsDefined = (Request.QueryString(MM_paramName) <> "")
End If
%>
<%
' *** Move To Record: handle 'index' or 'offset' parameter
 
if (Not MM_paramIsDefined And MM_rsCount <> 0) then
 
  ' use index parameter if defined, otherwise use offset parameter
  MM_param = Request.QueryString("index")
  If (MM_param = "") Then
    MM_param = Request.QueryString("offset")
  End If
  If (MM_param <> "") Then
    MM_offset = Int(MM_param)
  End If
 
  ' if we have a record count, check if we are past the end of the recordset
  If (MM_rsCount <> -1) Then
    If (MM_offset >= MM_rsCount Or MM_offset = -1) Then  ' past end or move last
      If ((MM_rsCount Mod MM_size) > 0) Then         ' last page not a full repeat region
        MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
      Else
        MM_offset = MM_rsCount - MM_size
      End If
    End If
  End If
 
  ' move the cursor to the selected record
  MM_index = 0
  While ((Not MM_rs.EOF) And (MM_index < MM_offset Or MM_offset = -1))
    MM_rs.MoveNext
    MM_index = MM_index + 1
  Wend
  If (MM_rs.EOF) Then 
    MM_offset = MM_index  ' set MM_offset to the last possible record
  End If
 
End If
%>
<%
' *** Move To Record: if we dont know the record count, check the display range
 
If (MM_rsCount = -1) Then
 
  ' walk to the end of the display range for this page
  MM_index = MM_offset
  While (Not MM_rs.EOF And (MM_size < 0 Or MM_index < MM_offset + MM_size))
    MM_rs.MoveNext
    MM_index = MM_index + 1
  Wend
 
  ' if we walked off the end of the recordset, set MM_rsCount and MM_size
  If (MM_rs.EOF) Then
    MM_rsCount = MM_index
    If (MM_size < 0 Or MM_size > MM_rsCount) Then
      MM_size = MM_rsCount
    End If
  End If
 
  ' if we walked off the end, set the offset based on page size
  If (MM_rs.EOF And Not MM_paramIsDefined) Then
    If (MM_offset > MM_rsCount - MM_size Or MM_offset = -1) Then
      If ((MM_rsCount Mod MM_size) > 0) Then
        MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
      Else
        MM_offset = MM_rsCount - MM_size
      End If
    End If
  End If
 
  ' reset the cursor to the beginning
  If (MM_rs.CursorType > 0) Then
    MM_rs.MoveFirst
  Else
    MM_rs.Requery
  End If
 
  ' move the cursor to the selected record
  MM_index = 0
  While (Not MM_rs.EOF And MM_index < MM_offset)
    MM_rs.MoveNext
    MM_index = MM_index + 1
  Wend
End If
%>
<%
' *** Move To Record: update recordset stats
 
' set the first and last displayed record
rs_contact_first = MM_offset + 1
rs_contact_last  = MM_offset + MM_size
 
If (MM_rsCount <> -1) Then
  If (rs_contact_first > MM_rsCount) Then
    rs_contact_first = MM_rsCount
  End If
  If (rs_contact_last > MM_rsCount) Then
    rs_contact_last = MM_rsCount
  End If
End If
 
' set the boolean used by hide region to check if we are on the last record
MM_atTotal = (MM_rsCount <> -1 And MM_offset + MM_size >= MM_rsCount)
%>
<%
' *** 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
%>
<%
' *** Move To Record: set the strings for the first, last, next, and previous links
 
Dim MM_keepMove
Dim MM_moveParam
Dim MM_moveFirst
Dim MM_moveLast
Dim MM_moveNext
Dim MM_movePrev
 
Dim MM_urlStr
Dim MM_paramList
Dim MM_paramIndex
Dim MM_nextParam
 
MM_keepMove = MM_keepBoth
MM_moveParam = "index"
 
' if the page has a repeated region, remove 'offset' from the maintained parameters
If (MM_size > 1) Then
  MM_moveParam = "offset"
  If (MM_keepMove <> "") Then
    MM_paramList = Split(MM_keepMove, "&")
    MM_keepMove = ""
    For MM_paramIndex = 0 To UBound(MM_paramList)
      MM_nextParam = Left(MM_paramList(MM_paramIndex), InStr(MM_paramList(MM_paramIndex),"=") - 1)
      If (StrComp(MM_nextParam,MM_moveParam,1) <> 0) Then
        MM_keepMove = MM_keepMove & "&" & MM_paramList(MM_paramIndex)
      End If
    Next
    If (MM_keepMove <> "") Then
      MM_keepMove = Right(MM_keepMove, Len(MM_keepMove) - 1)
    End If
  End If
End If
 
' set the strings for the move to links
If (MM_keepMove <> "") Then 
  MM_keepMove = Server.HTMLEncode(MM_keepMove) & "&"
End If
 
MM_urlStr = Request.ServerVariables("URL") & "?" & MM_keepMove & MM_moveParam & "="
 
MM_moveFirst = MM_urlStr & "0"
MM_moveLast  = MM_urlStr & "-1"
MM_moveNext  = MM_urlStr & CStr(MM_offset + MM_size)
If (MM_offset - MM_size < 0) Then
  MM_movePrev = MM_urlStr & "0"
Else
  MM_movePrev = MM_urlStr & CStr(MM_offset - MM_size)
End If
%>
<%session.timeout=540 'time in minutes %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Search Results - HeartAED Intranet</title>
<script type="text/javascript">
<!--
function MM_preloadImages() { //v3.0
  var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
    var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
    if (a[i].indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a[i];}}
}
//-->
</script>
<link href="/CSS/main.css" rel="stylesheet" type="text/css">
<script src="Scripts/AC_RunActiveContent.js" type="text/javascript"></script>
 
<script src="includes/datetimepicker.js" type="text/javascript"></script>
<style type="text/css">
<!--
#apDiv4 {
	position:absolute;
	width:auto;
	height:auto;
	z-index:1;
	left: 183px;
	top: 200px;
}
#apDiv3 {
	position:absolute;
	width:595px;
	height:251px;
	z-index:1;
	left: 346px;
	top: auto;
	overflow: scroll;
}
#apDiv5 {
	position:absolute;
	width:200px;
	height:115px;
	z-index:12;
	left: 706px;
	top: 70px;
}
-->
</style>
</head>
 
<body>
<!--#include file="includes/header.asp" -->
<!--#include file="includes/search_div.asp" -->
<!--#include file="includes/div_menu.asp" -->
<div id="apDiv4">
<img src="Images/backgrounds/search_results_date.jpg">
  <span class="results_total"><%=(rs_contact_total)%> Found</span>
  <% If rs_contact.EOF And rs_contact.BOF Then %>
<p class="body_text">There is nothing matching  what you typed.</p>
    <% End If ' end rs_contact.EOF And rs_contact.BOF %>
    <% If Not rs_contact.EOF Or Not rs_contact.BOF Then %>
<table border="0">
      <tr>
        <td><% If MM_offset <> 0 Then %>
            <a href="<%=MM_moveFirst%>"><img src="First.gif" border=0></a>
        <% End If ' end MM_offset <> 0 %></td>
      <td><% If MM_offset <> 0 Then %>
              <a href="<%=MM_movePrev%>"><img src="Previous.gif" border=0></a>
        <% End If ' end MM_offset <> 0 %>        </td>
        <td>
		</td>
      <td><% If Not MM_atTotal Then %>
              <a href="<%=MM_moveLast%>"><img src="Last.gif" border=0></a>
        <% End If ' end Not MM_atTotal %>        </td>
      </tr>
    </table>
<% 
While ((Repeat1__numRows <> 0) AND (NOT rs_contact.EOF)) 
%>
<table width="500" height="127" border="0">
        <tr>
  <td width="98" rowspan="4"><p align="center"><img src="<%=(rs_contact.Fields.Item("model_img").Value)%>" alt="" width="50" height="50"></p>
            <p align="center" class="results_serial"><%=(rs_contact.Fields.Item("Serial_Code").Value)%></p>
          <p align="center"><A HREF="aed_edit.asp?<%= Server.HTMLEncode(MM_keepNone) & MM_joinChar(MM_keepNone) & "AED_No=" & rs_contact.Fields.Item("AED_No").Value %>"><img src="Images/buttons/micro_edit.jpg" width="43" height="15" border="0"></A></p></td>
          <td width="9" rowspan="4" bgcolor="#CC0033">&nbsp;</td>
          <td width="211" rowspan="3"><p><%=(rs_contact.Fields.Item("Client.Client").Value)%><br>
              <%=(rs_contact.Fields.Item("Client.Address").Value)%><br>
              <%=(rs_contact.Fields.Item("Client.Address2").Value)%><br>
          <%=(rs_contact.Fields.Item("Client.City").Value)%>&nbsp; <%=(rs_contact.Fields.Item("Client.State").Value)%>, <%=(rs_contact.Fields.Item("Client.Zipplus").Value)%></p>
          <p><A HREF="aed_add.asp?<%= Server.HTMLEncode(MM_keepNone) & MM_joinChar(MM_keepNone) & "Client.ACT_No=" & rs_contact.Fields.Item("Client.ACT_No").Value %>"><img src="Images/buttons/micro_add.jpg" width="43" height="15" border="0"></A> <A HREF="client_edit.asp?<%= Server.HTMLEncode(MM_keepNone) & MM_joinChar(MM_keepNone) & "Client.ACT_No=" & rs_contact.Fields.Item("Client.ACT_No").Value %>"><img src="Images/buttons/micro_edit.jpg" alt="" width="43" height="15" border="0"></A>&nbsp;</p></td>
          <td width="69"><div align="right">Adult Pads</div></td>
          <td width="91"><%
' Show IF Conditional region1 
  If (rs_contact.Fields.Item("Adult_Pads").Value) > date() Then
%>
              <%=(rs_contact.Fields.Item("Adult_Pads").Value)%>
              <%
' else Conditional region1
  Else %>
              <span class="expired"><%=(rs_contact.Fields.Item("Adult_Pads").Value)%></span>
  <%
  End If
' End Conditional region1
%></td>
        </tr>
        <tr>
          <td><div align="right">Battery</div></td>
          <td><%
' Show IF Conditional region2 
  If (rs_contact.Fields.Item("Battery").Value) > date() Then
%>
              <%=(rs_contact.Fields.Item("Battery").Value)%>
              <%
' else Conditional region2
  Else %>
              <span class="expired"><%=(rs_contact.Fields.Item("Battery").Value)%></span>
  <%
  End If
' End Conditional region2
%></td>
        </tr>
        <tr>
          <td height="23"><div align="right">Ped Pads</div></td>
          <td height="23"><%
' Show IF Conditional region3 
  If (rs_contact.Fields.Item("Ped_Pads").Value) > date() Then
%>
              <%=(rs_contact.Fields.Item("Ped_Pads").Value)%>
              <%
' else Conditional region3
  Else %>
              <span class="expired"><%=(rs_contact.Fields.Item("Ped_Pads").Value)%></span>
  <%
  End If
' End Conditional region3
%></td>
        </tr>
        <tr>
          <td colspan="3"><hr align="center" width="90%" class="head_body_contact"></td>
        </tr>
      </table>
      <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rs_contact.MoveNext()
Wend
%>
    <% End If ' end Not rs_contact.EOF Or NOT rs_contact.BOF %>
  <table border="0">
      <tr>
        <td><% If MM_offset <> 0 Then %>
              <a href="<%=MM_moveFirst%>"><img src="First.gif" border=0></a>
        <% End If ' end MM_offset <> 0 %>        </td>
        <td><% If MM_offset <> 0 Then %>
              <a href="<%=MM_movePrev%>"><img src="Previous.gif" border=0></a>
        <% End If ' end MM_offset <> 0 %>        </td>
        <td><% If Not MM_atTotal Then %>
              <a href="<%=MM_moveNext%>"><img src="Next.gif" width="14" height="13" border=0></a>
        <% End If ' end Not MM_atTotal %>        </td>
        <td><% If Not MM_atTotal Then %>
              <a href="<%=MM_moveLast%>"><img src="Last.gif" border=0></a>
        <% End If ' end Not MM_atTotal %>        </td>
      </tr>
    </table>
</div>
</body>
</html>
<%
rsUser.Close()
Set rsUser = Nothing
%>
<%
rs_contact.Close()
Set rs_contact = Nothing
%>

Open in new window

Comment
Watch Question

Do you need to wrap single quotes around your date variables when building your SQL statement?  
LZ1
Top Expert 2011

Author

Commented:
I tried that and it is still not yielding any results.  I am so confused as to why this is not working.  Could it be that my javascript on my search form is messing up the ASP side of things??  I do have an older page that searches between the two dates fine with no trouble.  This older page however does have the column name right in the SQL.  
WHERE AED_Master.Adult_Pads >= date1 AND AED_Master.Adult_Pads <= date2

Open in new window

Top Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.