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

 

[MerlinB]

You should try adding a Recordset DTC and a Grid DTC to the page. Make sure the Recordset DTC is set to NOT auto-open the recordset on page-open (as you need to create the SQL first). This is a tick-box option on one of the property pages.

Construct an SQL clause in the Recordset DTC, that contains all of the columns that you want, and either no WHERE clause or a dummy one, or one containing parameters (if this works for Oracle).

parameter example:
SELECT A,B,C FROM USER.TABLE_1 WHERE D = ?
your code will need to replace the ? as follows
rsTable1.setParameter 1, &quot;AValue&quot;
rsTable1.Open

If your selection criteria is complex, then construct the SQL in your code as before, but assign it to the Recordset DTC:
rsTable1.sql = &quot;SELECT * FROM ***** where ***** = '&quot; & ****** & &quot;' and **** = '&quot; & **** & &quot;'&quot;
rsTable1.open

The Grid DTC (or just the NavBar DTC) can be bound to the Recordset DTC. It 'knows' about the required columns, so you just have to pick them from the list in the Property sheet. When the recordset is empty or closed (ie first time the page is displayed) the grid will not show. Enter criteria and press GO - and the SQL gets constructed and the RecordsetDTC is opened - and the Grid now populates.

Any use for you?

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)