how to check if record exists, if not insert using vb.net?

Question

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.

Add an UNIQUE keu on isbn, use INSERT IGNORE?

Wrikken
– Wrikken

2013-03-10 09:07:58 +00:00
Commented Mar 10, 2013 at 9:07 — Wrikken
– Wrikken, Commented Mar 10, 2013 at 9:07

Community · Accepted Answer · 2017-05-29 14:11:44Z

2

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

edited May 29, 2017 at 14:11

CommunityBot

11 silver badge

answered Mar 10, 2013 at 10:04

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

peterG Over a year ago

. . .or you could do the same thing entirely within your SQL ( at least you could using MSSQL - I'm assuming MySQL is the same )

MK Wong · Accepted Answer · 2015-06-12 08:18:28Z

0

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 ) "

answered Jun 12, 2015 at 8:18

MK Wong

955 bronze badges

Collectives™ on Stack Overflow

how to check if record exists, if not insert using vb.net?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related