Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    Portland, Oregon
    Posts
    5

    Question Unanswered: Referencing SQL tables in VBScript IF statements

    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

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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"

  3. #3
    Join Date
    Nov 2003
    Location
    Portland, Oregon
    Posts
    5

    Question

    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

  4. #4
    Join Date
    Dec 2003
    Location
    Inland Empire
    Posts
    18

    Hex values

    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.

  5. #5
    Join Date
    Dec 2003
    Location
    Inland Empire
    Posts
    18
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •