Private Sub Command1_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Dim str As String
str = "create trigger aa on t for delete as raiserror('不能删除数据!',16,1)"cn.ConnectionString = "driver={sql server};server=tlq;database=pubs;uid=;password=;"
cn.Open
cn.Execute (str)
rs.Open "delete from q", cn, adOpenKeyset, adLockOptimistic
End Sub我想要的结果是删除数据时间出现错误提示,但是运行不出结果。
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Dim str As String
str = "create trigger aa on t for delete as raiserror('不能删除数据!',16,1)"cn.ConnectionString = "driver={sql server};server=tlq;database=pubs;uid=;password=;"
cn.Open
cn.Execute (str)
rs.Open "delete from q", cn, adOpenKeyset, adLockOptimistic
End Sub我想要的结果是删除数据时间出现错误提示,但是运行不出结果。
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim str As String
str = "create trigger aa on t for delete as raiserror('不能删除数据!',16,1)" set cn.ConnectionString = "driver={sql server};server=tlq;database=pubs;uid=;password=;"
cn.Open
cn.Execute (str)
rs.Open "delete from q", cn, adOpenKeyset, adLockOptimistic
End Sub
以上根据习惯来发表的意见,没测试过,呵呵!
我想要的结果是出现象MSGBOX后者BEEP的方式通知用户不能执行这样的操作,但是现在出现的是实时错误[MICROSOFT][SQL SERVER DRIVER][SQL SERVER]不能删除数据!这样提示。请问这样的问题怎样解决,谢谢!
昨天是我疏忽,把t写成q。
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim str As String
str = "create trigger aa on t for delete as raiserror('不能删除数据!',16,1)" set cn.ConnectionString = "driver={sql server};server=tlq;database=pubs;uid=;password=;"
cn.Open
cn.Execute (str)
rs.Open "delete from q", cn, adOpenKeyset, adLockOptimistic
End Sub
上述代码存在的问题:1、对于cn设置连接串时,不能用set,直接赋值即可
cn.ConnectionString = "driver={sql server};server=tlq;database=pubs;uid=;password=;"2、此方法是每次都要创建新的触发器,这样会有错误,因为对于第一次执行,没有问题,以后执行便会提示“[Microsoft][ODBC SQL Server Driver][SQL Server]数据库中已存在名为 'aa' 的对象。”3、对于楼主说的要给出自己的提示,可以使用on error来处理对于1、3的处理应改为如下代码:Private Sub Command1_Click()
On Error GoTo Err_Handle
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim str As String
str = "create trigger aa on t for delete as raiserror('不能删除数据!',16,1)" cn.ConnectionString = "driver={sql server};server=tlq;database=pubs;uid=;password=;" cn.Open
cn.Execute (str)
rs.Open "delete from t", cn, adOpenKeyset, adLockOptimistic
Exit Sub
Err_Handle:
If Err.Number = -2147217900 Then
MsgBox "不能删除数据!"
Else
MsgBox Err.Description
End If
End Sub另外,生成触发器的代码应该判断是否存在,如果存在,就不需要再创建
谢谢你的建议,我现在通过你的提示已经解决了这个问题,非常感谢.
同时也谢谢所有参与我问题的人.