Hi,

I was wandering if anybody could help with this...

I am using ASP and I am using the following Sql statement in my Recordset:

SELECT question
FROM questions
WHERE reference = MMColParam

The form variable (MMColParam) is x_reference

I have 2 tables in my database. One of these has a list of questions in one field and a reference in the other. The other has a reference number. Im trying to link the 2 reference fields and disply the correct question relating to the reference number in the form on the page. Complicated i know but basically when i test this in the Recordset dialog, it works fine but when i try to display this on the page i get the following error:

ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/knowsley/datateam/618g/Table1Alist.asp, line 426

...I think this is because each page that i need to display the question on has more than one x_reference...works fine in Recordset dialog Test button.

Any advice on this appreciated as it is a very urgent project i'm working on.

Thanks.

You say the form variable (MMColParam) is x_reference... what do you mean by that?
Do you mean that you have a form variable named MMColParam and its value is the string "x_reference" or that you have a form variable that is named x_reference and it will hold a value that you put into a variable named MMColParam?

If you have more than one input element on a form with the same name it is likely that form variable of that name will contain a comma delimited string of all the values in all the elements with that name.

Show your code and I'm sure that someone here will easily solve the problem.

Woody Z
http://www.learntoprogramnow.com

<%@LANGUAGE="VBSCRIPT"%>

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_Recordset1_STRING
Recordset1.Source = "SELECT * FROM cover"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Recordset2
Dim Recordset2_numRows

Set Recordset2 = Server.CreateObject("ADODB.Recordset")
Recordset2.ActiveConnection = MM_Recordset1_STRING
Recordset2.Source = "SELECT reference FROM Table1A"
Recordset2.CursorType = 0
Recordset2.CursorLocation = 2
Recordset2.LockType = 1
Recordset2.Open()

Recordset2_numRows = 0
%>






<%
Response.Expires = 0
Response.ExpiresAbsolute = Now() - 1
Response.AddHeader "pragma", "no-cache"
Response.AddHeader "cache-control", "private, no-cache, no-store, must-revalidate"
%>
<%

' Initialize common variables
x_ID = Null: ox_ID = Null: z_ID = Null
x_reference = Null: ox_reference = Null: z_reference = Null
x_estab = Null: ox_estab = Null: z_estab = Null
x_School = Null: ox_School = Null: z_School = Null
x_a = Null: ox_a = Null: z_a = Null
x_b = Null: ox_b = Null: z_b = Null
x_c = Null: ox_c = Null: z_c = Null
x_d = Null: ox_d = Null: z_d = Null
x_e = Null: ox_e = Null: z_e = Null
x_f = Null: ox_f = Null: z_f = Null
x_g = Null: ox_g = Null: z_g = Null
x_h = Null: ox_h = Null: z_h = Null
%>
<%
sExport = Request.QueryString("export") ' Load Export Request
If sExport = "html" Then

    ' Printer Friendly
End If
If sExport = "excel" Then
    Response.ContentType = "application/vnd.ms-excel"
    Response.AddHeader "Content-Disposition", "attachment; filename=" & ewTblVar & ".xls"
End If
If sExport = "word" Then
    Response.ContentType = "application/vnd.ms-word"
    Response.AddHeader "Content-Disposition:", "attachment; filename=" & ewTblVar & ".doc"
End If
If sExport = "xml" Then
    Response.ContentType = "text/xml"
    Response.AddHeader "Content-Disposition:", "attachment; filename=" & ewTblVar & ".xml"
End If
If sExport = "csv" Then
    Response.ContentType = "application/csv"
    Response.AddHeader "Content-Disposition:", "attachment; filename=" & ewTblVar & ".csv"
End If
%>
<%
nStartRec = 0
nStopRec = 0
nTotalRecs = 0
nRecCount = 0
nRecActual = 0
sDbWhereMaster = ""
sDbWhereDetail = ""
sSrchAdvanced = ""
psearch = ""
psearchtype = ""
sSrchBasic = ""
sSrchWhere = ""
sDbWhere = ""
sOrderBy = ""
sSqlMaster = ""
nDisplayRecs = 20
nRecRange = 10

' Open connection to the database
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str

' Handle Reset Command
ResetCmd()

' Get Search Criteria for Basic Search
SetUpBasicSearch()

' Build Search Criteria
If sSrchAdvanced <> "" Then
    If sSrchWhere <> "" Then sSrchWhere = sSrchWhere & " AND "
    sSrchWhere = sSrchWhere & "(" & sSrchAdvanced & ")"
End If
If sSrchBasic <> "" Then
    If sSrchWhere <> "" Then sSrchWhere = sSrchWhere & " AND "
    sSrchWhere = sSrchWhere & "(" & sSrchBasic & ")"
End If

' Save Search Criteria
If sSrchWhere <> "" Then
    Session(ewSessionTblSearchWhere) = sSrchWhere
    nStartRec = 1 ' reset start record counter
    Session(ewSessionTblStartRec) = nStartRec
Else
    sSrchWhere = Session(ewSessionTblSearchWhere)
    Call RestoreSearch()
End If

' Build Filter condition
sDbWhere = ""
If sDbWhereDetail <> "" Then
    If sDbWhere <> "" Then sDbWhere = sDbWhere & " AND "
    sDbWhere = sDbWhere & "(" & sDbWhereDetail & ")"
End If
If sSrchWhere <> "" Then
    If sDbWhere <> "" Then sDbWhere = sDbWhere & " AND "
    sDbWhere = sDbWhere & "(" & sSrchWhere & ")"
End If


' Set Up Sorting Order
sOrderBy = ""
SetUpSortOrder()

' Set up SQL
sSql = ewBuildSql(ewSqlSelect, ewSqlWhere, ewSqlGroupBy, ewSqlHaving, ewSqlOrderBy, sDbWhere, sOrderBy)

'Response.Write sSql ' Uncomment to show SQL for debugging
' Export Data only

If sExport = "xml" Or sExport = "csv" Then
    Call ExportData(sExport, sSql)
    conn.Close ' Close Connection
    Set conn = Nothing
    Response.End
End If
%>
<% If sExport <> "word" And sExport <> "excel" Then %>


<script type="text/javascript">
<!--
EW_LookupFn = "ewlookup.asp"; // ewlookup file name
EW_AddOptFn = "ewaddopt.asp"; // ewaddopt.asp file name
//-->
</script>
<script type="text/javascript" src="ew.js"></script>
<script type="text/javascript">
<!--
EW_dateSep = "/"; // set date separator
EW_UploadAllowedFileExt = "gif,jpg,jpeg,bmp,png,doc,xls,pdf,zip"; // allowd upload file extension
//-->
</script>
<script type="text/javascript">
<!--
var firstrowoffset = 1; // first data row start at
var tablename = 'ewlistmain'; // table name
var usecss = true; // use css
//var usecss = false; // use css
var rowclass = 'ewTableRow'; // row class
var rowaltclass = 'ewTableAltRow'; // row alternate class
var rowmoverclass = 'ewTableHighlightRow'; // row mouse over class
var rowselectedclass = 'ewTableSelectRow'; // row selected class
var roweditclass = 'ewTableEditRow'; // row edit class
var rowcolor = '#FFFFFF'; // row color
var rowaltcolor = '#F5F5F5'; // row alternate color
var rowmovercolor = '#FFCCFF'; // row mouse over color
var rowselectedcolor = '#CCFFFF'; // row selected color
var roweditcolor = '#FFFF99'; // row edit color
//-->
</script>
<script type="text/javascript">
<!--
var EW_DHTMLEditors = [];
//-->
</script>
<% End If %>
<%

' Set up Record Set
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 2
rs.Open sSql, conn, 1, 2
nTotalRecs = rs.RecordCount
If nDisplayRecs <= 0 Then ' Display All Records
    nDisplayRecs = nTotalRecs
End If
nStartRec = 1
SetUpStartRec() ' Set Up Start Record Position
%>
<p><span class="aspmaker">TABLE: Table 1A</span></p>
<p><a href="http://knowit.kmbc/sites/DataTeam/Tutorials%20%20Guidance/Form%20618g%202007/Guidance%20Table%201.doc" target="_blank">guidance document available here</a></p>
<p><span class="aspmaker">Teachers with Qualified Teacher Status <br>
      <br>
  Note: Exclude: - teachers without Qualified Teacher Status (they go in table
  3A or 3B). <br>
  - teachers on contracts of less than one month (they go in tables 1C or 3B) <br>
  - teachers on secondment to another local education authority. <br>
  - teachers on unpaid leave. <br>
  Include: - teachers seconded for one term or more on training etc. or outside
  the LEA. <br>
  - seconded teachers to other than another local education authority, eg training. <br>
  - teachers on contracts of one month or more including those from agencies. <br>
  <br>
  The teacher should be recorded according to the grade/salary paid on the survey
  date. </span></p>
<p><span class="aspmaker">
      <% If sExport = "" Then %>
&nbsp;&nbsp;<a href="Table1Alist.asp?export=html">Printer Friendly</a> &nbsp;&nbsp;<a href="Table1Alist.asp?export=excel">Export to Excel</a> &nbsp;&nbsp;<a href="Table1Alist.asp?export=word">Export to Word</a> &nbsp;&nbsp;<a href="Table1Alist.asp?export=xml">Export to XML</a> &nbsp;&nbsp;<a href="Table1Alist.asp?export=csv">Export to CSV</a>
  <% End If %>
</span></p>
<% If sExport = "" Then %>
<form id="fTable1Alistsrch" name="fTable1Alistsrch" action="Table1Alist.asp" >
<table class="ewBasicSearch">
    <tr>
        <td><span class="aspmaker">
            <input type="text" name="<%=ewTblBasicSrch%>" size="100" disabled="false" value="<%=psearch%>">
            &nbsp;
        </span></td>
    </tr>
</table>
</form>
<% End If %>
<% If sExport = "" Then %>
<p>
<% End If %>
<%
If Session(ewSessionMessage) <> "" Then
%>
<p><span class="ewmsg"><%= Session(ewSessionMessage) %></span></p>
<%
    Session(ewSessionMessage) = "" ' Clear message
End If
%>
<% If nTotalRecs > 0 Then %>
      <span> </span>
      <form method="post">
      <p>&nbsp;</p>
      <table id="ewlistmain" class="ewTable">

    <tr class="ewTableHeader">
        <td valign="top"><span>
<% If sExport <> "" Then %>
ID
<% Else %>
    <a href="Table1Alist.asp?order=<%= Server.URLEncode("ID") %>">ID<% If Session(ewSessionTblSort & "_x_ID") = "ASC" Then %><img src="images/sortup.gif" width="10" height="9" border="0"><% ElseIf Session(ewSessionTblSort & "_x_ID") = "DESC" Then %><img src="images/sortdown.gif" width="10" height="9" border="0"><% End If %></a>
<% End If %>
        </span></td>
        <td valign="top"><span>
<% If sExport <> "" Then %>
Question
<% Else %>
    <a href="Table1Alist.asp?order=<%= Server.URLEncode("reference") %>">reference<% If Session(ewSessionTblSort & "_x_reference") = "ASC" Then %><img src="images/sortup.gif" width="10" height="9" border="0"><% ElseIf Session(ewSessionTblSort & "_x_reference") = "DESC" Then %><img src="images/sortdown.gif" width="10" height="9" border="0"><% End If %></a>
<% End If %>
        </span></td>
        <td valign="top"><span>
<% If sExport <> "" Then %>
estab
<% Else %>
    <a href="Table1Alist.asp?order=<%= Server.URLEncode("estab") %>">estab<% If Session(ewSessionTblSort & "_x_estab") = "ASC" Then %><img src="images/sortup.gif" width="10" height="9" border="0"><% ElseIf Session(ewSessionTblSort & "_x_estab") = "DESC" Then %><img src="images/sortdown.gif" width="10" height="9" border="0"><% End If %></a>
<% End If %>
        </span></td>
        <td valign="top"><span>
<% If sExport <> "" Then %>
School
<% Else %>
    <a href="Table1Alist.asp?order=<%= Server.URLEncode("School") %>">School&nbsp;(*)<% If Session(ewSessionTblSort & "_x_School") = "ASC" Then %><img src="images/sortup.gif" width="10" height="9" border="0"><% ElseIf Session(ewSessionTblSort & "_x_School") = "DESC" Then %><img src="images/sortdown.gif" width="10" height="9" border="0"><% End If %></a>
<% End If %>
        </span></td>
        <td valign="top"><span>
<% If sExport <> "" Then %>
a
<% Else %>
    <a href="Table1Alist.asp?order=<%= Server.URLEncode("a") %>">a<% If Session(ewSessionTblSort & "_x_a") = "ASC" Then %><img src="images/sortup.gif" width="10" height="9" border="0"><% ElseIf Session(ewSessionTblSort & "_x_a") = "DESC" Then %><img src="images/sortdown.gif" width="10" height="9" border="0"><% End If %></a>
<% End If %>
        </span></td>
        <td valign="top"><span>
<% If sExport <> "" Then %>
b
<% Else %>
    <a href="Table1Alist.asp?order=<%= Server.URLEncode("b") %>">b<% If Session(ewSessionTblSort & "_x_b") = "ASC" Then %><img src="images/sortup.gif" width="10" height="9" border="0"><% ElseIf Session(ewSessionTblSort & "_x_b") = "DESC" Then %><img src="images/sortdown.gif" width="10" height="9" border="0"><% End If %></a>
<% End If %>
        </span></td>
        <td valign="top"><span>
<% If sExport <> "" Then %>
c
<% Else %>
    <a href="Table1Alist.asp?order=<%= Server.URLEncode("c") %>">c<% If Session(ewSessionTblSort & "_x_c") = "ASC" Then %><img src="images/sortup.gif" width="10" height="9" border="0"><% ElseIf Session(ewSessionTblSort & "_x_c") = "DESC" Then %><img src="images/sortdown.gif" width="10" height="9" border="0"><% End If %></a>
<% End If %>
        </span></td>
        <td valign="top"><span>
<% If sExport <> "" Then %>
d
<% Else %>
    <a href="Table1Alist.asp?order=<%= Server.URLEncode("d") %>">d<% If Session(ewSessionTblSort & "_x_d") = "ASC" Then %><img src="images/sortup.gif" width="10" height="9" border="0"><% ElseIf Session(ewSessionTblSort & "_x_d") = "DESC" Then %><img src="images/sortdown.gif" width="10" height="9" border="0"><% End If %></a>
<% End If %>
        </span></td>
        <td valign="top"><span>
<% If sExport <> "" Then %>
e
<% Else %>
    <a href="Table1Alist.asp?order=<%= Server.URLEncode("e") %>">e<% If Session(ewSessionTblSort & "_x_e") = "ASC" Then %><img src="images/sortup.gif" width="10" height="9" border="0"><% ElseIf Session(ewSessionTblSort & "_x_e") = "DESC" Then %><img src="images/sortdown.gif" width="10" height="9" border="0"><% End If %></a>
<% End If %>
        </span></td>
        <td valign="top"><span>
<% If sExport <> "" Then %>
f
<% Else %>
    <a href="Table1Alist.asp?order=<%= Server.URLEncode("f") %>">f<% If Session(ewSessionTblSort & "_x_f") = "ASC" Then %><img src="images/sortup.gif" width="10" height="9" border="0"><% ElseIf Session(ewSessionTblSort & "_x_f") = "DESC" Then %><img src="images/sortdown.gif" width="10" height="9" border="0"><% End If %></a>
<% End If %>
        </span></td>
        <td valign="top"><span>
