Hey all,
So I have an asp/sql issue. I'm building a staff search function and am having a strange issue arise when passing letters via the querystring to a page that searches lastname based on the value in the query string. I am passing the letter, grabing it from the query string and creating my SQL properly, but there are no results. If I copy the outputted query and run it against my db, it works as it should. Any help here would be greatly appreciated.
So I have an asp/sql issue. I'm building a staff search function and am having a strange issue arise when passing letters via the querystring to a page that searches lastname based on the value in the query string. I am passing the letter, grabing it from the query string and creating my SQL properly, but there are no results. If I copy the outputted query and run it against my db, it works as it should. Any help here would be greatly appreciated.
Code:
<%' Declare all of the variables that will be used in the page.
Dim objConn ' ADO Connection Object
Dim objRst ' ADO Recordset Object
Dim strYear ' The year that we are searching for.
Dim Sql ' Our SQL statement
Dim intPageCount ' The number of pages in the recordset.
Dim intRecordCount ' The number of records in the recordset.
Dim intPage ' The current page that we are on.
Dim intRecord ' Counter used to iterate through the recordset.
Dim intStart ' The record that we are starting on.
Dim intFinish ' The record that we are finishing on.
dim alpha
alpha = request.QueryString("alpha")
response.write(alpha)
' Check to see if there is value in the NAV querystring. If there
' is, we know that the client is using the Next and/or Prev hyperlinks
' to navigate the recordset.
If Request.QueryString("NAV") = "" Then
intPage = 1
Else
intPage = Request.QueryString("NAV")
End If
SET objConn = server.CreateObject("ADODB.Connection")
objConn.Provider = "Microsoft.Jet.OLEDB.4.0"
objConn.Open = "C:\inetpub\[URL unfurl="true"]wwwroot\NEW_ETD\staff\directory.mdb"[/URL]
Sql = "select * from staff where lastname like '" & alpha & "*' order by lastname ASC;"
response.write("<br />" & Sql)
' Create you Recordset Object
Set objRst = Server.CreateObject("ADODB.Recordset")
' The CursorLocation and the CursorType must be set as they are here
' in order for Recordset Paging to work properly.
objRst.CursorLocation = 3 'adUseClient
objRst.CursorType = 3 'adOpenStatic
objRst.ActiveConnection = objConn
' Open the recordset.
objRst.Open Sql
'objRst.Open Sql, objConn
' Set the PageSize, CacheSize, and populate the intPageCount and
' intRecordCount variables.
objRst.PageSize = 15
' The cachesize property sets the number of records that will be cached
' locally in memory.
'objRst.CacheSize = 0 'objRst.PageSize
intPageCount = objRst.PageCount
intRecordCount = objRst.RecordCount
' Now you must double check to make sure that you are not before the start
' or beyond end of the recordset. If you are beyond the end, set
' the current page equal to the last page of the recordset. If you are
' before the start, set the current page equal to the start of the recordset.
If CInt(intPage) > CInt(intPageCount) Then intPage = intPageCount
If CInt(intPage) <= 0 Then intPage = 1
' Make sure that the recordset is not empty. If it is not, then set the
' AbsolutePage property and populate the intStart and the intFinish variables.
If intRecordCount > 0 Then
objRst.AbsolutePage = intPage
intStart = objRst.AbsolutePosition
If CInt(intPage) = CInt(intPageCount) Then
intFinish = intRecordCount
Else
intFinish = intStart + (objRst.PageSize - 1)
End if
End If
%>
<%If intRecordCount > 0 Then
' Display the record that you are starting on and the record
' that you are finishing on for this page by writing out the
' values in the intStart and the intFinish variables.
%>
<h2>Staff by Branch Results</h2><br />
<p>Results <%=intStart%> - <%=intFinish%> of <%=intRecordCount%> for <em>"<%=alpha%>"</em>.</p>
<%
' Check to see if the current page is greater than the first page
' in the recordset. If it is, then add a "Previous" link.
If cInt(intPage) > 1 Then
%>
<a href="staffbybranchresults.asp?NAV=<%=intPage - 1%>&alpha=<%=alpha%>" class="redlink"><< Prev </a>
<%End IF%>
<%
' Check to see if the current page is less than the last page
' in the recordset. If it is, then add a "Next" link.
If cInt(intPage) < cInt(intPageCount) Then
%>
<a href="staffbybranchresults.asp?NAV=<%=intPage + 1%>&alpha=<%=alpha%>" class="redlink">Next >></a>
<a href="export_branch_excel.asp?alpha=<%=alpha%>" target="_blank" class="redlink">Printer Friendly</a>
<%End If%>
<%
' Iterate through the recordset until we reach the end of the page
' or the last record in the recordset.
dim rowcount
rowcount = 1
response.Write("<table cellpadding=""3"" cellspacing=""0"" border=""0"" style=""border:1px solid black;"">")
response.Write("<tr>")
response.Write("<td width=""12%"" align=""center"" bgcolor=""#4f0135"" style=""color:white;"">First Name</td>")
response.Write("<td width=""12%"" align=""center"" bgcolor=""#4f0135"" style=""color:white;"">Last Name</td>")
response.Write("<td width=""20%"" align=""center"" bgcolor=""#4f0135"" style=""color:white;"">Position</td>")
response.Write("<td width=""15%"" align=""center"" bgcolor=""#4f0135"" style=""color:white;"">Phone Number</td>")
response.Write("<td width=""21%"" align=""center"" bgcolor=""#4f0135"" style=""color:white;"">Branch / Region</td>")
response.write("<td width=""20%"" align=""center"" bgcolor=""#4f0135"" style=""color:white;"">Unit</td>")
response.Write ("</tr>")
For intRecord = 1 to objRst.PageSize
if(rowcount = 1) then
response.Write("<tr class=""evenRow"">")
else
response.Write("<tr class=""oddRow"">")
end if
response.Write("<td>" & objRst("firstname") & "</td>")
response.Write("<td>" & objRst("lastname") & "</td>")
response.Write("<td>" & objRst("position") & "</td>")
response.Write("<td>" & objRst("phonenumber") & "</td>")
response.Write("<td>" & objRst("branchregion") & "</td>")
response.Write("<td>" & objRst("unit") & "</td>")
response.Write ("</tr>")
if(rowcount = 1) then
rowcount = 2
else
rowcount = 1
end if
objRst.MoveNext
If objRst.EOF Then Exit for
Next
response.write("</table><br />")
response.Write("<p>Search by last name <br />")
response.write("<a href=""staffsearch.asp?alpha='a'"" class=""redlink"">A</a> ")
response.write("<a href=""staffsearch.asp?alpha='b'"" class=""redlink"">B</a> ")
response.write("<a href=""staffsearch.asp?alpha='c'"" class=""redlink"">C</a> ")
response.write("<a href=""staffsearch.asp?alpha=d"" class=""redlink"">D</a> ")
response.write("<a href=""staffsearch.asp?alpha=e"" class=""redlink"">E</a> ")
response.write("<a href=""staffsearch.asp?alpha=f"" class=""redlink"">F</a> ")
response.write("<a href=""staffsearch.asp?alpha=g"" class=""redlink"">G</a> ")
response.write("<a href=""staffsearch.asp?alpha=h"" class=""redlink"">H</a> ")
response.write("<a href=""staffsearch.asp?alpha=i"" class=""redlink"">I</a> ")
response.write("<a href=""staffsearch.asp?alpha=j"" class=""redlink"">J</a> ")
response.write("<a href=""staffsearch.asp?alpha=k"" class=""redlink"">K</a> ")
response.write("<a href=""staffsearch.asp?alpha=l"" class=""redlink"">L</a> ")
response.write("<a href=""staffsearch.asp?alpha=m"" class=""redlink"">M</a> ")
response.write("<a href=""staffsearch.asp?alpha=n"" class=""redlink"">N</a> ")
response.write("<a href=""staffsearch.asp?alpha=o"" class=""redlink"">O</a> ")
response.write("<a href=""staffsearch.asp?alpha=p"" class=""redlink"">P</a> ")
response.write("<a href=""staffsearch.asp?alpha=q"" class=""redlink"">Q</a> ")
response.write("<a href=""staffsearch.asp?alpha=r"" class=""redlink"">R</a> ")
response.write("<a href=""staffsearch.asp?alpha=s"" class=""redlink"">S</a> ")
response.write("<a href=""staffsearch.asp?alpha=t"" class=""redlink"">T</a> ")
response.write("<a href=""staffsearch.asp?alpha=u"" class=""redlink"">U</a> ")
response.write("<a href=""staffsearch.asp?alpha=v"" class=""redlink"">V</a> ")
response.write("<a href=""staffsearch.asp?alpha=w"" class=""redlink"">W</a> ")
response.write("<a href=""staffsearch.asp?alpha=x"" class=""redlink"">X</a> ")
response.write("<a href=""staffsearch.asp?alpha=y"" class=""redlink"">Y</a> ")
response.write("<a href=""staffsearch.asp?alpha=z"" class=""redlink"">Z</a></p>")
%>
<%End If%>