vb.net 在vb.net中保存数据库时如何避免重复条目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28080140/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to avoid duplicate entries when saving in database in vb.net
提问by Men8 Burger
I have a button but it can save duplicate entries i don't know how to correctly put a if not exist operator pls help..
我有一个按钮，但它可以保存重复的条目我不知道如何正确放置如果不存在操作员请帮助..
cmd = New SqlCommand("INSERT INTO Students(Familyname,Firstname,Middlename,StudentID)VALUES('" & txtname.Text & "','" & txtfname.Text & "','" & txtmname.Text & "','" & txtid.Text & "')", cn)
cn.Open()
i = cmd.ExecuteNonQuery
cn.Close()
If txtname.Text <> "" Then
ElseIf i > 0 Then
MsgBox("Save Sucessfully!", MessageBoxIcon.Information, "Success")
showrecord()
clear()
Else
MsgBox("Save Failed!", MessageBoxIcon.Error, "Error")
End If
回答by Felix Pamittan
You can use NOT EXISTSto prevent duplicate insert:
您可以使用NOT EXISTS来防止重复插入：
Dim sql = "INSERT INTO Students(Familyname, Firstname, Middlename, StudentID) " & _
"VALUES(@FamilyName, @Firstname, @Middlename, @StudentID)" & _
"WHERE NOT EXISTS(SELECT 1 FROM Students WHERE StudentId = @StudentID)"
Using cn As New SqlConnection("Your connection string here")
Dim cmd As SqlCommand = New SqlCommand(sql, cn)
cmd.Parameters.Add("@FamilyName", SqlDbType.VarChar, 50).Value = txtname.Text
cmd.Parameters.Add("@Firstname", SqlDbType.VarChar, 50).Value = txtfname.Text
cmd.Parameters.Add("@Middlename", SqlDbType.VarChar, 50).Value = txtmname.Text
cmd.Parameters.Add("@StudentID", SqlDbType.VarChar, 50).Value = txtid.Text
Dim i = cmd.ExecuteNonQuery
End Using
You should always use parameterized queries to avoid SQL Injection attacks.
您应该始终使用参数化查询来避免 SQL 注入攻击。
NOTE:Please apply appropriate field types.
注意：请应用适当的字段类型。
回答by Jun Rikson
Try this one :
试试这个：
cn.Open()
Dim intReturn as integer
Dim strSql as string = "Select * from Students where StudentID = @StudentID"
sqlcmd = new sqlcommand(strSql, cn)
With sqlcmd.parameters
.addwithvalue("@StudentID", ctype(txtid.text,string)
End with
intReturn = sqlcmd.ExecuteScalar
If(intReturn > 0)
cmd = New SqlCommand("INSERT INTO Students(Familyname,Firstname,Middlename,StudentID)VALUES('" & txtname.Text & "','" & txtfname.Text & "','" & txtmname.Text & "','" & txtid.Text & "')", cn)
i = cmd.ExecuteNonQuery
If txtname.Text <> "" Then
ElseIf i > 0 Then
MsgBox("Save Sucessfully!", MessageBoxIcon.Information, "Success")
showrecord()
clear()
Else
MsgBox("Save Failed!", MessageBoxIcon.Error, "Error")
End If
Else
MsgBox("Student Already Exist", MessageBoxIcon.Error, "Error")
End If
cn.Close()
And don't forget to make your StudentID field as Unique in your database.
并且不要忘记将您的 StudentID 字段设置为数据库中的唯一字段。
