Click here to Skip to main content
15,073,496 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Situation: Adding New Records while avoiding duplicates.
Question: How to Cancel the Insert Function when there is a duplicate
Problem: the code detects if there is a duplicate and shows in a pop-up , but it still adds in the record anyways.

What I have tried:

<pre> Dim sqlconn1 As New OleDb.OleDbConnection("Provider =Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Profile.mdb"
 Dim myreader As OleDbDataReader
            Dim strql As String
sqlquery.CommandText="INSERT INTO Table1(ID,Name)VALUES(@lID,@Name
)"
       strql = "Select rName from Profile where Name='" + NameTextBox.Text + "'"
    Dim cmd2 As New OleDbCommand(strql, sqlconn1)
            sqlquery.Parameters.AddWithValue("@ID", IDTextBox.Text)
            sqlquery.Parameters.AddWithValue("@Name", TextBox.Text)
myreader = cmd2.ExecuteReader
            If (myreader.Read()) Then
                MsgBox("Teacher Exists")
            End If
            sqlquery.ExecuteNonQuery()
            sqlconn.Close()
            sqlconn1.Close()
 MessageBox.Show("School Added")
            Update()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
Posted
Updated 12-Aug-21 20:22pm
Comments
Richard MacCutchan 13-Aug-21 3:05am
   
1. Execute the SELECT statement
2. If the record is not found then Execute the INSERT.

All you are doing is posting a message and then continuing to execute the INSERT.

Also try using more meaningful names. An INSERT is not a query, it is a command, so why name it "sqlquery"?

1 solution

First off, don;t do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

When you have fixed that through your whole app ... add a Return after the call to MsgBox ...

But do yourself a favour, and stop using ancient VB forms: use the more modern MessageBox class instead of legacy code that was replaced twenty years ago ...
   
Comments
Beginner213456 13-Aug-21 3:14am
   
When you said delete the table, will it literally delete the whole table or delete the records from the table that has a duplicate?
OriginalGriff 13-Aug-21 3:45am
   
Did you read what I said, or just gloss over the highlights?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900