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