Assigning recordset values to variables in case select statements in vbscript

When assigning a recordset field value to a variable to not enclose in quotes.  Code below should work as long as the recordsets are populated prior to the selec case routine.

Dim MM_query
Dim MM_reviewLink
Dim MM_linkID
Dim MM_reportLink
Dim MM_linkDate
Dim page
 
page = "dreams"
 
SELECT CASE page
		CASE "dreams"
			MM_query = rsDreams("memberID")
			MM_reviewLink = "dreamsReview.asp"
			MM_linkID = rsDreams("dreamID")
			MM_reportLink = "empowerment.asp"
			MM_linkDate = rsDreams("dreamDate")
            
		CASE "reality"
			MM_query = rsReality("userID")
			MM_pagelink = "realityReview.asp"
			MM_linkID = rsReality("transID")
			MM_reportLink = "empowerment.asp"
			MM_linkDate = rsReality("transDate")
	
		CASE "budget"
			MM_query = rsBudget("userID")
			MM_pagelink = "budgetReview.asp"
			MM_linkID = rsBudget("transID")
			MM_reportLink = "empowerment.asp"
			MM_linkDate = rsBudget("transDate")
			
		CASE "flexible"
			MM_query = rsFlexible("userID")
			MM_pagelink = "flexibleReview.asp"
			MM_linkID = rsFlexible("transID")
			MM_reportLink = "empowerment.asp"
            MM_linkDate = rsFlexible("transDate")
	
		CASE "fixed"
			MM_query = rsFixed("userID")
			MM_pagelink = "fixedReview.asp"
			MM_linkID = rsFixed("transID")
			MM_reportLink = "empowerment.asp"
			MM_linkDate = rsFlexible("transDate")
			
		CASE ELSE
			response.redirect("error_page.asp")
			
	END SELECT
 
'Here is the code in the form to populate the table with record links
 <tr>
                <td><%=MM_query%></td>
                <td><a href="<%=MM_reviewLink%>"><%=MM_linkID %></td>
                <td><a href="<%=MM_reportLink%>"><%=MM_linkDate %></td>
            
              </tr>

Select allOpen in new window

Thanks. I moved the code from the top to the bottom of the code section, removed the appropriate quotes and I got this error.

ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/gmmcPub/CheckExisting.asp, line 152

Line 152 is
SELECT CASE page
151            CASE "dreams"
152            MM_query = rsDreams("memberID")

As I stated this will only work if your recordsets are populated.  Where are you populating these recordsets?  No evidence in code provided.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>

<!--#include file="Connections/connGMMCdata.asp" -->



