如何在插入前检查重复记录，使用 vb.net 和 sql？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22922638/
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 check duplicate record before insert, using vb.net and sql?
提问by user3367225
can someone help me with my code, i need to check first if record exist. Well i actually passed that one, but when it comes to inserting new record. im getting the error "There is already an open DataReader associated with this Command which must be closed first." can some help me with this? thanks.
有人可以帮我写代码吗，我需要先检查记录是否存在。好吧，我实际上通过了那个，但是在插入新记录时。我收到错误“已经有一个与此命令关联的打开的 DataReader，必须先关闭它。” 有人可以帮我吗？谢谢。
Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim reg_con As SqlConnection
Dim reg_cmd, chk_cmd As SqlCommand
Dim checker As SqlDataReader
Dim ID As Integer
Dim fname_, mname_, lname_, gender_, emailadd_, college_, password_ As String
ID = idnumber.Value
fname_ = fname.Value.ToString
mname_ = mname.Value.ToString
lname_ = lname.Value.ToString
gender_ = gender.Value.ToString
college_ = college.Value.ToString
emailadd_ = emailadd.Value.ToString
password_ = reg_password.Value.ToString
reg_con = New SqlConnection("Data Source=JOSH_FLYHEIGHT;Initial Catalog=QceandCceEvaluationSystemDatabase;Integrated Security=True")
reg_con.Open()
chk_cmd = New SqlCommand("SELECT IDnumber FROM UsersInfo WHERE IDnumber = '" & ID & "'", reg_con)
checker = chk_cmd.ExecuteReader(CommandBehavior.CloseConnection)
If checker.HasRows Then
MsgBox("Useralreadyexist")
Else
reg_cmd = New SqlCommand("INSERT INTO UsersInfo([IDnumber], [Fname], [Mname], [Lname], [Gender], [Emailadd], [College], [Password]) VALUES ('" & ID & "', '" & fname_ & "', '" & mname_ & "', '" & lname_ & "', '" & gender_ & "', '" & emailadd_ & "', '" & college_ & "', '" & password_ & "')", reg_con)
reg_cmd.ExecuteNonQuery()
End If
reg_con.Close()
End Sub
回答by Steve
Add this string to your connection string
将此字符串添加到您的连接字符串
...MultipleActiveResultSets=True;";
Starting from Sql Server version 2005, this string allows an application to maintain multiple active statements on a single connection. Without it, until you close the SqlDataReader you cannot emit another command on the same connection used by the reader.
从 Sql Server 2005 版开始，此字符串允许应用程序在单个连接上维护多个活动语句。没有它，在关闭 SqlDataReader 之前，您无法在读取器使用的同一连接上发出另一个命令。
Apart from that, you insert statement is very dangerous because you use string concatenation. This is a well known code weakness that could result in an easy Sql Injection vulnerability
除此之外，您插入语句非常危险，因为您使用字符串连接。这是一个众所周知的代码弱点，可能导致一个简单的 Sql 注入漏洞
You should use a parameterized query (both for the insert and for the record check)
您应该使用参数化查询（用于插入和记录检查）
reg_cmd = New SqlCommand("INSERT INTO UsersInfo([IDnumber], ......) VALUES (" & _
"@id, ......)", reg_con)
reg_cmd.Parameters.AddWithValue("@id", ID)
.... add the other parameters required by the other field to insert.....
reg_cmd.ExecuteNonQuery()
In a parameterized query, you don't attach the user input to your sql command. Instead you put placeholders where the value should be placed (@id), then, before executing the query, you add, one by one, the parameters with the same name of the placeholder and its corresponding value.
在参数化查询中，您不会将用户输入附加到您的 sql 命令。相反，您将占位符放在应放置值的位置 (@id)，然后在执行查询之前，您将与占位符及其对应值同名的参数一一添加。
回答by Yuriy Galanter
Quick and dirty solution - issue checker.Close()as a first command of both IF and ELSE block.
快速而肮脏的解决方案 -checker.Close()作为 IF 和 ELSE 块的第一个命令发出。
But (better) you don't need a full blown data reader to check for record existence. Instead you can do something like this:
但是（更好）您不需要完整的数据读取器来检查记录是否存在。相反，您可以执行以下操作：
chk_cmd = New SqlCommand("SELECT TOP (1) 1 FROM UsersInfo WHERE IDnumber = '" & ID & "'", reg_con)
Dim iExist as Integer = chk_cmd.ExecuteScalar()
If iExist = 1 Then
....
This approach uses ExecuteScalarmethod that returns a single value and doesn't tie the connection.
此方法使用ExecuteScalar方法，该方法返回单个值并且不绑定连接。
Side note: Instead of adding parameters like you do now - directly to the SQL String, a much better (and safer) approach is to use parametrized queries. Using this approach can save you a lot of pain in the future.
旁注：不是像现在那样添加参数 - 直接添加到 SQL 字符串，更好（更安全）的方法是使用参数化查询。使用这种方法可以在未来为您节省很多痛苦。
回答by rory.ap
You need to close your reader using checker.Close()as soon as you're done using it.
使用checker.Close()完毕后，您需要立即关闭阅读器。
