Classic ASP : locate specific order in array, then move up or down in array based on that specific/ current order

I need to be able to find an element in an array, or a record-set, always starting at position of the order id value that gets passed through query-string.  Then start moving in the array, or record-set Next, or Previous, having as the starting point, the current order id.

Example array, or record-set, or array either solution is ok

0 02345
1 04568
2 07549
3 01954
4 06183

If Order Id passed is 07549 then

0 02345 - Beginning of file/ array - cannot go further back
1 04568 - previous
2 07549 - START HERE/ Current, can go previous, and can go next
3 01954 - next
4 06183 - End of file/ array - cannot go further forward

If Order Id passed is 02345

0 02345 - START HERE/ cannot go further back
1 04568 - next
2 07549 - next
3 01954 - next
4 06183 - End of file/ array - cannot go further forward

If Order Id passed is 06183

0 02345 - beginning of file/ array - cannot go further back
1 04568 - next
2 07549 - next
3 01954 - next
4 06183 - START HERE, and this is EOF/ array - cannot go further forward

I am trying to do the code but have not got much further, and need serious help if possible, I can learn quick, but I do need some examples in order to understand what I need to do. I have seen examples using record-set moveNext, movePrevious, but I am not sure how to implement those examples when I need to START from an specific position in the record-set.

Here is what I have for coding right now:

HTML
            .Write "<input type='submit' name='btnDown' value='Previous' class='buttonLeft' />"                
            .Write "<input type='submit' name='btnUp' value='Next' class='buttonRight'  />"   

Open in new window


ASP Code - not much and not clear, please understand I am stuck on this code.
    If Request("btnUp") = "Next" Then Call FuctionUp()
    If Request("btnDown") = "Previous" Then Call FuctionDown()

Function FuctionUp()
    Dim objConn
    Dim objRS
    Dim SQLOrderList
    Dim SQLCurrentOrder 
    Dim strCurrentOrder 
    strCurrentOrder = Trim(Request.QueryString("order"))
    Dim strPreviousOrder
        
    Set objRS = Server.CreateObject("ADODB.Recordset")
    Set objConn = CreateObject("ADODB.Connection")
    objConn.Open Application(strConn)
    objRS.Cursortype = 3
    
    SQLOrderList = "SELECT orderno" & _
                    " FROM order" & _
                    " WHERE order_date >= '" & strDateStart & "'" & _
                    " AND order_date < '" & strDateEnd & "'" & _     
                    " ORDER BY " & strOrderBy
    
    objRS.Open SQLOrderList, objConn        
   
    'Get this to Array.
    Dim iArray
    Dim i
    Dim found
    iArray = objRS.GetRows()
 
    found = false
    For i = 0 To ubound(iArray)
        If iArray(i) = strCurrentOrder.value Then
            found = true
            Exit For
        End if
    Next
    objRS.Close()
    Set objRS = Nothing
    objConn.Close()
    Set objConn = Nothing
End Function

Function FuctionDown()
    Dim objConn
    Dim objRS
    Dim strCurrentOrder 
    strCurrentOrder = Trim(Request.QueryString("order"))
    Dim SQLCurrentOrder
    Dim SQLOrderList   
    Dim strNextOrder
    
    Set objRS = Server.CreateObject("ADODB.Recordset")
    Set objConn = CreateObject("ADODB.Connection")

    objConn.Open Application(strConn)

    objRS.Cursortype = 3

    SQLOrderList = "SELECT orderno" & _
                    " FROM order" & _
                    " WHERE order_date >= '" & strDateStart & "'" & _
                    " AND order_date < '" & strDateEnd & "'" & _     
                    " ORDER BY " & strOrderBy
    
    objRS.Open SQLOrderList, objConn      
    
    If objRS.BOF Then
        Response.Write "This is the first record in the file."
    Else
        Response.Write(objRS.Fields("orderno").value & ", ")        
        objRS.MoveNext()
    End If

    objRS.Close()
    Set objRS = Nothing
    objConn.Close()
    Set objConn = Nothing
End Function

Open in new window

