Checking of duplicate entry when updating the database vb.net

Question

1.00/5 (1 vote)

See more:

I am already okay with the checking when adding and it works just as I wanted it to be.
I used the oledbDataReader to check my database
This is my sample code used when checking the database (This code is for when I am adding new entry)

VB

  Dim CompQuery As String = "SELECT * FROM Company WHERE CompanyName = '" & txtCompName.Text & "' AND AssociationID = " & cmbUnderAssoc.SelectedValue & " "
 Dim compCommand As OleDbCommand = New OleDbCommand(CompQuery, con)
        compCommand.Parameters.AddWithValue("CompanyName", txtCompName.Text)
        compCommand.Parameters.AddWithValue("AssociationID", cmbUnderAssoc.SelectedValue)
        con.Close()
con.Open()
                Using compReader As OleDbDataReader = compCommand.ExecuteReader
                    If compReader.HasRows Then
                        MsgBox("Entry already exist! Please input new entry")
                        Exit Sub
                    Else

I tried the same logic when updating but it blocks even the entry that is being edited. So I need to change Name of the Company or its Association in order for it to update.

What I want to happen is that the entry being edited can be updated anytime even without changes being made while the datareader will compare the entry to the other entries in the database to check whether it will have any duplicate entry upon updating.

Thanks in advance :)

Posted 4-Aug-15 21:56pm

Jake Robert

Updated 26-Oct-20 6:33am

v2

Add a Solution

Comments

OriginalGriff 5-Aug-15 4:06am

What is the query itself?
I.e., what is in the CompQuery string?

Jake Robert 5-Aug-15 4:29am

Here it is

Dim CompQuery As String = "SELECT * FROM Company WHERE CompanyName = '" & txtCompName.Text & "' AND AssociationID = " & cmbUnderAssoc.SelectedValue & " "

I also updated my question for a better view.

Maciej Los 5-Aug-15 4:43am

What kind of database?

Jake Robert 5-Aug-15 4:47am

I use MS Access as database.

Maciej Los 5-Aug-15 5:05am

In addition to solution 1 by OriginalGriff, i would suggest to change the logic you use. If you would like to add data and prevent from duplicates, use query with EXISTS[^] statement. Plaese see Example - With INSERT Statement section.

Jake Robert 5-Aug-15 20:38pm

Gonna try your idea too. Thanks :) Will update as soon as I test it.

3 solutions

Solution 2

Update[^] statement is used to update record(s) based on criteria. So, it can't create duplicates!

SQL

UPDATE TableName
SET FieldName = "Whatever"
WHERE <Condition>

Finally, your query should look like:

VB

Dim CompQuery As String = "UPDATE * Company" & vbCr & _
"SET CompanyName = '" & txtCompName.Text & "' AND AssociationID = " & cmbUnderAssoc.SelectedValue & vbCr & _
"WHERE CompanyId = " & txtComapnyId.Text & " "

Note: i use CompanyId as Primary Key[^] of Company table.

Posted 5-Aug-15 20:03pm

Maciej Los

Solution 3

Simple solution is to delete that particular record and with updates insert again as new record that matches the exact criteria when adding.

Posted 26-Oct-20 6:33am

Member 14966431

Comments

CHill60 27-Oct-20 7:16am

Very wasteful approach and not recommended

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2015-08-04T22:40:00

Um...when you were reading up on parameterised queries, did you notice how you were supposed to use them?
This query:

VB

Dim CompQuery As String = "SELECT * FROM Company WHERE CompanyName = '" & txtCompName.Text & "' AND AssociationID = " & cmbUnderAssoc.SelectedValue & " "

does not use parameters. Instead, it uses the text directly and is wide open to SQL injection.
Try this:

VB

Dim CompQuery As String = "SELECT COUNT(*) FROM Company WHERE CompanyName = ? AND AssociationID = ?"
Dim compCommand As OleDbCommand = New OleDbCommand(CompQuery, con)
        compCommand.Parameters.AddWithValue("?", txtCompName.Text)
        compCommand.Parameters.AddWithValue("?", cmbUnderAssoc.SelectedValue)
        con.Close()
con.Open()
                    If Convert.ToInt32(compCommand.ExecuteScalar()) > 0 Then
                        MsgBox("Entry already exist! Please input new entry")
                        Exit Sub
                    Else