Oracle connections

[outra9e]

Hi guys wonder if anyone can give some advice.

As you probably all know, the chances of Oracle supporting me on this issue are about miniscule to nowt!

I can connect to an oracle data source using an ASP and can retrieve, add, update, delete and search the records.

I have a problem, in that if I return a search that has multiple returns, I cannot make the page browsable, I can create a page that lets me browse the records, but if I try to duplicate this on a search results page it doesnt work.

Here is my connection code to return a search:

<%
Dim objConn, objRS, sOraUserId
'declare variables for holding submitted values
dim sPurchOrder, sPos
'get values from submitted form
sPurchOrder = Request.Form(&quot;Purch_Order&quot
sPos = Request.Form(&quot;Pos&quot

Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
objConn.Open &quot;Provider=msdaora;User ID=****;Password=****;Data Source=****&quot;
Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot
objRS.CursorLocation = adUseServer
objRS.CursorType = adOpenForwardOnly
objRS.LockType = adLockOptimistic

objRS.Open &quot;SELECT * FROM ***** where ***** = '&quot; & ****** & &quot;' and **** = '&quot; & **** & &quot;'&quot;, objconn, , , adCmdText
%>

Now all I need is code to make the page browsable, eg nav buttons...

Can anyone help?

Cheers

 

[Bastien]

Hi
Can I get a clearer explanation of what you want the page to do?

Just what do mean by browsable? Do you want some of the items in the record set to act as hyperlinks? Do you have a certain number of results that you want to see on each page?

Need more detail... Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[outra9e]

Basically, there is a search page which searches an oracle data source and posts to an ASP.

The ASP needs to return the results, one record per page of the recordset.

The problem I have is in the following line...

If Not(objRS.EOF) Then objRS.AbsolutePage = intCurrentPage

With this line in I get the following error...

ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

If I take this line out, the results are returned, however I cannot browse through the returned results, as the recordset requires a current page!!!!!

Very infuriating!! I can't win!!!

Any ideas?

Following, is the entire code, if you want to have a look over it...

Cheers


<%@ LANGUAGE=&quot;VBSCRIPT&quot; %>
<% ' Listing 17.1 Paging through a Recordset Part 1: Declaration
%>
<!--#include file=&quot;adovbs.inc&quot;-->
<%
Const intPageSize = 1
Dim intCurrentPage, objConn, objRS, strQ
Dim intTotalPages, intI
dim sPurchOrder
'get values from submitted form
sPurchOrder = Request.Form(&quot;Purch_Order&quot

' Listing 17.2 Paging Part 2: Setting the Current Page Number
If Request.ServerVariables(&quot;CONTENT_LENGTH&quot = 0 Then
intCurrentPage = 1
Else
intCurrentPage = CInt(Request.Form(&quot;CurrentPage&quot)
Select Case Request.Form(&quot;Submit&quot
Case &quot;Previous&quot;
intCurrentPage = intCurrentPage - 1
Case &quot;Next&quot;
intCurrentPage = intCurrentPage + 1
End Select
End If

' Listing 17.3[em]Paging[md]Part 3: Preparing the Recordset for Paging
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
objConn.Open &quot;Provider=msdaora;User ID=****;Password=****;Data Source=****&quot;

Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot
objRS.CursorLocation = adUseClient
objRS.CursorType = adOpenStatic
objRS.CacheSize = intPageSize

' Listing 17.4[em]Paging[md]Part 4: Opening the Recordset
strQ = &quot;SELECT * FROM **** where **** = '&quot; & **** & &quot;'&quot;
strQ = strQ & &quot;ORDER BY ****&quot;
objRS.Open strQ, objConn, , , adCmdText

' Listing 17.5 Paging Part 5: Setting the Recordset cursor
objRS.PageSize = intPageSize
If Not(objRS.EOF) Then objRS.AbsolutePage = intCurrentPage

' Listing 17.6 Paging Part 6: Sending Database Data
intTotalPages = objRS.PageCount
%>

<%
For intI = 1 to objRS.PageSize%>
<TABLE>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
<TR><TD>****</TD><TD><%=objRS(&quot;****&quot%></TD></TR>
</TABLE>
<br>
<%objRS.MoveNext
If objRS.EOF Then Exit For
Next

objRS.Close : objConn.Close
Set objRS = Nothing : Set objConn = Nothing
%>

<BR>
Page <%= intCurrentPage %> of <%= intTotalPages %><P>

<% ' Listing 17.7 Paging Part 7: Providing Web Form for Paging %>
<FORM ACTION=&quot;<%= Request.ServerVariables(&quot;SCRIPT_NAME&quot %>&quot; METHOD=&quot;POST&quot; id=form1 name=form1>
<INPUT TYPE=&quot;Hidden&quot; NAME=&quot;CurrentPage&quot;
VALUE=&quot;<%= intCurrentPage%>&quot;>
<%
If intCurrentPage > 1 Then %>
<INPUT TYPE=&quot;Submit&quot; NAME=&quot;Submit&quot; VALUE=&quot;Previous&quot;>
<% End If
If intCurrentPage <> intTotalPages Then %>
<INPUT TYPE=&quot;Submit&quot; NAME=&quot;Submit&quot; VALUE=&quot;Next&quot;>
<% End If %>
</FORM>
</BODY></HTML>