<%
'---------------------------------
' Get our Record Set into an Array
'---------------------------------
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("***.mdb")
Conn.Errors.Clear
set rs = Server.CreateObject("ADODB.Recordset")
Sql = "SELECT ID, DisplayOrder, Name, Profile FROM crew ORDER By DisplayOrder"
set rs = Conn.Execute (Sql)
if not rs.EOF then
  MyArray = rs.GetRows()
  rs.Close()
  set rs = nothing
  NumCols = ubound(MyArray,1)
  NumRows = ubound(MyArray,2)
else
  NumCols = 0
  NumRows = 0
end if

'---------------------------
' Define our Column Pointers
'---------------------------
const pID = 0
const pDisplayOrder = 1
const pName = 2
const pProfile = 3

'-------------------------
' Any Changes to be made ?
'-------------------------
if isnumeric(Request.QueryString("cpos")) then 
  MyOrder = Request.QueryString("order")
  cPosition = cint(Request.QueryString("cpos"))
  select case MyOrder
    case "up"
      for i = 0 to NumRows
        SQL = "UPDATE crew SET DisplayOrder = " & i & " WHERE ID = " & MyArray(pID,i)          
        if i = cPosition - 1 then SQL = "UPDATE crew SET DisplayOrder = " & i &  " WHERE ID = " & MyArray(pID,i+1)    
        if i = cPosition then SQL = "UPDATE crew SET DisplayOrder = " & i & " WHERE ID = " & MyArray(pID,i-1)
        Conn.Execute(SQL)
      next
    case "down"
      for i = 0 to NumRows
        SQL = "UPDATE crew SET DisplayOrder = " & i & " WHERE ID = " & MyArray(pID,i)          
        if i = cPosition then SQL = "UPDATE crew SET DisplayOrder = " & i+1 &  " WHERE ID = " & MyArray(pID,i)    
        if i = cPosition+1 then SQL = "UPDATE crew SET DisplayOrder = " & i-1 & " WHERE ID = " & MyArray(pID,i)
        Conn.Execute(SQL)
      next
    Conn.Execute(SQL)    
  end select  
  
  '----------------------------------
  ' Refresh our Recordset for Display 
  '----------------------------------
  set rs = Server.CreateObject("ADODB.Recordset")
  Sql = "SELECT ID, DisplayOrder, Name, Profile FROM crew ORDER By DisplayOrder"
  set rs = Conn.Execute (Sql)
  if not rs.EOF then
    MyArray = rs.GetRows()
    rs.Close()
    set rs = nothing
    Conn.Close()
    set Conn = nothing
    NumCols = ubound(MyArray,1)
    NumRows = ubound(MyArray,2)
  else
    NumCols = 0
    NumRows = 0
  end if

end if

'------------------
' Display the Table
'------------------
Response.Write "<table border=""1"" cellpadding=""4"" cellspacing=""0"">" & vbNewLine &_
               "  <tr>" & vbNewLine &_
               "    <td> </td>" & vbNewLine &_
               "    <td>Record Id</td>" & vbNewLine &_
               "    <td>Name</td>" & vbNewLine &_
               "    <td>Profile</td>" & vbNewLine &_
               "  </tr>" & vbNewLine
               
for i = 0 to NumRows
  ID = myArray(pId,i)
  DisplayOrder = MyArray(pDisplayOrder,i)
  Name = MyArray(pName,i)
  Profile = MyArray(pProfile,i)

  Response.Write "  <tr>" & vbNewLine &_
                 "    <td>" & vbNewLine
  if i > 0 then           
    Response.Write "      <input type=""button"" name=""MoveUp"" value=""+"" onclick=""location.href='sort.asp?order=up&cpos=" & i & "'"" title=""Move Up"">" & vbNewLine
  end if
  if i < NumRows then
    Response.Write "      <input type=""button"" name=""MoveDown"" value=""-"" onclick=""location.href='sort.asp?order=down&cpos=" & i & "'"" title=""Move Down"">" & vbNewLine
  end if
  Response.Write "    </td>" & vbNewLine &_
                 "    <td>" & ID & "</td>" & vbNewLine &_
                 "    <td>" & Name & "</td>" & vbNewLine &_
                 "    <td>" & Profile & "</td>" & vbNewLine &_
                 "  </tr>" & vbNewLine

next
  
Response.Write "</table>" & vbNewLine
%>