<% If sExport <> "" Then %>
g
<% Else %>
    <a href="Table1Alist.asp?order=<%= Server.URLEncode("g") %>">g<% If Session(ewSessionTblSort & "_x_g") = "ASC" Then %><img src="images/sortup.gif" width="10" height="9" border="0"><% ElseIf Session(ewSessionTblSort & "_x_g") = "DESC" Then %><img src="images/sortdown.gif" width="10" height="9" border="0"><% End If %></a>
<% End If %>
        </span></td>
        <td valign="top"><span>
<% If sExport <> "" Then %>
h
<% Else %>
    <a href="Table1Alist.asp?order=<%= Server.URLEncode("h") %>">h<% If Session(ewSessionTblSort & "_x_h") = "ASC" Then %><img src="images/sortup.gif" width="10" height="9" border="0"><% ElseIf Session(ewSessionTblSort & "_x_h") = "DESC" Then %><img src="images/sortdown.gif" width="10" height="9" border="0"><% End If %></a>
<% End If %>
        </span></td>
<% If sExport = "" Then %>
<td>&nbsp;</td>
<% End If %>
    </tr>
    <tr<%=sItemRowClass%><%=sListTrJs%>>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>Head teacher</td>
      <td>Deputy Head</td>
      <td>Assistant Head</td>
      <td>Excellent Teacher</td>
      <td>ASTs</td>
      <td>post-threshold</td>
      <td>Other classroom</td>
      <td>Total</td>
      <td>&nbsp;</td>
      </tr>
<%

' Set the last record to display
nStopRec = nStartRec + nDisplayRecs - 1

' Move to first record directly for performance reason
nRecCount = nStartRec - 1
If Not rs.Eof Then
    rs.MoveFirst
    rs.Move nStartRec - 1
End If
nRecActual = 0
Do While (Not rs.Eof) And (nRecCount < nStopRec)
    nRecCount = nRecCount + 1
    If CLng(nRecCount) >= CLng(nStartRec) Then
        nRecActual = nRecActual + 1

    ' Set row color
    sItemRowClass = " class=""ewTableRow"""
    sListTrJs = " onmouseover='ew_mouseover(this);' onmouseout='ew_mouseout(this);' onclick='ew_click(this);'"

    ' Display alternate color for rows
    If nRecCount Mod 2 <> 1 Then
        sItemRowClass = " class=""ewTableAltRow"""
    End If
    x_ID = rs("ID")
    x_reference = rs("reference")
    x_estab = rs("estab")
    x_School = rs("School")
    x_a = rs("a")
    x_b = rs("b")
    x_c = rs("c")
    x_d = rs("d")
    x_e = rs("e")
    x_f = rs("f")
    x_g = rs("g")
    x_h = rs("h")
%>

    <tr<%=sItemRowClass%><%=sListTrJs%>>

        <td height="26"><span>
<% Response.Write x_ID %>
</span></td>

            <td> <span>
            </span><span>
            <% Response.Write x_reference %>
            </span><span id="cb_x_reference">

</span> </td>

        <td><span>
<% Response.Write x_estab %>
</span></td>

        <td><span>
<% Response.Write x_School %>
</span></td>

        <td><span>
<% Response.Write x_a %>
</span></td>

        <td><span>
<% Response.Write x_b %>
</span></td>

        <td><span>
<% Response.Write x_c %>
</span></td>

        <td><span>
<% Response.Write x_d %>
</span></td>

        <td><span>
<% Response.Write x_e %>
</span></td>

        <td><span>
<% Response.Write x_f %>
</span></td>

        <td><span>
<% Response.Write x_g %>
</span></td>

        <td><span>
<% Response.Write x_h %>
</span></td>
<% If sExport = "" Then %>
<td><span class="aspmaker"><a href="<% If Not IsNull(x_ID) Then Response.Write "Table1Aedit.asp?ID=" & Server.URLEncode(x_ID) Else Response.Write "javascript:alert('Invalid Record! Key is null');" End If %>">Edit</a></span></td>
<% End If %>
    </tr>
<%
    End If
    rs.MoveNext
Loop
%>
</table>
</form>
<p><span class="aspmaker"><a href="<% If Not IsNull(x_ID) Then Response.Write "Table1Blist.asp?psearch=" & Server.URLEncode(x_School) Else Response.Write "javascript:alert('Invalid Record! Key is null');" End If %>">Once
      you are happy with your figures please click here to move to the next table >></a></span></p>
<p>
      <% End If %>
      <%

' Close recordset and connection
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
      <% If sExport = "" Then %>
</p>
<form action="Table1Alist.asp" name="ewpagerform" id="ewpagerform">
<table border="0" cellspacing="0" cellpadding="0">
    <tr>
        <td nowrap>
