Click here to Skip to main content
14,735,709 members
Please Sign up or sign in to vote.
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)

  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
Updated 26-Oct-20 7:33am
v2
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.

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


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:
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
   
Comments
Jake Robert 5-Aug-15 4:44am
   
Gonna try it maybe tomorrow. My head is already spinning. I will update you as soon as I test your solution.

Thanks :)
Jake Robert 5-Aug-15 20:50pm
   
It worked as well when I am adding/inserting new entry but still, it blocks the currently selected entry when updating. It also reads it as a duplicate even when updating.

Do I have to create another Query and Command when updating?
Update[^] statement is used to update record(s) based on criteria. So, it can't create duplicates!

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


Finally, your query should look like:
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.
   
Simple solution is to delete that particular record and with updates insert again as new record that matches the exact criteria when adding.
   
Comments
CHill60 27-Oct-20 7:16am
   
Very wasteful approach and not recommended

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