Private Sub Command1_Click()
Dim conn As New ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Ldbms"
conn.Open
Dim strSQL As String
Dim str1 As String
Dim ADOrs As ADODB.Recordset
Set ADOrs = New ADODB.Recordset
str1 = "select 文献编号 from 文献 where 文献编号 ='" + Trim(Text1.Text) + "'"
ADOrs.Open str1, conn, adOpenKeyset, adLockOptimistic
If ADOrs.EOF Then
MsgBox "文献不存在,不能修改", vbOKOnly, "提示"
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Else
Set ADOrs = conn.Execute("exec updatepro" & "'" & Text1.Text & "','" & Text2.Text & "','" & Text3.Text & "','" & Text4.Text & "'")
On Error GoTo Err
MsgBox "修改成功!", vbOKOnly, "提示"
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Exit Sub
Err:
MsgBox "不存在该图书馆编号,不能修改!"
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
End If
End Sub
这是VB窗体中的部分
我写的存储过程如下:
create procedure updatepro(@id int,@name char(50),@no char,@index int)
as
begin
if @no > 'C'
begin
RAISERROR ('不存在该图书馆编号!',16,1)
rollback transaction
end
begin
update 文献
set 文献.文献名称=@name
where 文献.文献编号=@id
update 保存
set 图书馆编号=@no
where 保存.文献编号=@id
update 保存
set 索引号=@index
where 保存.文献编号=@id
end
end
就是希望输入的图书馆编号如果不是已有的A,B,C则存储过程出错,VB有错误提示框。
但是我运行后都是显示“修改成功!”(不管是正确的还是错误的,但是正确的确实修改成功了,错误的也确实没有修改,但是无法显示MsgBox "不存在该图书馆编号,不能修改!)很困惑~~求解~~
------解决思路----------------------
存储过程的入参不是这样弄的,应该是
create procedure updatepro
@id int,
@name char(50),
@no char,
@index int
as
begin