<%
If nTotalRecs > 0 Then
    rsEof = (nTotalRecs < (nStartRec + nDisplayRecs))
    PrevStart = nStartRec - nDisplayRecs
    If PrevStart < 1 Then PrevStart = 1
    NextStart = nStartRec + nDisplayRecs
    If NextStart > nTotalRecs Then NextStart = nStartRec
    LastStart = ((nTotalRecs-1)\nDisplayRecs)*nDisplayRecs+1
    %>
    <table border="0" cellspacing="0" cellpadding="0"><tr><td><span class="aspmaker">Page&nbsp;</span></td>

    <% If CLng(nStartRec)=1 Then %>
    <td><img src="images/firstdisab.gif" alt="First" width="16" height="16" border="0"></td>
    <% Else %>
    <td><a href="Table1Alist.asp?start=1"><img src="images/first.gif" alt="First" width="16" height="16" border="0"></a></td>
    <% End If %>

    <% If CLng(PrevStart) = CLng(nStartRec) Then %>
    <td><img src="images/prevdisab.gif" alt="Previous" width="16" height="16" border="0"></td>
    <% Else %>
    <td><a href="Table1Alist.asp?start=<%=PrevStart%>"><img src="images/prev.gif" alt="Previous" width="16" height="16" border="0"></a></td>
    <% End If %>

    <td><input type="text" name="pageno" value="<%=(nStartRec-1)\nDisplayRecs+1%>" size="4"></td>

    <% If CLng(NextStart) = CLng(nStartRec) Then %>
    <td><img src="images/nextdisab.gif" alt="Next" width="16" height="16" border="0"></td>
    <% Else %>
    <td><a href="Table1Alist.asp?start=<%=NextStart%>"><img src="images/next.gif" alt="Next" width="16" height="16" border="0"></a></td>
    <% End If %>

    <% If CLng(LastStart) = CLng(nStartRec) Then %>
    <td><img src="images/lastdisab.gif" alt="Last" width="16" height="16" border="0"></td>
    <% Else %>
    <td><a href="Table1Alist.asp?start=<%=LastStart%>"><img src="images/last.gif" alt="Last" width="16" height="16" border="0"></a></td>
    <% End If %>
    <td><span class="aspmaker">&nbsp;of <%=(nTotalRecs-1)\nDisplayRecs+1%></span></td>
    </tr></table>
    <% If CLng(nStartRec) > CLng(nTotalRecs) Then nStartRec = nTotalRecs
    nStopRec = nStartRec + nDisplayRecs - 1
    nRecCount = nTotalRecs - 1
    If rsEOF Then nRecCount = nTotalRecs
    If nStopRec > nRecCount Then nStopRec = nRecCount %>
    <span class="aspmaker">Records <%= nStartRec %> to <%= nStopRec %> of <%= nTotalRecs %></span>
<% Else %>
    <% If sSrchWhere = "0=101" Then %>
    <span class="aspmaker"></span>
    <% Else %>
    <span class="aspmaker">No records found</span>
    <% End If %>
<% End If %>
        </td>
    </tr>
</table>
</form>
<% End If %>
<% If sExport = "" Then %>
<% End If %>
<% If sExport <> "word" And sExport <> "excel" Then %>

<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
<%
Recordset2.Close()
Set Recordset2 = Nothing
%>
<% End If %>
<%

'-------------------------------------------------------------------------------
' Function BasicSearchSQL
' - Build WHERE clause for a keyword

Function BasicSearchSQL(Keyword)
    Dim sKeyword
    sKeyword = AdjustSql(Keyword)
    BasicSearchSQL = ""
    BasicSearchSQL = BasicSearchSQL & "[School] LIKE '%" & sKeyword & "%' OR "
    If Right(BasicSearchSQL, 4) = " OR " Then BasicSearchSQL = Left(BasicSearchSQL, Len(BasicSearchSQL)-4)
End Function

'-------------------------------------------------------------------------------
' Function SetUpBasicSearch
' - Set up Basic Search parameter based on form elements pSearch & pSearchType
' - Variables setup: sSrchBasic

Sub SetUpBasicSearch()
    Dim arKeyword, sKeyword
    psearch = Request.QueryString(ewTblBasicSrch)
    psearchtype = Request.QueryString(ewTblBasicSrchType)
    If psearch <> "" Then
        If psearchtype <> "" Then
            While InStr(psearch, " ") > 0
                sSearch = Replace(psearch, " ", " ")
            Wend
            arKeyword = Split(Trim(psearch), " ")
            For Each sKeyword In arKeyword
                sSrchBasic = sSrchBasic & "(" & BasicSearchSQL(sKeyword) & ") " & psearchtype & " "
            Next
        Else
            sSrchBasic = BasicSearchSQL(psearch)
        End If
    End If
    If Right(sSrchBasic, 4) = " OR " Then sSrchBasic = Left(sSrchBasic, Len(sSrchBasic)-4)
    If Right(sSrchBasic, 5) = " AND " Then sSrchBasic = Left(sSrchBasic, Len(sSrchBasic)-5)
    If psearch <> "" then
        Session(ewSessionTblBasicSrch) = psearch
        Session(ewSessionTblBasicSrchType) = psearchtype
    End If
End Sub

'-------------------------------------------------------------------------------
' Function ResetSearch
' - Clear all search parameters
'

Sub ResetSearch()

    ' Clear search where
    sSrchWhere = ""
    Session(ewSessionTblSearchWhere) = sSrchWhere

    ' Clear advanced search parameters
    Session(ewSessionTblAdvSrch & "_x_ID") = ""
    Session(ewSessionTblAdvSrch & "_x_reference") = ""
    Session(ewSessionTblAdvSrch & "_x_estab") = ""
    Session(ewSessionTblAdvSrch & "_x_School") = ""
    Session(ewSessionTblAdvSrch & "_x_a") = ""
    Session(ewSessionTblAdvSrch & "_x_b") = ""
    Session(ewSessionTblAdvSrch & "_x_c") = ""
    Session(ewSessionTblAdvSrch & "_x_d") = ""
    Session(ewSessionTblAdvSrch & "_x_e") = ""
    Session(ewSessionTblAdvSrch & "_x_f") = ""
    Session(ewSessionTblAdvSrch & "_x_g") = ""
    Session(ewSessionTblAdvSrch & "_x_h") = ""
    Session(ewSessionTblBasicSrch) = ""
    Session(ewSessionTblBasicSrchType) = ""
