RDO Stored procedure problems

RDO Stored procedure problems

Post by Michael Le » Sat, 05 Jun 1999 04:00:00



    Hi All,
    I need help with this stored procedure. I hope that you can help. I
need to insert two fields into a table and return a value based on an ID
field (Identity field) and the date. When I run the VB RDO code below I
get the following error message:
37000: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open a
cursor on a stored procedure that has anything other than a single
select statement in it
Here is the VB Code:
Public Function DW_GetNextTransId(ClientId As String, ProductId As Long)
As String
Dim CallingFunctionName$
Dim sql As String, TransId As Long
Dim cn As rdoConnection, rdoTransId As rdoResultset
Dim qd As New rdoQuery

On Error GoTo Err_RunNum

    CallingFunctionName$ = gFunctionName
    gFunctionName$ = "DW_GetNextTransId"
    Set cn = rdoEnvironments(0).OpenConnection(gDBWatch_SOURCE, _
    rdDriverNoPrompt, False, "UID=" & gDBWatchDB_USER & ";PWD=" _
    & gDBWatchDB_PW & ";Database=" & gDBWatch_SOURCE)
    Set qd.ActiveConnection = cn
    qd.sql = "{ call GetNextTransId (?, ?) }"
    qd(0) = ClientId
    qd(1) = ProductId
    'I Get the Error on the following statement.
    Set rdoTransId = qd.OpenResultset(sql, rdConcurReadOnly)

    DW_GetNextTransId = rdoTransId!TransCode
    On Error Resume Next
    rdoTransId.Close
    cn.Close
    Exit Function

Err_RunNum:
    DW_GetNextTransId = 0
    Dim errnum As Long, errmsg$
    errnum = Err.number
    errmsg = Err.Description
    rdoTransId.Close
    cn.Close
End Function

    I have the following two Stored procedures:

as
begin


UPDATE PlanPFR_TransIDs
SET TransCode = CONVERT(varchar(12), getdate(), 12) +


Select CONVERT(varchar(12), getdate(), 12) +

end
GO


as
begin
insert PlanPFR_TransIDs (ClientId, ProductId)

end
GO

    Can anyone please tell me why I'm getting this error message. And if
there is a sollution. I started to do this with just a standard
rdoresultset (Insert) then an update query, but I though was not as safe
as the stored procedure way. Thanks again.

Michael Lee