VB6.0 調用存儲過程的例子（方法一）
2024-07-21 02:24:41
供稿：網友
打開form1窗體，copy以下的代碼到窗體中，該段代碼將測試存儲過程adotestrpe的返回值、輸入參數及輸出參數，測試的過程中，可能需要修改鏈接字符串。
sub createparms()
dim adocmd as new adodb.command
dim adoprm as new adodb.parameter
dim adocon as adodb.connection
dim adors as adodb.recordset
dim sparmname as string
dim strconnect as string
dim rstr as string
on error goto errhandler
strconnect = "driver={sql server};server=(local);uid=sa;pwd=;database=pubs"
set adocon = new adodb.connection
with adocon
.provider = "msdasql"
.cursorlocation = aduseserver 'must use server side cursor.
.connectionstring = strconnect
.open
end with
set adocmd.activeconnection = adocon
with adocmd
.commandtype = adcmdstoredproc
.commandtext = "adotestrpe"
end with
'parameter 0 is the stored procedure return code.
sparmname = "return"
set adoprm = adocmd.createparameter(sparmname, adinteger, _
adparamreturnvalue, , 0)
adocmd.parameters.append adoprm
adocmd.parameters(sparmname).value = -1
'parameter 1 is the setting for the stored procedure output
' parameter.
sparmname = "output"
set adoprm = adocmd.createparameter(sparmname, adinteger, _
adparamoutput)
adocmd.parameters.append adoprm
adocmd.parameters(sparmname).value = 999
'parameter 2
sparmname = "r1num" 'number of rows to return in resultset 1.
set adoprm = adocmd.createparameter(sparmname, adinteger, _
adparaminput)
adocmd.parameters.append adoprm
adocmd.parameters(sparmname).value = 1
'parameter 3
sparmname = "p1num" 'number of print statements in resultset 1.
set adoprm = adocmd.createparameter(sparmname, adinteger, _
adparaminput)
adocmd.parameters.append adoprm
adocmd.parameters(sparmname).value = 0
'parameter 4
sparmname = "e1num" 'number of raiserror statements in resultset
'1.
set adoprm = adocmd.createparameter(sparmname, adinteger, _
adparaminput)
adocmd.parameters.append adoprm
adocmd.parameters(sparmname).value = 0
'parameter 5
sparmname = "r2num" 'number of rows to return in resultset 2.
set adoprm = adocmd.createparameter(sparmname, adinteger, _
adparaminput)
adocmd.parameters.append adoprm
adocmd.parameters(sparmname).value = 2
'parameter 6
sparmname = "p2num" 'number of print statements in resultset 2.
set adoprm = adocmd.createparameter(sparmname, adinteger, _
adparaminput)
adocmd.parameters.append adoprm
adocmd.parameters(sparmname).value = 0
'parameter 7
sparmname = "e2num" 'number of raiserror statements in resultset
' 2.
set adoprm = adocmd.createparameter(sparmname, adinteger, _
adparaminput)
adocmd.parameters.append adoprm
adocmd.parameters(sparmname).value = 0
set adors = adocmd.execute
do while (not adors is nothing)
if adors.state = adstateclosed then exit do
while not adors.eof
for i = 0 to adors.fields.count - 1
rstr = rstr & " : " & adors(i)
next i
debug.print mid(rstr, 3, len(rstr))
adors.movenext
rstr = ""
wend
debug.print "----------------------"
set adors = adors.nextrecordset
loop
debug.print "return: " & adocmd.parameters("return").value
debug.print "output: " & adocmd.parameters("output").value
goto shutdown
errhandler:
call errhandler(adocon)
resume next
shutdown:
set adocmd = nothing
set adoprm = nothing
set adors = nothing
set adocon = nothing
end sub
private sub command1_click()
call createparms
end sub
sub errhandler(objcon as object)
dim adoerr as adodb.error
dim strerror as string
for each adoerr in objcon.errors
strerror = "error #" & adoerr.number & vbcrlf & adoerr.description _
& vbcr & _
" (source: " & adoerr.source & ")" & vbcr & _
" (sql state: " & adoerr.sqlstate & ")" & vbcr & _
" (nativeerror: " & adoerr.nativeerror & ")" & vbcr
if adoerr.helpfile = "" then
strerror = strerror & " no help file available" & vbcr & vbcr
else
strerror = strerror & " (helpfile: " & adoerr.helpfile & ")" _
& vbcr & " (helpcontext: " & adoerr.helpcontext & ")" & _
vbcr & vbcr
end if
debug.print strerror
next
objcon.errors.clear
end sub