End Sub

'-------------------------------------------------------------------------------
' Function RestoreSearch
' - Restore all search parameters
'

Sub RestoreSearch()

    ' Restore advanced search settings
    x_ID = Session(ewSessionTblAdvSrch & "_x_ID")
    x_reference = Session(ewSessionTblAdvSrch & "_x_reference")
    x_estab = Session(ewSessionTblAdvSrch & "_x_estab")
    x_School = Session(ewSessionTblAdvSrch & "_x_School")
    x_a = Session(ewSessionTblAdvSrch & "_x_a")
    x_b = Session(ewSessionTblAdvSrch & "_x_b")
    x_c = Session(ewSessionTblAdvSrch & "_x_c")
    x_d = Session(ewSessionTblAdvSrch & "_x_d")
    x_e = Session(ewSessionTblAdvSrch & "_x_e")
    x_f = Session(ewSessionTblAdvSrch & "_x_f")
    x_g = Session(ewSessionTblAdvSrch & "_x_g")
    x_h = Session(ewSessionTblAdvSrch & "_x_h")
    psearch = Session(ewSessionTblBasicSrch)
    psearchtype = Session(ewSessionTblBasicSrchType)
End Sub

'-------------------------------------------------------------------------------
' Function SetUpSortOrder
' - Set up Sort parameters based on Sort Links clicked
' - Variables setup: sOrderBy, Session(TblOrderBy), Session(Tbl_Field_Sort)

Sub SetUpSortOrder()
    Dim sOrder, sSortField, sLastSort, sThisSort
    Dim bCtrl

    ' Check for an Order parameter
    If Request.QueryString("order").Count > 0 Then
        sOrder = Request.QueryString("order")

        ' Field [ID]
        If sOrder = "ID" Then
            sSortField = "[ID]"
            sLastSort = Session(ewSessionTblSort & "_x_ID")
            If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
            Session(ewSessionTblSort & "_x_ID") = sThisSort
        Else
            If Session(ewSessionTblSort & "_x_ID") <> "" Then Session(ewSessionTblSort & "_x_ID") = ""
        End If

        ' Field [reference]
        If sOrder = "reference" Then
            sSortField = "[reference]"
            sLastSort = Session(ewSessionTblSort & "_x_reference")
            If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
            Session(ewSessionTblSort & "_x_reference") = sThisSort
        Else
            If Session(ewSessionTblSort & "_x_reference") <> "" Then Session(ewSessionTblSort & "_x_reference") = ""
        End If

        ' Field [estab]
        If sOrder = "estab" Then
            sSortField = "[estab]"
            sLastSort = Session(ewSessionTblSort & "_x_estab")
            If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
            Session(ewSessionTblSort & "_x_estab") = sThisSort
        Else
            If Session(ewSessionTblSort & "_x_estab") <> "" Then Session(ewSessionTblSort & "_x_estab") = ""
        End If

        ' Field [School]
        If sOrder = "School" Then
            sSortField = "[School]"
            sLastSort = Session(ewSessionTblSort & "_x_School")
            If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
            Session(ewSessionTblSort & "_x_School") = sThisSort
        Else
            If Session(ewSessionTblSort & "_x_School") <> "" Then Session(ewSessionTblSort & "_x_School") = ""
        End If

        ' Field [a]
        If sOrder = "a" Then
            sSortField = "[a]"
            sLastSort = Session(ewSessionTblSort & "_x_a")
            If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
            Session(ewSessionTblSort & "_x_a") = sThisSort
        Else
            If Session(ewSessionTblSort & "_x_a") <> "" Then Session(ewSessionTblSort & "_x_a") = ""
        End If

        ' Field [b]
        If sOrder = "b" Then
            sSortField = "[b]"
            sLastSort = Session(ewSessionTblSort & "_x_b")
            If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
            Session(ewSessionTblSort & "_x_b") = sThisSort
        Else
            If Session(ewSessionTblSort & "_x_b") <> "" Then Session(ewSessionTblSort & "_x_b") = ""
        End If

        ' Field [c]
        If sOrder = "c" Then
            sSortField = "[c]"
            sLastSort = Session(ewSessionTblSort & "_x_c")
            If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
            Session(ewSessionTblSort & "_x_c") = sThisSort
        Else
            If Session(ewSessionTblSort & "_x_c") <> "" Then Session(ewSessionTblSort & "_x_c") = ""
        End If

        ' Field [d]
        If sOrder = "d" Then
            sSortField = "[d]"
            sLastSort = Session(ewSessionTblSort & "_x_d")
            If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
            Session(ewSessionTblSort & "_x_d") = sThisSort
        Else
            If Session(ewSessionTblSort & "_x_d") <> "" Then Session(ewSessionTblSort & "_x_d") = ""
        End If

        ' Field [e]
        If sOrder = "e" Then
            sSortField = "[e]"
            sLastSort = Session(ewSessionTblSort & "_x_e")
            If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
            Session(ewSessionTblSort & "_x_e") = sThisSort
        Else
            If Session(ewSessionTblSort & "_x_e") <> "" Then Session(ewSessionTblSort & "_x_e") = ""
        End If

        ' Field [f]
        If sOrder = "f" Then
            sSortField = "[f]"
            sLastSort = Session(ewSessionTblSort & "_x_f")
            If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
            Session(ewSessionTblSort & "_x_f") = sThisSort
        Else
            If Session(ewSessionTblSort & "_x_f") <> "" Then Session(ewSessionTblSort & "_x_f") = ""
        End If

        ' Field [g]
        If sOrder = "g" Then
            sSortField = "[g]"
            sLastSort = Session(ewSessionTblSort & "_x_g")
            If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
            Session(ewSessionTblSort & "_x_g") = sThisSort
        Else
            If Session(ewSessionTblSort & "_x_g") <> "" Then Session(ewSessionTblSort & "_x_g") = ""
        End If

        ' Field [h]
        If sOrder = "h" Then
            sSortField = "[h]"
            sLastSort = Session(ewSessionTblSort & "_x_h")
            If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
            Session(ewSessionTblSort & "_x_h") = sThisSort
        Else
            If Session(ewSessionTblSort & "_x_h") <> "" Then Session(ewSessionTblSort & "_x_h") = ""
        End If
        Session(ewSessionTblOrderBy) = sSortField & " " & sThisSort
        Session(ewSessionTblStartRec) = 1
    End If
    sOrderBy = Session(ewSessionTblOrderBy)
    If sOrderBy = "" Then
        If ewSqlOrderBy <> "" And ewSqlOrderBySessions <> "" Then
            sOrderBy = ewSqlOrderBy
            Session(ewSessionTblOrderBy) = sOrderBy
            Dim arOrderBy, i
            arOrderBy = Split(ewSqlOrderBySessions, ",")
            For i = 0 to UBound(arOrderBy)\2
                Session(ewSessionTblSort & "_" & arOrderBy(i*2)) = arOrderBy(i*2+1)
            Next
        End If
    End If
