VB中得到存儲過程的出錯信息

T315608 2004-08-27 09:58:07
利用一個存儲過程首先刪除表t1中滿足條件的記錄,然後向其中插入一些數據,隻有當兩個操作都成功後才提交事務,當存儲過程操作失敗時在vb中怎樣得知存儲過程的此次對表t1的操作失敗,望大俠給一簡單實例,謝謝!
...全文
52 点赞 收藏 4
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
email0755 2004-08-27
找个带返回值的StoreProce看看就行了!
看看这个:
USE pubs
GO
CREATE PROCEDURE myProc
@outparm int OUTPUT
@inparm int
AS
SELECT * FROM titles WHERE royalty > @inparm
SELECT @outparm = COUNT (*) FROM TITLES WHERE royalty > @inparm
IF (@outparm > 0)
RETURN 0
ELSE
RETURN 99
GO

An ADO code program that executes the stored procedure myProc is shown here.

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim fldloop As ADODB.Field
Dim param1 As Parameter, param2 As Parameter, param3 As Parameter
Dim provStr As String
Dim royalty As Variant

Private Sub spStart()

' Connect using the SQLOLEDB provider.
cn.Provider = "sqloledb"

' Specify connection string on Open method.
provStr = "Server=MyServer;Database=pubs;Trusted_Connection=yes"
cn.Open provStr

' Set up a command object for the stored procedure.
Set cmd.ActiveConnection = cn
cmd.CommandText = "myProc"
cmd.CommandType = adCmdStoredProc

' Set up a return parameter.
Set param1 = cmd.CreateParameter("Return", adInteger, adParamReturnValue)
cmd.Parameters.Append param1

' Set up an output parameter.
Set param2 = cmd.CreateParameter("Output", adInteger, adParamOutput)
cmd.Parameters.Append param2

' Set up an input parameter.
Set param3 = cmd.CreateParameter("Input", adInteger, adParamInput)
cmd.Parameters.Append param3
royalty = Trim(InputBox("Enter royalty:"))
param3.Value = royalty

' Execute command, and loop through recordset, printing out rows.
Set rs = cmd.Execute

Dim i As Integer
While Not rs.EOF
For Each fldloop In rs.Fields
Debug.Print rs.Fields(i)
i = i + 1
Next fldloop
Debug.Print ""
i = 0
rs.MoveNext
Wend

' Need to close recordset before getting return
' and output parameters.
rs.Close

Debug.Print "Program ended with return code: " & Cmd(0)
Debug.Print "Total rows satisfying condition: " & Cmd(1)
cn.Close

End Sub

回复
yijiansong 2004-08-27
在存儲過程写异常处理
回复
lxxldd 2004-08-27
通过返回值来判断

回复
online 2004-08-27
失败返回一个值
vb中接受

http://blog.csdn.net/online/archive/2004/08/05/66376.aspx
回复
相关推荐
发帖

1188

社区成员

VB 数据库(包含打印,安装,报表)
申请成为版主
帖子事件
创建了帖子
2004-08-27 09:58
社区公告
暂无公告