[samajam]

Hi all!!

I would like to know how to reference an SQL table when building the criteria for IF statements in VBscript. I have the following code in an ASP page:

If Session("AgtType") = "3" Then
rsData.Open "Select AgentID, blah blah blah..."

As you can see the criteria is hard coded but I would like to change it so that it looks in an SQL table for the values. That way I can make changes to the SQL table without having to go back to all the ASP pages to update them.

I have a table called configuration with columns CfgValue and CfgOption. I want the ASP page to look at this table for all rows where CfgValue = ‘Sales Assistant’ and compare AgtType to the resulting CfgOption column (i.e. select CfgOption from configuration where CfgValue = 'Sales Assistant').

If it is equal to any of the CfgOption values then it satisfies the criteria. I hope I am making sense. I am just not sure how to go about building the VBScript statement.

Thanks!!

Sam

[rokslide]

So you want something like....

rsCriteria.Open "Select CfgValue, CfgOption from tblConfiguration where CfgOption=" & Session("AgtType")

to get your criteria for your data search.... and then...

rsData.Open "Select " & rsCriteria("CfgValue") & " from table blah blah blah"

[samajam]

Sorry for the lack of clarity in my post. I was trying to keep it short and sweet but it looks like it ended up being more confusing. Let me try again…

I have the following code hard coded in some of my ASP pages:

If Session("AgtType") = "3" Then
rsData.Open "Select AgentID, blah blah blah..."

I now have an additional AgtType of 9 that I want the code to check for. I could just rewrite the code to:

If Session("AgtType") = "3" or Session("AgtType") = "9" Then
rsData.Open "Select AgentID, blah blah blah..."

However, I want to avoid having to hard code it so that if I have to make a change in the future I don’t need to go back to all 35 (or whatever) ASP pages and make the change. Having the ASP pages look up an SQL table would allow me to make the change in one place and save me time.

The table to be looked up is called Configuration and it has 3 columns: CfgOption, CfgCode, CfgValue. I want the ASP code to look up this table and compare AgtType to the CfgOption column where CfgValue equals ‘Sales Assistant’. If I do a select statement it yields the following:

CfgOption CfgCode CfgValue
------------------------------ ---------- ------------------------------
3 NULL Sales Assistant
9 NULL Sales Assistant

In this case it would look to to see if AgtType is equal to the CfgOption value of 3 or 9. I was wondering about how to construct the ASP code to do this lookup and comparison.

I hope this makes more sense. I have been thinking more about this and was thinking of using a flag. This is what I have so far:

<%
'Determine if Agent Type is Sales assistant
'by looking up Configuration table;
'flag set to 1 if so, 0 if not

Dim rsAgtType
Dim flAgtType

set flAgtType = 0
Set rsAgtType = Server.CreateObject("ADODB.Recordset")
rsAgtType.Open "SELECT CfgOption FROM configuration WHERE CfgValue = 'Sales Assistant'" adoCn, adOpenForwardOnly, adLockReadOnly
While not rsAgtType.EOF
If Session("AgtType") = rsAgtType("CfgOption") then
set flAgtType = 1
end if
rsAgtType.MoveNext
Wend
rsAgtType.Close
%>

Then later on in the code I have the following:

If flAgtType = 1 Then
rsData.Open "Select AgentID, blah blah blah..."

Am I on the right track or is there a better way to go about this?

Thanks a million!!

Sam

[Cipherlad]

How many AgentTypes do you have?

There's a neat trick you can use with hexadecimal values that will take care of your problem.

Essentially, the ID you would give to each Agent would be an integer value, based on a "doubling" system...

AgentID
--------
1
2
4
8
16
32
64


Here's the key, then, you can express any COMBINATION of Agents by summing up the ID values (i.e. "3" can only consist of Agent ID's "2" and "1";
"13" can only consist of "8", "4", and "1").

You'll put your sum value (whatever combination it is you want to check for) into an Application variable. When you need to compare it against a Session variable, your VBScript would read...

'If 3 and 1 = 1 Then
If Application("AgtSum") and Session("AgtType") = Session("AgtType") Then

[code here]
End If

It can be difficult to get used to, but it will solve your problem.

[Cipherlad]

Another option for you I forgot to mention, would be to create a global function that would check your value for you and return a true/false. That way when you need to change your code, you only have to do it in one place.