The below ASP page should pass a string to a Oracle stored preocdure then using the returned recordset display the data using paging, simple. The IN_SQL string would be built up by a user selection but whilst I'm developing it's hardcoded in the ASP page.
I am getting a syntax error in {call...} ODBC ESCAPE on the line that opens the recordset.
Can anyone assist please ?
<%@ LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>
<!-- #include file="ADOVBS.INC" -->
<%
Dim IN_SQL
'Set how many records per page we want
Const NumPerPage = 5
'Retrieve what page we're currently on
Dim CurPage
If Request.QueryString("CurPage"
= "" then
CurPage = 1 'We're on the first page
Else
CurPage = Request.QueryString("CurPage"
End If
Dim conn
Dim strconnection
strconnection = "Provider=MSDAORA.1;Password=reading;User ID=s27287;Data Source=trackdev"
Set conn = Server.CreateObject("ADODB.Connection"
conn.Open strconnection
'Explicitly Create a recordset object
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset"
'Set the cursor location property
rs.CursorLocation = adUseclient
'Set the cache size = to the # of records/page
rs.CacheSize = NumPerPage
'Open our recordset
Dim strSQL
IN_SQL = "select claim_number, client_number from summaries"
strSQL= "PACKAGE_name.PROCEDURE_name" & IN_SQL
rs.Open strSQL, Conn, adOpenKeyset, adLockReadOnly, adCmdStoredProc
rs.MoveFirst
rs.PageSize = NumPerPage
'Get the max number of pages
Dim TotalPages
TotalPages = rs.PageCount
'Set the absolute page
rs.AbsolutePage = CurPage
'Counting variable for our recordset
Dim count
%>
<HTML>
<BODY>
<BR>
<table border=1 bgcolor=lightblue>
<tr>
<th> claim_number </th> <th> client_number </th>
</tr>
<%
'Set Count equal to zero
Count = 0
Do While Not rs.EOF And Count < rs.PageSize
%> <tr><td><%
Response.Write(rs("claim_number") %> </td><td> <% Response.Write(rs("client_number") %> </td></tr><%
Count = Count + 1
rs.MoveNext
Loop
%></table><%
'Print out the current page # / total pages
Response.Write("Page " & CurPage & " of " & TotalPages & "<P>"
'Display Next / Prev buttons
if CurPage > 1 then
'We are not at the beginning, show the prev button
Response.Write("<INPUT TYPE=BUTTON VALUE=PREV ONCLICK=""document.location.href='paging.asp?curpage=" & curpage - 1 & "';"">"
End If
if CInt(CurPage) <> CInt(TotalPages) then
'We are not at the end, show a next button
Response.Write("<INPUT TYPE=BUTTON VALUE=NEXT ONCLICK=""document.location.href='paging.asp?curpage=" & curpage + 1 & "';"">"
End If
%>
</BODY>
</HTML>
I am getting a syntax error in {call...} ODBC ESCAPE on the line that opens the recordset.
Can anyone assist please ?
<%@ LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>
<!-- #include file="ADOVBS.INC" -->
<%
Dim IN_SQL
'Set how many records per page we want
Const NumPerPage = 5
'Retrieve what page we're currently on
Dim CurPage
If Request.QueryString("CurPage"
= "" thenCurPage = 1 'We're on the first page
Else
CurPage = Request.QueryString("CurPage"
End If
Dim conn
Dim strconnection
strconnection = "Provider=MSDAORA.1;Password=reading;User ID=s27287;Data Source=trackdev"
Set conn = Server.CreateObject("ADODB.Connection"
conn.Open strconnection
'Explicitly Create a recordset object
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset"
'Set the cursor location property
rs.CursorLocation = adUseclient
'Set the cache size = to the # of records/page
rs.CacheSize = NumPerPage
'Open our recordset
Dim strSQL
IN_SQL = "select claim_number, client_number from summaries"
strSQL= "PACKAGE_name.PROCEDURE_name" & IN_SQL
rs.Open strSQL, Conn, adOpenKeyset, adLockReadOnly, adCmdStoredProc
rs.MoveFirst
rs.PageSize = NumPerPage
'Get the max number of pages
Dim TotalPages
TotalPages = rs.PageCount
'Set the absolute page
rs.AbsolutePage = CurPage
'Counting variable for our recordset
Dim count
%>
<HTML>
<BODY>
<BR>
<table border=1 bgcolor=lightblue>
<tr>
<th> claim_number </th> <th> client_number </th>
</tr>
<%
'Set Count equal to zero
Count = 0
Do While Not rs.EOF And Count < rs.PageSize
%> <tr><td><%
Response.Write(rs("claim_number"
) %> </td><td> <% Response.Write(rs("client_number") %> </td></tr><%Count = Count + 1
rs.MoveNext
Loop
%></table><%
'Print out the current page # / total pages
Response.Write("Page " & CurPage & " of " & TotalPages & "<P>"
'Display Next / Prev buttons
if CurPage > 1 then
'We are not at the beginning, show the prev button
Response.Write("<INPUT TYPE=BUTTON VALUE=PREV ONCLICK=""document.location.href='paging.asp?curpage=" & curpage - 1 & "';"">"
End If
if CInt(CurPage) <> CInt(TotalPages) then
'We are not at the end, show a next button
Response.Write("<INPUT TYPE=BUTTON VALUE=NEXT ONCLICK=""document.location.href='paging.asp?curpage=" & curpage + 1 & "';"">"
End If
%>
</BODY>
</HTML>