如何检查记录是否存在，如果不使用 vb.net 插入？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15320544/
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 if record exists, if not insert using vb.net?
提问by user2107624
im stuck checking if record exists in the database. this is easy in php but i can't find a good tutorial how to do it in vb.net. i want to insert values from textboxes if it does not exist in database.
我坚持检查数据库中是否存在记录。这在 php 中很容易，但我找不到如何在 vb.net 中做到这一点的好教程。如果数据库中不存在文本框，我想从文本框中插入值。
here is my code:
这是我的代码：
Using SQLConnection As New MySqlConnection(connString)
Using sqlCommand As New MySqlCommand()
With sqlCommand
'check if record exist
'if not execute these
.CommandText = "INSERT INTO bookrecords (Title, Author, Edition, Publisher, ISBN) values (@title,@author,@edition,@publisher,@isbn)"
.Connection = SQLConnection
.CommandType = CommandType.Text
.Parameters.AddWithValue("@title", txtTitle.Text)
.Parameters.AddWithValue("@author", txtAuthor.Text)
.Parameters.AddWithValue("@edition", txtEdition.Text)
.Parameters.AddWithValue("@publisher", txtPublisher.Text)
.Parameters.AddWithValue("@isbn", txtISBN.Text)
End With
Try
SQLConnection.ConnectionString = "Server=localhost;Database=booksdb;Uid=root;Pwd=;"
SQLConnection.Open()
sqlCommand.ExecuteNonQuery()
iReturn = True
'MessageBox.Show("Connection Opened")
Catch ex As MySqlException
MessageBox.Show("Error: " & ex.ToString())
iReturn = False
Finally
SQLConnection.Close()
'MessageBox.Show("Connection Closed")
End Try
End Using
End Using
i just want the @isbn to be the key for determining if a record already exists.
我只是希望@isbn 成为确定记录是否已经存在的关键。
回答by Steve
The obvious thing to do (without touching the database schema) is to send a count query to the database for the ISBN required.
This could be done with ExecuteScalar the appropriate query
显而易见的事情（不涉及数据库架构）是向数据库发送计数查询以获取所需的 ISBN。
这可以通过 ExecuteScalar 完成适当的查询
Using con = new MySqlConnection(connectionString)
Using sqlCommand As New MySqlCommand()
With sqlCommand
.Connection = con
.Parameters.AddWithValue("@isbn", txtISBN.Text)
.CommandText = "SELECT COUNT(*) FROM bookrecords WHERE ISBN = @isbn"
End With
con.Open()
Dim result = sqlCommand.ExecuteScalar()
if Convert.ToInt32(result) = 0 Then
' ISBN doesn't exist ....
sqlCommand.Parameters.Clear()
' rest of your insert code ...
If the command returns a count of zero remember to clear the parameters collection for the subsequent insert query
如果命令返回的计数为零，请记住清除后续插入查询的参数集合
回答by MK Wong
you can do it in one query with something like this:
您可以在一个查询中使用以下内容进行操作：
.CommandText = "INSERT INTO bookrecords (Title, Author, Edition, Publisher, ISBN) " & _
"SELECT * FROM (SELECT @title, @author, @edition, @publisher, @isbn AS ISBN) t " & _
"WHERE NOT EXISTS(SELECT ISBN FROM bookrecords b WHERE t.ISBN = b.ISBN ) "