the_lone_noteAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
The problem with using recordsets is you keep hitting the database and that can be slow.   The next best thing is using a server side array where you use getrows to send out the rows of data to an array then page using the array.   It is nicely outlined in this old article about paging using getrows http://www.4guysfromrolla.com/webtech/070500-1.shtml

If you are trying to show a table of data and being carful to only place 30 to 50 rows of data on the page at once, then I think an even better approach is to still use getrows but as an ajax request and throw the data to a javascript table where things can be quickly searched and sorted on the client making things fast.    So check out datatables https://www.datatables.net/ and specifically https://www.datatables.net/examples/data_sources/ajax.html

Here are a few threads on using datatables
http://www.experts-exchange.com/Programming/Languages/Scripting/AJAX/Q_28479115.html
http://www.experts-exchange.com/Programming/Languages/Java/Q_28257163.html
http://www.experts-exchange.com/Programming/Languages/Scripting/AJAX/Q_28509347.html

See if datatables does what you want. If so we can work on it in steps.  But step one is going to the example I showed you and just get the static example to work.  From there we can point out how to do things like create json data from asp code etc.
Big MontyWeb Ninja at largeCommented:
stick with arrays, you don't want to get into the world of managing recordset cursors, it's not worth the aggravation, plus arrays or more efficient. Scot's first link to the tutorial on paging is def worth looking at, it's essentially keeping track of your "place" in the data set.

to apply it to your scenario, simply add a reference to your current location as a querystring parameter, and then process the data based off of that:

first the html, note i change the button type to "button" and added some javascript to submit the form
.Write "<input type='button' name='btnDown' value='Previous' class='buttonLeft' onclick='document.forms[0].action=""page.asp?index=<%=index - 1%>""; document.forms[0].submit();' />"                
.Write "<input type='submit' name='button' value='Next' class='buttonRight' onclick='document.forms[0].action=""page.asp?index=<%=index + 1%>""; document.forms[0].submit();'  />"   

Open in new window


then, with the server side code you have, you would tweak it to:

    dim index : index = Request.QueryString("index")
    if not isNumeric( index ) or index = "" then index = 0     '-- default starting point

    processData()

Function processData()
    Dim objConn
    Dim objRS
    Dim SQLOrderList
    Dim SQLCurrentOrder 
    Dim strCurrentOrder 
    strCurrentOrder = Trim(Request.QueryString("order"))
    Dim strPreviousOrder
        
    Set objRS = Server.CreateObject("ADODB.Recordset")
    Set objConn = CreateObject("ADODB.Connection")
    objConn.Open Application(strConn)
    objRS.Cursortype = 3
    
    SQLOrderList = "SELECT orderno" & _
                    " FROM order" & _
                    " WHERE order_date >= '" & strDateStart & "'" & _
                    " AND order_date < '" & strDateEnd & "'" & _     
                    " ORDER BY " & strOrderBy
    
    objRS.Open SQLOrderList, objConn        
   
    'Get this to Array.
    Dim iArray
    Dim i
    iArray = objRS.GetRows()
 
    '-- at this point, i'm not sure what you want to do when you've found the place you want to be. below is writing out the records from the current index until the end

   for i = index to UBound( iArray, 2 )   '-- assuming 2-d array here
      Response.Write iArray( i, 0 )
      Response.Write iArray( i, 1 )
      Response.Write iArray( i, 2 )
   next
End Function

Open in new window


with more info on what you're trying to accomplish, we'll be able to help you better

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
the_lone_noteAuthor Commented:
Thank you guys for your help. I will read the article and also give Big Monty's code a try and see if I can make it work on my own, otherwise I will post my questions fir sure.

Many thanks.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

the_lone_noteAuthor Commented:
Hi Big Monty,

in the part in which you state not being sure as to what I need to do in the code, I have some asp.net code that does what I need, but it uses a type List, instead that an array. I just need to mimic the functionality, but in classic asp.

I am stuck in your code, in the part in which I have to locate the next order in the array, using the current order reference index that I pass in the URL (param: rowindex)