<%Dim myDateString
myDateString = Date()%>
<%
' *** Restrict Access To Page: Grant or deny access to this page
MM_authorizedUsers="1,2,3,4"
MM_authFailedURL="login.asp?LoginType=FailedLogin"
MM_grantAccess=false
If Session("MM_Username") <> "" Then
  If (false 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

Dim rsDreams__MMColParam
rsDreams__MMColParam = "1"
If (Session("MM_userID") <> "") Then
  rsDreams__MMColParam = Session("MM_userID")
End If
%>
<%
Dim rsDreams
Dim rsDreams_cmd
Dim rsDreams_numRows

Set rsDreams_cmd = Server.CreateObject ("ADODB.Command")
rsDreams_cmd.ActiveConnection = MM_connGMMCdata_STRING
rsDreams_cmd.CommandText = "SELECT dreamID, memberID, dreamDate FROM dbo.dreams WHERE memberID = ?"
rsDreams_cmd.Prepared = true
rsDreams_cmd.Parameters.Append rsDreams_cmd.CreateParameter("param1", 5, 1, -1, rsDreams__MMColParam) ' adDouble

Set rsDreams = rsDreams_cmd.Execute
rsDreams_numRows = 0
%>
<%
Dim rsReality__MMColParam
rsReality__MMColParam = "1"
If (Session("MM_userID") <> "") Then
  rsReality__MMColParam = Session("MM_userID")
End If
%>
<%
Dim rsReality
Dim rsReality_cmd
Dim rsReality_numRows

Set rsReality_cmd = Server.CreateObject ("ADODB.Command")
rsReality_cmd.ActiveConnection = MM_connGMMCdata_STRING
rsReality_cmd.CommandText = "SELECT transID, userID, transDate FROM dbo.finAnalysisNetWorth WHERE userID = ?"
rsReality_cmd.Prepared = true
rsReality_cmd.Parameters.Append rsReality_cmd.CreateParameter("param1", 5, 1, -1, rsReality__MMColParam) ' adDouble

Set rsReality = rsReality_cmd.Execute
rsReality_numRows = 0
%>
<%
Dim rsBudget__MMColParam
rsBudget__MMColParam = "1"
If (Session("MM_userID") <> "") Then
  rsBudget__MMColParam = Session("MM_userID")
End If
%>
<%
Dim rsBudget
Dim rsBudget_cmd
Dim rsBudget_numRows

Set rsBudget_cmd = Server.CreateObject ("ADODB.Command")
rsBudget_cmd.ActiveConnection = MM_connGMMCdata_STRING
rsBudget_cmd.CommandText = "SELECT transID, userID, transDate FROM dbo.finAnalysisFixedExpenses WHERE userID = ?"
rsBudget_cmd.Prepared = true
rsBudget_cmd.Parameters.Append rsBudget_cmd.CreateParameter("param1", 5, 1, -1, rsBudget__MMColParam) ' adDouble

Set rsBudget = rsBudget_cmd.Execute
rsBudget_numRows = 0
%>
<%
Dim rsFlexible__MMColParam
rsFlexible__MMColParam = "1"
If (Session("MM_userID") <> "") Then
  rsFlexible__MMColParam = Session("MM_userID")
End If
%>
<%
Dim rsFlexible
Dim rsFlexible_cmd
Dim rsFlexible_numRows

Set rsFlexible_cmd = Server.CreateObject ("ADODB.Command")
rsFlexible_cmd.ActiveConnection = MM_connGMMCdata_STRING
rsFlexible_cmd.CommandText = "SELECT transID, userID, transDate FROM dbo.finAnalysisFlexibleExpenses WHERE userID = ?"
rsFlexible_cmd.Prepared = true
rsFlexible_cmd.Parameters.Append rsFlexible_cmd.CreateParameter("param1", 5, 1, -1, rsFlexible__MMColParam) ' adDouble

Set rsFlexible = rsFlexible_cmd.Execute
rsFlexible_numRows = 0
%>
<%
Dim rsFixed__MMColParam
rsFixed__MMColParam = "1"
If (Session("MM_userID") <> "") Then
  rsFixed__MMColParam = Session("MM_userID")
End If
%>
<%
Dim rsFixed
Dim rsFixed_cmd
Dim rsFixed_numRows

Set rsFixed_cmd = Server.CreateObject ("ADODB.Command")
rsFixed_cmd.ActiveConnection = MM_connGMMCdata_STRING
rsFixed_cmd.CommandText = "SELECT transID, userID, transDate FROM dbo.finAnalysisFixedExpenses WHERE userID = ?"
rsFixed_cmd.Prepared = true
rsFixed_cmd.Parameters.Append rsFixed_cmd.CreateParameter("param1", 5, 1, -1, rsFixed__MMColParam) ' adDouble

Set rsFixed = rsFixed_cmd.Execute
rsFixed_numRows = 0
%>
<%
'Dim RedirectWhere
'RedirectWhere = "dream.asp"
'If rs_ExistingAnalysis_numRows = 0 then
'      response.redirect(RedirectWhere)
'End If
%>
<%
Dim MM_query
Dim MM_reviewLink
Dim MM_linkID
Dim MM_reportLink
Dim MM_linkDate
Dim page

page = "dreams"

SELECT CASE page
            CASE "dreams"
                  MM_query = rsDreams("memberID")
                  MM_reviewLink = "dreamsReview.asp"
                  MM_linkID = rsDreams("dreamID")
                  MM_reportLink = "empowerment.asp"
                  MM_linkDate = rsDreams("dreamDate")
           
            CASE "reality"
                  MM_query = rsReality("userID")
                  MM_pagelink = "realityReview.asp"
                  MM_linkID = rsReality("transID")
                  MM_reportLink = "empowerment.asp"
                  MM_linkDate = rsReality("transDate")
      
            CASE "budget"
                  MM_query = rsBudget("userID")
                  MM_pagelink = "budgetReview.asp"
                  MM_linkID = rsBudget("transID")
                  MM_reportLink = "empowerment.asp"
                  MM_linkDate = rsBudget("transDate")
                  
            CASE "flexible"
                  MM_query = rsFlexible("userID")
                  MM_pagelink = "flexibleReview.asp"
                  MM_linkID = rsFlexible("transID")
                  MM_reportLink = "empowerment.asp"
            MM_linkDate = rsFlexible("transDate")
      
            CASE "fixed"
                  MM_query = rsFixed("userID")
                  MM_pagelink = "fixedReview.asp"
                  MM_linkID = rsFixed("transID")
                  MM_reportLink = "empowerment.asp"
                  MM_linkDate = rsFlexible("transDate")
                  
            CASE ELSE
                  response.redirect("error_page.asp")
                  
      END SELECT
%>      

ok - no errors. but no data was returned.

for some reason the variable is not being passed to the recordsets. let me check that.

Ok - looks like my login system is not working properly. I'm setting my session("MM_userID") variable in my login_processor.asp file. I've attached it and my login files for you to look at.

It is possible that what I am trying to accomplish can be accomplished in a more streamlined approach. I have been concerned about my error calls to login page not returning proper error messages.
login.txt
login-processor.txt