Oralce and VB6

[abhishek.ankur]

Hi all
I have created an interface in VB6 which passes the values entered
through the textboxes to the backend procedure running in Oracle 9i. I
am pasting the exact code which I have for VB6 interface

Private Sub Command1_Click()
Dim conn As ADODB.Connection
Dim comm As ADODB.Command
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set conn = New ADODB.Connection

conn.Open "DSN=smartdb;UID=scott;PWD=tiger"

Set comm = New ADODB.Command
With comm
.ActiveConnection = conn
.CommandType = adCmdStoredProc
.CommandText = "pk_retrieve_records.sp_retrieve_records"
rs.CursorLocation = adOpenStatic
End With

comm.Parameters.Append comm.CreateParameter("copyright_year",
adInteger, adParamInput, 4, Text1.Text)
comm.Parameters.Append comm.CreateParameter("active_edition",
adVarChar, adParamInput, 1, Text2.Text)
Set rs = comm.Execute

Dim i As Integer
Dim J As Integer


MSFlexGrid1.FixedRows = 1
MSFlexGrid1.FixedCols = 0

i = 1

Do Until rs.EOF
MSFlexGrid1.TextMatrix(i, 1) = rs(0)
MSFlexGrid1.TextMatrix(i, 2) = rs(1)
MSFlexGrid1.TextMatrix(i, 3) = rs(2)
MSFlexGrid1.TextMatrix(i, 4) = rs(3)
MSFlexGrid1.TextMatrix(i, 5) = rs(4)
MSFlexGrid1.TextMatrix(i, 6) = rs(5)
MSFlexGrid1.TextMatrix(i, 7) = rs(6)
MSFlexGrid1.TextMatrix(i, 8) = rs(7)
MSFlexGrid1.TextMatrix(i, 9) = rs(8)
rs.MoveNext
i = i + 1
Loop

conn.Close
End Sub

The problem is that whenever i click on command button i get an error:

Run-time error: '-2147217887 (80040e21)':
ODBC driver doesnot support the requested properties.

The procedure running at the back end basically selets the values form
a table and returns the cursor which should have been displayed in the
flexigrid.

Please help me out in removing the error

PKG SPec:

create or replace package pk_retrieve_records AS
TYPE T_CURSOR IS REF CURSOR;
Procedure sp_retrieve_records(
p_copy IN a_titledetails.copyright_year%TYPE,
p_active IN a_titledetails.active_edition%TYPE,
cur_rec OUT T_CURSOR);
END pk_retrieve_records;


PKG BODY:

CREATE or REPLACE PACKAGE BODY PK_RETRIEVE_RECORDS As
Procedure sp_retrieve_records(
p_copy IN a_titledetails.copyright_year%TYPE,
p_active IN a_titledetails.active_edition%TYPE,
cur_rec OUT T_CURSOR)
IS
BEGIN
if((trim(p_copy) <> NULL) AND (trim(p_active) = NULL)) Then
OPEN cur_rec For
Select * from a_titledetails where copyright_year=p_copy;
ElsIf ((trim(p_copy) <> NULL) AND (trim(p_active) <> NULL)) Then
OPEN cur_rec For
Select * from a_titledetails where copyright_year=p_copy And
active_edition=p_active;
ElsIf ((trim(p_copy) = Null) And (trim(p_active) <> Null)) Then
OPEN cur_rec For
Select * from a_titledetails where active_edition=p_active;
END IF;
END sp_retrieve_records;
END PK_RETRIEVE_RECORDS;

 

[Brendan Reynolds]

Well ... you might get a more definitive answer in a forum that deals with
Oracle, VB6, and / or ADO than in one that deals with Access and DAO!
That said, though, adOpenStatic is a member of CursorTypeEnum, not
CursorLocationEnum ...

rs.CursorLocation = adOpenStatic '<-- should be adUseClient or
adUseServer

The actual value of adOpenStatic is 3, same as adUseClient. Perhaps that
might be the problem? Perhaps the Oracle ODBC driver does not support
client-side cursors?