This is what I do in .net - I am using the Up button function (next)
Dim currentOrder = (From order In orderList.Items Where order.OrderID.Equals(currentOrderID) Select order).FirstOrDefault()

        If currentOrder IsNot Nothing Then

            Dim index = orderList.Items.IndexOf(currentOrder)

            If Not index >= orderList.Items.Count - 1 Then previousOrder = DirectCast(orderList.Items(index + 1), Order)

            If previousOrder IsNot Nothing Then
                Response.Redirect("~/order-review.aspx?order_id=" & previousOrder.OrderID &
                                  "&siteKey=" & spiderSiteKey &
                                  "&strEnv=" & strEnv &
                                  "&Start=" & CDate(Request.QueryString("Start")) &
                                  "&End=" & CDate(Request.QueryString("End")) &
                                  "&SortBy=" & strSortBy &
                                  "&SortDirection=" & strSortDirection)
            Else
                Response.Redirect("~/order-review.aspx?order_id=" & currentOrder.OrderID &
                                  "&siteKey=" & spiderSiteKey &
                                  "&strEnv=" & strEnv &
                                  "&Start=" & CDate(Request.QueryString("Start")) &
                                  "&End=" & CDate(Request.QueryString("End")) &
                                  "&SortBy=" & strSortBy &
                                  "&SortDirection=" & strSortDirection &
                                  "&LastRecord=Up")
            End If
        End If

Open in new window

the_lone_noteAuthor Commented:
This is as far as I got using array.

    If Request("btnUp") = "Next" Then Call FuctionUp()
    If Request("btnDown") = "Previous" Then Call FuctionDown()

Function FuctionUp()
    Dim objConn
    Dim objRS
    Dim SQLOrderList
    Dim SQLCurrentOrder 
    Dim currentorder     
    Dim previousorder
    Dim sortby 
    Dim dtstart
    Dim dtend
    Dim index 

    currentorder = Trim(Request.QueryString("order"))
    sortby = Request.QueryString("sortby")
    currentorder = Request.QueryString("order")
    dtstart = Request.QueryString("start")
    dtend = Request.QueryString("end")

    Set objRS = Server.CreateObject("ADODB.Recordset")
    Set objConn = CreateObject("ADODB.Connection")
    
    objConn.Open Application("conn_AWDSTAGE")
    objRS.Cursortype = 3    
    
    SQLOrderList = "SELECT orderno" & _
                    " FROM _order" & _
                    " WHERE order_date >= '" & dtstart & "'" & _
                    " AND order_date < '" & dtend & "'" & _     
                    " ORDER BY " & sortby    
    objRS.Open SQLOrderList, objConn
    
    index = CINT(Request.QueryString("rowindex"))
            
    if not isNumeric(index) or index = "" then 
        index = 0 
    end if 
          
    'Get this to Array.
    Dim iArray
    Dim i
    Dim sizeOfiArray
    iArray = objRS.GetRows()    

    ' sample of array contents after sql execution

    'A1G722
    'A1G723
    'A1G724
    'A1G725
    'A1G726
    'A1G727

    sizeOfiArray = uBound(iArray) + 1    
    if not index >= sizeOfiArray - 1 then previousorder = (index + 1)

    ' if previous order is not nothing, then I have to get the order id value for that previous order
    ' and redirect to page printpreview.asp with the previous order id value as a querystring param, 
    ' otherwise redirect to printpreview.asp using current order id which is the current index value order id in the array.

    objRS.Close()
    Set objRS = Nothing
    objConn.Close()
    Set objConn = Nothing

End Function

Open in new window

the_lone_noteAuthor Commented:
I did a bit more, but I am still unsure this is going to work.

So far I got the function up coded, this is the code that will go in the following sequence:

If array of orders contains:
   
    'A1G722
    'A1G723
    'A1G724
    'A1G725
    'A1G726
    'A1G727

And the order passed in query-string is A1G725, the function up will go A1G725, A1G724, A1G723, A1G722


The code for the button:
.Write "<input type='submit' name='btnUp' value='Next' class='buttonRight' />"

Open in new window


The code that calls the function:
If Request("btnUp") = "Next" Then Call FuctionUp()

Open in new window


The code for the function:
Function FuctionUp()
    Dim objConn
    Dim objRS
    Dim SQLOrderList
    Dim SQLCurrentOrder 
    Dim currentorder     
    Dim previousorder
    Dim sortby 
    Dim dtstart
    Dim dtend
    Dim index 

    currentorder = Trim(Request.QueryString("order"))
    sortby = Request.QueryString("sortby")
    currentorder = Request.QueryString("order")
    dtstart = Request.QueryString("start")
    dtend = Request.QueryString("end")

    Set objRS = Server.CreateObject("ADODB.Recordset")
    Set objConn = CreateObject("ADODB.Connection")
    
    objConn.Open Application("conn_AWDSTAGE")
    objRS.Cursortype = 3    
    
    SQLOrderList = "SELECT orderno" & _
                    " FROM awd_order" & _
                    " WHERE order_date >= '" & dtstart & "'" & _
                    " AND order_date < '" & dtend & "'" & _     
                    " ORDER BY " & sortby    
    objRS.Open SQLOrderList, objConn
    
    index = CINT(Request.QueryString("rowindex"))
            
    If Not isNumeric(index) Or index = "" Then 
        index = 0 
    End If 
          
    'Get this to Array.
    Dim iArray
    Dim i
    Dim sizeOfiArray
    iArray = objRS.GetRows()    

    ' sample of array contents after sql execution
    'A1G722
    'A1G723
    'A1G724
    'A1G725
    'A1G726
    'A1G727

    sizeOfiArray = uBound(iArray) + 1    
    if not index >= (sizeOfiArray - 1) then previousorder = (index + 1)

    If Not previousorder Is Nothing Then
        Response.Redirect("~/printpreview.asp?order=" & previousorder(i) &
                            "&site=" & spiderSiteKey &
                            "&env=" & strEnv &
                            "&start=" & CDate(dtstart) &
                            "&end=" & CDate(dtend) &
                            "&rowindex=" & (index + 1) &
                            "&sortby=" & sortby)
    Else
        Response.Redirect("~/printpreview.asp?order=" & currentOrder.OrderID &
                            "&site=" & spiderSiteKey &
                            "&env=" & strEnv &
                            "&start=" & CDate(dtstart)) &
                            "&end=" & CDate(dtend) &
                            "&rowindex=" & (index) &
                            "&sortby=" & strSortBy &
                            "&LastRecord=Up")
    End If

    objRS.Close()
    Set objRS = Nothing
    objConn.Close()
    Set objConn = Nothing

End Function

Open in new window


I am no tsure how to obtaing the desired value from the array, I get the index value, then using the index value, access the 'previous' value if exists, how the vaue is placed into the previousorder variable?
Big MontyWeb Ninja at largeCommented:
i'm having a hard time following your code:

sizeOfiArray = uBound(iArray) + 1    
    if not index >= (sizeOfiArray - 1) then previousorder = (index + 1)

    If Not previousorder Is Nothing Then
        Response.Redirect("~/printpreview.asp?order=" & previousorder(i) &
                            "&site=" & spiderSiteKey &
                            "&env=" & strEnv &
                            "&start=" & CDate(dtstart) &
                            "&end=" & CDate(dtend) &
                            "&rowindex=" & (index + 1) &
                            "&sortby=" & sortby)
    Else
        Response.Redirect("~/printpreview.asp?order=" & currentOrder.OrderID &
                            "&site=" & spiderSiteKey &
                            "&env=" & strEnv &
                            "&start=" & CDate(dtstart)) &
                            "&end=" & CDate(dtend) &
                            "&rowindex=" & (index) &
                            "&sortby=" & strSortBy &
                            "&LastRecord=Up")
    End If

Open in new window


at one point, the variable previousOrder is an integer, then two lines below, it's being referenced as an array, using "i" as an index pointer in the array, although it's never declared or set a value.

