Re: Error reading recordset from DB2/AS400

ShaggyMoose wrote:
On Sep 10, 6:39 pm, "Stephen Howe" <stephenPOINThoweATtns-
globalPOINTcom> wrote:
Should not be. It could be the ADO code.
Could we see the code?

Sure. Here is the stored procedure on AS400:

CREATE PROCEDURE PR_TEST()
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE C1 CURSOR WITH RETURN FOR
SELECT * FROM QPGR/TMP_WRK;
OPEN C1;
END

And here is the VB6 code:

connString = "Driver={Client Access ODBC Driver (32-bit)};" & _
"System=xxx.xxx.xxx.xxx"
";Naming=1" & _
";DefaultLibraries=*USRLIBL"

Set adoConn = New ADODB.Connection
Call adoConn.Open(connString)

Set adoCmd = New ADODB.Command
With adoCmd
.ActiveConnection = adoConn
.CommandType = adCmdStoredProc
.CommandText = "PR_TEST"
End With

Set adoRecSet = New ADODB.Recordset
With adoRecSet
.CursorType = adOpenForwardOnly
.LockType = adLockOptimistic
.CursorLocation = adUseClient

Nothing to do with your problem, but the forward-only cursor type is
incompatible with a client-side cursor location. The only cursor type
supported by the ADO cursor library is static. If you want a
forward-only cursor, then you need to use a server-side cursor.

End With

It's been awhile, but I seem to recall having to use the ODBC syntax for
calling stored procedures grom the AS400. Let me see if I can find an
example ...

With adoCmd
Set .ActiveConnection = adoConn
.CommandType = adCmdText
.CommandText = "{Call libraryname.PR_TEST()}"
End With

In fact, a server-side forward-only cursor is the default cursor, so
given that you want a forward-only cursor, you don't even need to create
an explicit Command object. Try this;

Set adorecSet= _
adoConn.Execute("{Call libraryname.PR_TEST()}", ,adCmdText)


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages

  • Re: Error reading recordset from DB2/AS400
    ... Here is the stored procedure on AS400: ... DECLARE C1 CURSOR WITH RETURN FOR ... .CommandType = adCmdStoredProc ... Set adoRecSet = New ADODB.Recordset ...
    (microsoft.public.data.ado)
  • Re: noise words, @@ERROR, and stop and resume indexing
    ... these words at the beginning before sending them to the cursor. ... Looking for a FAQ on Indexing Services/SQL FTS ... >>Create table Noise ... >>> 1) check noise words inside stored procedure ...
    (microsoft.public.sqlserver.fulltext)
  • Re: SQL stored procedure not working.
    ... If I do not use that, how can I get the total record count? ... >> I create a stored procedure and a asp page. ... Even if you could specify the cursor type there, ... > interested in the RETURN value, you do not need to use a Command object. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Case statement issue
    ... except with a cursor other than the default ... > I have taken the sample of paging using a stored procedure from the ... > case statement, then the error message. ... > The for loop also seems to be a problem. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Case statement issue
    ... except with a cursor other than the default ... > I have taken the sample of paging using a stored procedure from the ... > case statement, then the error message. ... > The for loop also seems to be a problem. ...
    (microsoft.public.inetserver.asp.general)