Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder
    Join Date
    Oct 2004
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sql_getVAR = "SELECT DISTINCT help

    please bare with me here:
    <%
    dim VAR(2)
    VAR(1)="nba"
    VAR(2)="mlb"

    sql_getVAR = "SELECT DISTINCT pcity FROM VAR"
    Set getVAR = Conn.Execute(sql_getVAR) %>

    I want several table according to the sport (nba,mlb, nfl etc...) I thought if i put the above code on an asp page (page02.asp) and then specified VAR on page01.asp with the link for NBA being /teams_02.asp?VAR=1 it would display the fields from the "nba" table.
    Of course it did not saying the error was that VAR was not defined.
    Can someone tell me what I am doin wrong or if you understand it, explain a better way to me. Thanks.

  • #2
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    10,965
    Thanks
    0
    Thanked 236 Times in 233 Posts
    Array index starts at 0 not 1.
    Code:
    <%
    dim VAR(1) '1 means the upper bound of the array (the last index). so an upper bound of 1 means 2 (ubound + 1) elements.
    VAR(0)="nba"
    VAR(1)="mlb"
    
    sql_getVAR = "SELECT DISTINCT pcity FROM " & Join(VAR, ",")
    Set getVAR = Conn.Execute(sql_getVAR) 
    %>
    The sql statement will be:

    SELECT DISTINCT pcity FROM nba,mlb

  • #3
    Regular Coder
    Join Date
    Oct 2004
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok i understand that. however i want the VAR to be declared in a link seperate of that .asp doc. can that be done?

    i also no got an error saying the subscript was out of range.

    Code:
    <%
    dim VAR(0)  
    
    VAR(0)="mlb"
    VAR(1)="nba"
    
    sql_getVAR = "SELECT DISTINCT pcity FROM " & Join(VAR, ",")
    Set getVAR = Conn.Execute(sql_getVAR) 
    %>
    Last edited by tns123j; 12-17-2004 at 04:08 AM.

  • #4
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    10,965
    Thanks
    0
    Thanked 236 Times in 233 Posts
    Why did you use dim VAR(0), I used 1 not 0 in my previous post.

    Ok, i read your original post again and I think this i what you need.

    page01.asp:
    <a href="teams_02.asp?VAR=1">NBA</a>
    <a href="teams_02.asp?VAR=2">MLB</a>
    <a href="teams_02.asp?VAR=3">NFL</a>

    teams_02.asp:
    Code:
    <%
    select case Request.QueryString("VAR")
      case "1"
        sql_getVAR = "SELECT DISTINCT pcity FROM NBA"
      case "2"
        sql_getVAR = "SELECT DISTINCT pcity FROM MLB"
      case "3"
        sql_getVAR = "SELECT DISTINCT pcity FROM NFL"
      case else
        'invalid VAR, stop processing
        response.write "Invalid request!"
        response.end
    
        'or you may want to query a default table instead
        'sql_getVAR = "SELECT DISTINCT pcity FROM NBA"
    end select
    response.write "sql:" & sql_getVAR
    %>

  • #5
    Regular Coder
    Join Date
    Oct 2004
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    as you can tell i am very new to this and well, i suck. it didn't work saying line 40 had an error. this is line 40:
    Code:
    <%	Do Until getVAR.EOF %>
    i assume if that had a problem then the following will as well:
    Code:
    <%	getVAR.MoveNext
    	Loop %>
    </table>
    </body>
    </html>
    <%	getVAR.Close
    	Set getVAR = nothing
    	Conn.Close
    	Set Conn = nothing %>

    Thanks for helping .

  • #6
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    10,965
    Thanks
    0
    Thanked 236 Times in 233 Posts
    Where is line 40? and what error message? Can you post the whole code?

  • #7
    Regular Coder
    Join Date
    Jul 2004
    Location
    France
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts
    why don't you make it easier


    const var ="mlb,nba,nlf"
    dim sql_getVAR

    sql_getVAR = "SELECT DISTINCT pcity FROM " + split(var,",")(cint(Request.QueryString("var")))

    ................

    you must of course control that you get a valid Request.QueryString

    then just do >>>>


    if (getVAR.BOF = false) and (getVAR.EOF = false) then
    getVAR.MoveFirst
    While (NOT getVAR.EOF)
    '...get your rows here
    getVAR.MoveNext()
    Wend
    end if
    Last edited by gwendaal; 12-17-2004 at 05:58 AM.

  • #8
    Regular Coder
    Join Date
    Oct 2004
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i understood the Case statement post. However I do not understand the last post. could you possily explain this:

    Code:
    const var ="mlb,nba,nlf"
    dim sql_getVAR
    
    sql_getVAR = "SELECT DISTINCT pcity FROM " + split(var,",")(cint(Request.QueryString("var")))

  • #9
    Regular Coder
    Join Date
    Jul 2004
    Location
    France
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts
    you dont need at all the case statement >>>

    ------------------
    select case Request.QueryString("VAR")
    case "1"
    sql_getVAR = "SELECT DISTINCT pcity FROM NBA"
    case "10000000000"
    .....
    ---------------

    my code is just a shortcut for it >>>

    const varS ="mlb,nba,nlf,as many as you like,...."

    sql_getVAR = "SELECT DISTINCT pcity FROM " + + split(varS,",")(cint(Request.QueryString("var")))

    you just split on the coma and with cint(Request.QueryString("var")) you get an integer to return the value of your array

    with "mlb,nba,nlf"
    mlb = 0
    nba = 1
    nlf =2

    imagine you have 1000 possibilities !! ... case 1 to case 1000 is not realist

    split(myArrayofValues,",")(number) will return it in just one line of code
    Last edited by gwendaal; 12-20-2004 at 03:45 PM.

  • #10
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    10,965
    Thanks
    0
    Thanked 236 Times in 233 Posts
    Why not make an array so you don't have to split?

    page01.asp:
    <a href="teams_02.asp?VAR=0">NBA</a>
    <a href="teams_02.asp?VAR=1">MLB</a>
    <a href="teams_02.asp?VAR=2">NFL</a>
    ...

    teams_02.asp:
    Code:
    <%
    dim dbtable, index
    dbtable = Array("nba", "mlb", "nfl", "...") 
    index = Request.QueryString("VAR")
    if isnumeric(index) then 
      index = CInt(index)
    else
      index = 0
    end if
    if index < 0 or index > ubound(dbtable) then 'not within range
      index = 0
    end if
    
    sql_getVAR = "SELECT DISTINCT pcity FROM " & dbtable(index)
    response.write "sql:" & sql_getVAR
    %>

  • #11
    Regular Coder
    Join Date
    Jul 2004
    Location
    France
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts
    yes of course (but to split produce an array when needed) it was just a lazy way to write it .. the effect at the end is the same

    any kind of array ... the main thing is just to avoid a select case

    on error resume next
    index = Cint(Request.QueryString("VAR"))
    If Err.Number <> 0 then
    index = 0
    end if

    no matter the method

    or a Function writed one time

    Function QueryControler(QueryString)

    Dim index
    index = Request.QueryString(QueryString)

    if isNumeric(index) then
    QueryControler = index
    else
    QueryControler = 0
    end if

    End Function


    or

    Function QueryToArray(QueryString, anyArray)

    Dim index

    if isNumeric(Request.QueryString(QueryString)) then
    index = Request.QueryString(QueryString)
    else
    index = 0
    end if

    QueryToArray = anyArray(index)
    End Function

    sql_getVAR = "SELECT DISTINCT pcity FROM " & QueryToArray("VAR",dbtable)
    Last edited by gwendaal; 12-21-2004 at 08:15 AM.


  •  

    Posting Permissions

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