End Sub

'-------------------------------------------------------------------------------
' Function SetUpStartRec
' - Set up Starting Record parameters based on Pager Navigation
' - Variables setup: nStartRec

Sub SetUpStartRec()
    Dim nPageNo

    ' Check for a START parameter
    If Request.QueryString(ewTblStartRec).Count > 0 Then
        nStartRec = Request.QueryString(ewTblStartRec)
        Session(ewSessionTblStartRec) = nStartRec
    ElseIf Request.QueryString("pageno").Count > 0 Then
        nPageNo = Request.QueryString("pageno")
        If IsNumeric(nPageNo) Then
            nStartRec = (nPageNo-1)*nDisplayRecs+1
            If nStartRec <= 0 Then
                nStartRec = 1
            ElseIf nStartRec >= ((nTotalRecs-1)\nDisplayRecs)*nDisplayRecs+1 Then
                nStartRec = ((nTotalRecs-1)\nDisplayRecs)*nDisplayRecs+1
            End If
            Session(ewSessionTblStartRec) = nStartRec
        Else
            nStartRec = Session(ewSessionTblStartRec)
        End If
    Else
        nStartRec = Session(ewSessionTblStartRec)
    End If

    ' Check if correct start record counter
    If Not IsNumeric(nStartRec) Or nStartRec = "" Then ' Avoid invalid start record counter
        nStartRec = 1 ' Reset start record counter
        Session(ewSessionTblStartRec) = nStartRec
    ElseIf CLng(nStartRec) > CLng(nTotalRecs) Then ' Avoid starting record > total records
        nStartRec = ((nTotalRecs-1)\nDisplayRecs)*nDisplayRecs+1 ' point to last page first record
        Session(ewSessionTblStartRec) = nStartRec
    End If
End Sub

'-------------------------------------------------------------------------------
' Function ResetCmd
' - Clear list page parameters
' - RESET: reset search parameters
' - RESETALL: reset search & master/detail parameters
' - RESETSORT: reset sort parameters

Sub ResetCmd()
    Dim sCmd

    ' Get Reset Cmd
    If Request.QueryString("cmd").Count > 0 Then
        sCmd = Request.QueryString("cmd")

        ' Reset Search Criteria
        If LCase(sCmd) = "reset" Then
            Call ResetSearch()

        ' Reset Search Criteria & Session Keys
        ElseIf LCase(sCmd) = "resetall" Then
            Call ResetSearch()

        ' Reset Sort Criteria
        ElseIf LCase(sCmd) = "resetsort" Then
            sOrderBy = ""
            Session(ewSessionTblOrderBy) = sOrderBy
            If Session(ewSessionTblSort & "_x_ID") <> "" Then Session(ewSessionTblSort & "_x_ID") = ""
            If Session(ewSessionTblSort & "_x_reference") <> "" Then Session(ewSessionTblSort & "_x_reference") = ""
            If Session(ewSessionTblSort & "_x_estab") <> "" Then Session(ewSessionTblSort & "_x_estab") = ""
            If Session(ewSessionTblSort & "_x_School") <> "" Then Session(ewSessionTblSort & "_x_School") = ""
            If Session(ewSessionTblSort & "_x_a") <> "" Then Session(ewSessionTblSort & "_x_a") = ""
            If Session(ewSessionTblSort & "_x_b") <> "" Then Session(ewSessionTblSort & "_x_b") = ""
            If Session(ewSessionTblSort & "_x_c") <> "" Then Session(ewSessionTblSort & "_x_c") = ""
            If Session(ewSessionTblSort & "_x_d") <> "" Then Session(ewSessionTblSort & "_x_d") = ""
            If Session(ewSessionTblSort & "_x_e") <> "" Then Session(ewSessionTblSort & "_x_e") = ""
            If Session(ewSessionTblSort & "_x_f") <> "" Then Session(ewSessionTblSort & "_x_f") = ""
            If Session(ewSessionTblSort & "_x_g") <> "" Then Session(ewSessionTblSort & "_x_g") = ""
            If Session(ewSessionTblSort & "_x_h") <> "" Then Session(ewSessionTblSort & "_x_h") = ""
        End If

        ' Reset Start Position (Reset Command)
        nStartRec = 1
        Session(ewSessionTblStartRec) = nStartRec
    End If
