Thank you so much for this. I used this code but I am still having problems and I think it is because this particular stored procedure has no parameters and I can't seem to work out how to alter this function accordingly. I am new to RDO and I am definitely not using it by choice. Do you perhaps have code that runs a stored procedure which has no parameters? I would really appreciate it - sorry to be a pest!
--- In [email protected], Srinivasan <[EMAIL PROTECTED]> wrote: > > Hi. > > No need to wonder, still some people are using RDO like me, eventhough we know ADO.net still we have some projects which has been built and supported on RDO. > > Anyway, hope this function will help you. > > -Srinivas > > Public Function gfExecuteSP(ByVal aStrSPName As String, ByRef aArrType(), ByRef aArrValue()) As Boolean > > On Error GoTo gfExecuteSP_Err > Dim prdySP As rdoQuery > Dim pstrSQL As String > Dim rst As rdoResultset > Dim pintParamCnt As Integer > Dim pstrParamChr As String > Dim pintloop As Integer > Dim pblnOnTrans As Boolean > > > For pintloop = LBound(aArrType) To UBound(aArrType) > pstrParamChr = pstrParamChr & "?, " > Next > > pstrParamChr = Left(pstrParamChr, Len(pstrParamChr) - 2) > > pstrSQL = "{call " & aStrSPName & "(" & pstrParamChr & ") }" > Set prdySP = cnMSTT.CreateQuery(aStrSPName, pstrSQL) > > ' Set Parameter "direction" for each output > ' and return value parameter. > > For pintloop = LBound(aArrType) To UBound(aArrType) > prdySP(pintloop).Direction = aArrType(pintloop) > If aArrType(pintloop) = 0 Or aArrType(pintloop) = 1 Then 'Input or InputOutput > prdySP(pintloop).Value = aArrValue(pintloop) > End If > Next pintloop > > > ' Create the result set and populate the Ps values. > cnMSTT.BeginTrans > pblnOnTrans = True > Set rst= prdySP.OpenResultset(rdOpenStatic) > > If prdySP(0) = 0 And IsNull(prdySP(1)) And IsNull(prdySP(2)) Then > cnMSTT.CommitTrans > gfExecuteSP = True > pblnOnTrans = False > Else > cnMSTT.RollbackTrans > pblnOnTrans = False > gfExecuteSP = False > > aArrValue(0) = prdySP(0) > aArrValue(1) = prdySP(1) > aArrValue(2) = prdySP(2) > > If IsNull(prdySP(1)) Then > MsgBox prdySP(2), vbCritical, "Error While Executing SP -" & aStrSPName > Else > MsgBox prdySP(1) & vbCr & prdySP(2), vbCritical, "Error While Executing SP -" & aStrSPName > End If > > GoTo Cleanup > End If > > Cleanup: > Set prdySP = Nothing > Set rst= Nothing > Exit Function > > gfExecuteSP_Err: > If pblnOnTrans Then > cnMSTT.RollbackTrans > Set prdySP = Nothing > Set rst = Nothing > End If > MsgBox Err.Description, vbExclamation > > End Function > > ********************************************************************** *************************** > > Good day friends! > > I was wondering if anyone has a code snippet to call an SQL stored > procedure from within VB using RDO. > > Thanks so much! > > > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > [Non-text portions of this message have been removed] '// ======================================================= Rules : http://ReliableAnswers.com/List/Rules.asp Home : http://groups.yahoo.com/group/vbHelp/ ======================================================= Post : [email protected] Join : [EMAIL PROTECTED] Leave : [EMAIL PROTECTED] '// ======================================================= Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/vbhelp/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