- set a global variable, call it index. this'll be your overall position in the array. it should always be set via a querystring parameter. if that querystring parameter doesn't exist, set it to 0
- using the markup i gave you for the next/prev button, this'll set the index to the right value
- wherever you need to access the array, access it with index variable, this'll give you the proper position
the_lone_noteAuthor Commented:
I tried to follow your directions and some examples online, this is what I have for the Up function:
Please let me know if this code looks acceptable.

Many thanks,

    If Request("btnUp") = "Next" Then Call FuctionUp()
    If Request("btnDown") = "Previous" Then Call FuctionDown()

Function FuctionUp()

    Dim objConn
    Dim objRS
    Dim SQLOrderList
    Dim SQLCurrentOrder 
    Dim currentorder     
    Dim nextorderindex
    Dim sortby 
    Dim dtstart
    Dim dtend
    Dim rowindex 
    Dim iArray
    Dim i
    Dim sizeOfiArray

    currentorder = Trim(Request.QueryString("order"))
    sortby = Trim(Request.QueryString("sortby"))
    dtstart = Trim(Request.QueryString("start"))
    dtend = Trim(Request.QueryString("end"))
    rowindex = CINT(Trim(Request.QueryString("rowindex")))

    Set objRS = Server.CreateObject("ADODB.Recordset")
    Set objConn = CreateObject("ADODB.Connection")
    
    objConn.Open Application(strConn)
    objRS.Cursortype = 3    
    
    SQLOrderList = "SELECT orderno" & _
                    " FROM _order" & _
                    " WHERE order_date >= '" & dtstart & " 00:00:00'" & _
                    " AND order_date < '" & dtend & " 23:59:59'" & _                    
                    " ORDER BY " & sortby

    objRS.Open SQLOrderList, objConn
    
    i = objRS.RecordCount    
    
    If i > 0 Then
        If Not isNumeric(rowindex) Or rowindex = "" Then 
            rowindex = 0 
        End If 
    
        If rowindex < (i) Then 
            nextorderindex = (rowindex+1)
        End If
        
        'Get this to Array        
        iArray = objRS.GetRows((i),0)

        If nextorderindex >= 0 Then
            'response.write iArray(0,nextorderindex) 
            Response.Redirect("printpreview.asp?order=" & iArray(0,nextorderindex) & _
                                "&site=" & strSite & _
                                "&env=" & strEnv & _
                                "&start=" & CDate(dtstart) & _
                                "&end=" & CDate(dtend) & _
                                "&rowindex=" & nextorderindex & _
                                "&sortby=" & sortby) 
        Else
            'response.write currentorder
            Response.Redirect("printpreview.asp?order=" & currentorder & _
                                "&site=" & strSite & _
                                "&env=" & strEnv & _
                                "&start=" & CDate(dtstart) & _
                                "&end=" & CDate(dtend) & _
                                "&rowindex=" & rowindex & _
                                "&sortby=" & sortby & _
                                "&LastRecord=Up")

        End If
    End If

    objRS.Close()
    Set objRS = Nothing
    objConn.Close()
    Set objConn = Nothing

End Function

Open in new window

the_lone_noteAuthor Commented:
The function Up goes to the next available order, the function Down, goes to the previous available order.

This is the Down function code, have not tested it thoroughly:

