[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Assigning recordset values to variables in case select statements in vbscript

Posted on 2009-04-23
8
Medium Priority
?
325 Views
Last Modified: 2012-05-06
Hi experts, I'm using a case select statement to assign variables to be used to check records in various tables. I'm having trouble assigning recordset values to variables to be used in the form.
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>

Open in new window

0
Comment
Question by:GMMC_man
  • 5
  • 3
8 Comments
 
LVL 9

Expert Comment

by:dan_neal
ID: 24221358
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>

Open in new window

0
 

Author Comment

by:GMMC_man
ID: 24221419
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")
0
 
LVL 9

Expert Comment

by:dan_neal
ID: 24221429
As I stated this will only work if your recordsets are populated.  Where are you populating these recordsets?  No evidence in code provided.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:GMMC_man
ID: 24221445
<%@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
%>      
0
 
LVL 9

Accepted Solution

by:
dan_neal earned 2000 total points
ID: 24221513
So now that I see you are loading each recordset, question is, have you verified data is actually getting returned?
You will likely want to add a
if not rsDreams.eof then
...
end if
inside of each case to prevent the error.  Then you just need to work out why no data is coming in.
0
 

Author Comment

by:GMMC_man
ID: 24221547
ok - no errors. but no data was returned.
0
 

Author Comment

by:GMMC_man
ID: 24221552
for some reason the variable is not being passed to the recordsets. let me check that.
0
 

Author Comment

by:GMMC_man
ID: 24221604
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
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Integration Management Part 2
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question