End Sub

'-------------------------------------------------------------------------------
' Function ExportData
' - Export Data in Xml or Csv format

Sub ExportData(sExport, sSql)
    Dim oXmlDoc, oXmlTbl, oXmlRec, oXmlFld
    Dim sCsvStr
    Dim rs

    ' Set up Record Set
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.CursorLocation = 2
    rs.Open sSql, conn, 1, 2
    nTotalRecs = rs.RecordCount
    nStartRec = 1
    SetUpStartRec() ' Set Up Start Record Position
    If sExport = "xml" Then
        Set oXmlDoc = Server.CreateObject("MSXML.DOMDocument")
        Set oXmlTbl = oXmlDoc.createElement("table")
    End If
    If sExport = "csv" Then
        sCsvStr = sCsvStr & """ID""" & ","
        sCsvStr = sCsvStr & """reference""" & ","
        sCsvStr = sCsvStr & """estab""" & ","
        sCsvStr = sCsvStr & """School""" & ","
        sCsvStr = sCsvStr & """a""" & ","
        sCsvStr = sCsvStr & """b""" & ","
        sCsvStr = sCsvStr & """c""" & ","
        sCsvStr = sCsvStr & """d""" & ","
        sCsvStr = sCsvStr & """e""" & ","
        sCsvStr = sCsvStr & """f""" & ","
        sCsvStr = sCsvStr & """g""" & ","
        sCsvStr = sCsvStr & """h""" & ","
        sCsvStr = Left(sCsvStr, Len(sCsvStr)-1) ' Remove last comma
        sCsvStr = sCsvStr & vbCrLf
    End If

    ' Set the last record to display
    If nDisplayRecs < 0 Then
        nStopRec = nTotalRecs
    Else
        nStopRec = nStartRec + nDisplayRecs - 1
    End If

    ' Move to first record directly for performance reason
    nRecCount = nStartRec - 1
    If Not rs.Eof Then
        rs.MoveFirst
        rs.Move nStartRec - 1
    End If
    nRecActual = 0
    Do While (Not rs.Eof) And (nRecCount < nStopRec)
        nRecCount = nRecCount + 1
        If CLng(nRecCount) >= CLng(nStartRec) Then
            nRecActual = nRecActual + 1
            x_ID = rs("ID")
            x_reference = rs("reference")
            x_estab = rs("estab")
            x_School = rs("School")
            x_a = rs("a")
            x_b = rs("b")
            x_c = rs("c")
            x_d = rs("d")
            x_e = rs("e")
            x_f = rs("f")
            x_g = rs("g")
            x_h = rs("h")
            If sExport = "xml" Then
                Set oXmlRec = oXmlDoc.createElement("record")
                Call oXmlTbl.appendChild(oXmlRec)

                ' Field ID
                Set oXmlFld = oXmlDoc.createElement("ID")
                sTmp = x_ID
                if IsNull(sTmp) then sTmp = "<Null>"
                oXmlFld.Text = sTmp
                Call oXmlRec.AppendChild(oXmlFld)

                ' Field reference
                Set oXmlFld = oXmlDoc.createElement("reference")
                sTmp = x_reference
                if IsNull(sTmp) then sTmp = "<Null>"
                oXmlFld.Text = sTmp
                Call oXmlRec.AppendChild(oXmlFld)

                ' Field estab
                Set oXmlFld = oXmlDoc.createElement("estab")
                sTmp = x_estab
                if IsNull(sTmp) then sTmp = "<Null>"
                oXmlFld.Text = sTmp
                Call oXmlRec.AppendChild(oXmlFld)

                ' Field School
                Set oXmlFld = oXmlDoc.createElement("School")
                sTmp = x_School
                if IsNull(sTmp) then sTmp = "<Null>"
                oXmlFld.Text = sTmp
                Call oXmlRec.AppendChild(oXmlFld)

                ' Field a
                Set oXmlFld = oXmlDoc.createElement("a")
                sTmp = x_a
                if IsNull(sTmp) then sTmp = "<Null>"
                oXmlFld.Text = sTmp
                Call oXmlRec.AppendChild(oXmlFld)

                ' Field b
                Set oXmlFld = oXmlDoc.createElement("b")
                sTmp = x_b
                if IsNull(sTmp) then sTmp = "<Null>"
                oXmlFld.Text = sTmp
      &n