Have an ADO Recordset populated wit

[vza]

Have an ADO Recordset populated with data from an oracle table.

I am trying to implement the find method of the recordset to find a row.
I recieve a error upon the execution of the find statement in my code which states:

"Runtime Error '-2147217879 (80040e29)
Rowset does not support scrolling backward"

I tried to correct this eror in VB and was able to do so by making my recordset cursor type adOpenStatic. (within the Open method call) This was unable to fix my VBScript code however...any ideas??

here is my code...I can't seem to figure out what is wrong. Any help would be greatly appreciated.

(VB6....altered for VBscript code)

        Dim RsFind As New ADODB.Recordset
        Dim NTAGNumber
        
        NTAGNumber = NTAGText.Text
    ' Open RecordSets
        RsFind.Open "Select * From PI_GIS", Cn
        If NTAGText.Text <> &quot;&quot; Then
             RsFind.Find &quot;Field1 = 'NTAGNumber'&quot;
                If RsFind.EOF = True Then
                    TAGText.Text = RsFind.Fields(&quot;Field2&quot;).Value
                    DESText.Text = RsFind.Fields(&quot;Field3&quot;).Value
                    LOCText.Text = RsFind.Fields(&quot;Field4&quot;).Value
                    PointText.Text = RsFind.Fields(&quot;Field5&quot;).Value
                End If
        Else
            MsgBox &quot;Specified values do not exist within the Table. Please re-insert.&quot;, vbOKOnly, &quot;Error&quot;
        End If

Thanks
-vza

 

[Veep]

I would skip the recordset object and the find method. Just use the connection object and criteria in your SQL statement:
set conn=createobject("ADODB.Connection&quot
conn.open YourConnectionString
strSql="SELECT * FROM PI_GIS WHERE Field1='NTAGNumber'"
set rs=conn.execute(strSql)
If not rs.eof then
'do stuff
ELSE
'do other stuff
End if
set rs=nothing
conn.close
set conn=nothing

 

[gjconely]

You will notice that alot of high-end dbm systems do not allow things such as .move, .previous, .absoluteposition - This is common with ORACLE, SQL, DBiii, etc...


Greg Conely

http://www.ImageMentor.net

 

[dilettante]

Hmm... doesn't look like VBScript to me:

Dim RsFind As New ADODB.Recordset

A lot of what you have here looks like VB instead of VBScript.

You need to open the Recordset with something other than the default

adForwardOnly

cursor here as well.

You also need a current row position before doing a

.Find

call. A

.MoveFirst

will take care of this as long as the Recordset isn't empty (be sure to check for both

.BOF

and

.EOF

being

True

).

Also, if

.EOF

is

True

after the

.Find

, you aren't going to have much luck pulling fields from the "current row."

I agree with Veep in so far as only returning the rows you need from the database. I'm not a big fan of

<conn>.Execute

however unless all you want to do is display stuff that comes back. You end up with a woefully crippled Recordset if there is value in performing significant client manipulation of the data. The end result of this approach is repeated round-tripping to the database for stuff at different points - but it depends on what you need to do.

 

[vza]

Thanks for the responses.....
Veep I have tried to implement your method but have fun into a problem....

I decided to set the field names and values to variables for many can be chosen to find on the vbScript HTML page.
I put the variables into the Cn.Execute string with everything working fine. When I test the Recordset.EOF property it is always true, even though the variable values are correct and the Cn.Execute line runs with no problems...is there something I am missing? Any help would be greatly appreciated.
Here is my code so far:

     Dim RsFind
     Dim FieldValue
     Dim Field
     Dim StrSQL

' Open RecordSets
     Set RsFind = CreateObject("ADODB.Recordset")

*** FieldValue and Field Variables set here*****

                    *******
                    *******
                    *******  

     StrSQL = "Select * From PI_GIS Where '" & Field & "' = '" & FieldValue & "'"
        Set RsFind = Cn.Execute(StrSQL)
        Debug.Print Field  '**Displays Correct Field**'
        Debug.Print FieldValue  '**Displays Correct Value**'
     
     If Not RsFind.EOF Then 'ALWAYS EQUALS TRUE!!!'
         NTAGText.Text = RsFind.Fields("NTAG").Value
         TAGText.Text = RsFind.Fields("TAG").Value
         DESText.Text = RsFind.Fields("DESCRIPTOR").Value
         LOCText.Text = RsFind.Fields("LOCATION2").Value
         PointText.Text = RsFind.Fields("POINTID").Value
     Else
         Exit Sub
     End If

Thanks
-vza