Reputation: 87
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.
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.
Upvotes: 0
Views: 11008
Reputation: 216358
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
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
Upvotes: 2
Reputation: 95
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 ) "
Upvotes: 0