Function FuctionDown()

    Dim objConn
    Dim objRS
    Dim SQLOrderList
    Dim SQLCurrentOrder 
    Dim currentorder     
    Dim previousorderindex
    Dim sortby 
    Dim dtstart
    Dim dtend
    Dim rowindex 
    Dim iArray
    Dim i
    Dim sizeOfiArray

    currentorder = Trim(Request.QueryString("order"))
    sortby = Trim(Request.QueryString("sortby"))
    dtstart = Trim(Request.QueryString("start"))
    dtend = Trim(Request.QueryString("end"))
    rowindex = CINT(Trim(Request.QueryString("rowindex")))

    Set objRS = Server.CreateObject("ADODB.Recordset")
    Set objConn = CreateObject("ADODB.Connection")
    
    objConn.Open Application(strConn)
    objRS.Cursortype = 3   

    SQLOrderList = "SELECT orderno" & _
                    " FROM awd_order" & _
                    " WHERE order_date >= '" & dtstart & " 00:00:00'" & _
                    " AND order_date < '" & dtend & " 23:59:59'" & _                    
                    " ORDER BY " & sortby  
    
    objRS.Open SQLOrderList, objConn      
    
    i = objRS.RecordCount
    
    If i > 0 Then

        If Not isNumeric(rowindex) Or rowindex = "" Then
            rowindex = 0
        End If
    
        If rowindex < (i) Then
            previousorderindex = (rowindex-1)
        End If
        
        'Get this to Array        
        iArray = objRS.GetRows((i),0)

        If Not previousorderindex < 0 Then
            'response.write iArray(0,previousorderindex) 
            Response.Redirect("printpreview.asp?order=" & iArray(0,previousorderindex) & _
                                "&site=" & strSite & _
                                "&env=" & strEnv & _
                                "&start=" & CDate(dtstart) & _
                                "&end=" & CDate(dtend) & _
                                "&rowindex=" & previousorderindex & _
                                "&sortby=" & sortby) 
        Else
            'response.write currentorder
            Response.Redirect("printpreview.asp?order=" & currentorder & _
                                "&site=" & strSite & _
                                "&env=" & strEnv & _
                                "&start=" & CDate(dtstart) & _
                                "&end=" & CDate(dtend) & _
                                "&rowindex=" & rowindex & _
                                "&sortby=" & sortby & _
                                "&LastRecord=Down")

        End If
    End If

    objRS.Close()
    Set objRS = Nothing
    objConn.Close()
    Set objConn = Nothing


End Function

Open in new window

Big MontyWeb Ninja at largeCommented:
the functionUp() function looks good, although you don't really need two different functions, but we can do it this way just as a proof of concept.

when you run it, does it do what you want?
the_lone_noteAuthor Commented:
The functionUp() function does what I need it to do. I move from the current order to the next by redirecting to the same page, but submitting the next order information. When I reach the last order going 'up', then I get redirected to the same page, but staying in the current order, with a message displaying "no more orders to go next"

Where you able to take a glance to the funtionDown() funtion? I basically need that function to do the same as the functionUP() function, but the other way around.

Many thanks.
Big MontyWeb Ninja at largeCommented:
looks good to me, it's setting the proper index based off of what the query string value is and if it's less than 0, reverts back to the current order.

what happens when you run it?
the_lone_noteAuthor Commented:
I just checked the funtionUp(0 function again, and it seems is not working as I need it to.

On the check:
If nextorderindex >= 0 Then

Open in new window


nextorderindex is always >= 0, so it never goes to the else part of the statement. I think instead that doing a >= 0 I should do a >= length of the array. But I do not know how this can be done. Will it be possible you show me how to do this?

Many thanks.
the_lone_noteAuthor Commented:
I meant do a nextorderindex >= (lenght of array - 1)  - does this sound correct?
Big MontyWeb Ninja at largeCommented:
since you already have the RecordCount stored in the variable "i", you can do something like:

If nextorderindex >= 0 and nextorderindex <= i Then
the_lone_noteAuthor Commented:
I am trying that and what I am seeing is that if the last nextorderindex contains a 7 and the rs count is 7, then it will not go to the Else. Do I need to replace the And with and Or?


If nextorderindex(7) >= 0 And nextorderindex(7) <= i(7) Then
Big MontyWeb Ninja at largeCommented:
"i" is a variable, not an array, so what you have there can't work. same with nextOrderIndex, they are both variables being used as an integer, NOT an array. just use what I gave you:

If nextorderindex >= 0 and nextorderindex <= i Then

you could probably drop the check for >= 0, but it doesn't hurt to have it there. what this statement does is saying "if the index is less than or equal to the over all record count, proceed as normal, otherwise it's gone out of the bounds of the array (it's gone over the over all record count) which is no good"
the_lone_noteAuthor Commented:
This is what worked for me:

 If nextorderindex >= 0 and Not nextorderindex >= i Then

I probably should remove >=0 check but so far I have tested, it works.

Thank you very much for all your help. It has been really great to learn how to do this.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.