UC知道VB对SQL进行添加操作
来源：百度知道 编辑：UC知道 时间：2024/06/24 01:37:59
小弟正在学习VB与SQL数据库，现在需要向SQL数据库的一个表里添加数据，但是要求DeptName不能有重复的，出现重复则发出警告。我写了一部分代码，虽然DataGridView及界面能满足要求，但是数据库里仍然将相同DeptName的信息写入了。怎样才能使出现相同DeptName时，数据库也不能写入。
代码如下，请各位高手帮忙解决一下：
Dim SY_Sqlstr As String
Dim SY_Sqlstr1 As String
Dim SY_table1 As DataTable = New DataTable()
Private Sub SY_Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SY_Button1.Click
Dim SY_CBRDBConn1 As SqlConnection = New SqlConnection("Data source=NEU-20052785;Initial Catalog=CBRDB;Integrated Security=True")
Dim SY_CBRDBComm1 As New SqlCommand
SY_Sqlstr1 = TextBox1.Text
Dim SY_Sqlstr0 As String = "Insert Into t_Department (DeptName,Appendix) Values('" & SY_TextBox1.Text & "','" & SY_TextBox2.Text & "')"
SY_CBRDBComm1.Connection = SY_CBRDBConn1
SY_CBRDBComm1.CommandText = SY_Sqlstr0
SY_CBRDBConn1.Open()
If Me.
代码如下，请各位高手帮忙解决一下：
Dim SY_Sqlstr As String
Dim SY_Sqlstr1 As String
Dim SY_table1 As DataTable = New DataTable()
Private Sub SY_Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SY_Button1.Click
Dim SY_CBRDBConn1 As SqlConnection = New SqlConnection("Data source=NEU-20052785;Initial Catalog=CBRDB;Integrated Security=True")
Dim SY_CBRDBComm1 As New SqlCommand
SY_Sqlstr1 = TextBox1.Text
Dim SY_Sqlstr0 As String = "Insert Into t_Department (DeptName,Appendix) Values('" & SY_TextBox1.Text & "','" & SY_TextBox2.Text & "')"
SY_CBRDBComm1.Connection = SY_CBRDBConn1
SY_CBRDBComm1.CommandText = SY_Sqlstr0
SY_CBRDBConn1.Open()
If Me.
在数据库中对表t_Department创建触发器 具体如下:
create trigger indel on t_Department instead of insert
as
if not exists (select * from t_Department where DeptName=(select DeptName from inserted))
begin
insert t_Department select * from inserted
end
else
begin
raiserror ('此部门名已存在',16,1)
end
或者在建表时将 字段DeptName设为 unique或者 primary key
两种办法：
1.在sql server 设置约束，如主键约束，唯一，也可以在sql server数据库编写触发器
2.在程序中控制,数据插入前检查if not exists (select 1 from deptname where deptno ='" & SY_TextBox1.Text & "') Insert Into t_Department (DeptName,Appendix) Values('" & SY_TextBox1.Text & "','" & SY_TextBox2.Text & "')