Click here to Skip to main content
15,506,981 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
My Insert/Add Button is now working fine

Problem: How to check if a record has duplicate and if there is, t would show some error.

I used the parameterized query for update and Insert.
I am not familiar on how insert records and avoid Duplicates.

Info: SchoolName is my Primary Key

What I have tried:

Dim cmd As OleDbCommand
       Dim Myconnection As OleDbConnection

           Myconnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\PLAN.mdb")
           cmd = New OleDbCommand("UPDATE Elementary SET KM=?,KF=?,KT=? WHERE SchoolName=?", Myconnection)

           cmd.Parameters.AddWithValue("@KM", KMTextBox.Text)
           cmd.Parameters.AddWithValue("@KF", KFTextBox.Text)
           cmd.Parameters.AddWithValue("@KT", KTTextBox.Text)


           MsgBox("Data Updated")
       Catch ex As Exception
       End Try
Updated 29-Jul-21 21:08pm
Richard MacCutchan 26-Jul-21 3:58am    
So you found a perfect example of the worst way to work with databases.
Beginner213456 26-Jul-21 21:03pm    
yeah, and all will appreciate if you we're to provide the correct code or correct the code, no one will appreciate your negative criticism
Richard MacCutchan 27-Jul-21 3:56am    
And we would appreciate if you actually explained exactly where the error occurred so we would have a clue as to what is wrong.

This is why following random tutorials on the internet is a bad idea ... :sigh:

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:
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:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
A perfectly valid "delete the table" command
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?

Fix that throughout your whole app and the problem you have noticed will go away at the same time.
Share this answer
Beginner213456 26-Jul-21 20:20pm    
Thank you for ur advice, i have read about parameterized queries. and found out i have been doing it wrong,
please check on these new codes i've written,
Problem: Inserts data to the Access database but when you run the program again, and check the database again, the inserted data is removed from the Access Database.
OriginalGriff 27-Jul-21 0:43am    
How did you check that?
Exactly what did you check with, and when - remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with!
Beginner213456 27-Jul-21 2:05am    
Like when you run the vb project and insert some data. then you open the access database from its location. you CAN check if the data is inserted.
Am i a little clear or still blurry?
OriginalGriff 27-Jul-21 2:43am    
Blurry. :D

What I'd be doing is: open the file in Access and check the rows.
Start my app in the debugger.
Check the rows in access again.
Put a breakpoint just before I executed the INSERT. When it hits, check the DB with Access, and the actual data with the debugger.
Single step the INSERT.
Check the DB with Access.
Continue the app.
Check the DB with Access.
Exit the app.
Check the DB with Access.
Run the app in the debugger again.
Check the DB with Access.

At some point, it'd be obvious that something I expected to happen didn't, or something I didn't expect to happen did. Then I can look more closely at exactly what happened and start working out why.

Just running the app and checking a file doesn't prove anything, except "something happened that I didn't expect" which isn't helpful! :laugh:
Beginner213456 27-Jul-21 20:19pm    
Yes, its what i wanted to say, sorry. i just cant seem to think the best way to explain it
           Dim sqlconn As New OleDb.OleDbConnection
           Dim sqlquery As New OleDb.OleDbCommand

           connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\Plan.mdb"
           sqlconn.ConnectionString = connstring
           sqlquery.Connection = sqlconn
           sqlquery.CommandText = "INSERT INTO Elementary([SchoolName],[KM],[KF],[KT],[G1M],[G1F],[G1T],[G2M],[G2F],[G2T],[G3M],[G3F],[G3T],[G4M],[G4F],[G4T],[G5M],[G5F],[G5T],[G6M],[G6F],[G6T],[ESTOTAL],[SY])VALUES(@Schoolname,@KM,@KF,@KT,@G1M,@G1F,@G1T,@G2M,@G2F,@G2T,@G3M,@G3F,@G3T,@G4M,@G4F,@G4T,@G5M,@G5F,@G5T,@G6M,@G6F,@G6T,@ESTOTAL,@SY)"
           sqlquery.Parameters.AddWithValue("@SchoolName", SchoolNameTextBox.Text)
           sqlquery.Parameters.AddWithValue("@KM", G1MTextBox.Text)
           sqlquery.Parameters.AddWithValue("@KF", G1MTextBox.Text)
           sqlquery.Parameters.AddWithValue("@KT", G1MTextBox.Text)
           sqlquery.Parameters.AddWithValue("@G1M", G1MTextBox.Text)
           sqlquery.Parameters.AddWithValue("@G1F", G1FTextBox.Text)
           sqlquery.Parameters.AddWithValue("@G1T", G1TTextBox.Text)
           sqlquery.Parameters.AddWithValue("@G2M", G2MTextBox.Text)
           sqlquery.Parameters.AddWithValue("@G2F", G2FTextBox.Text)
           sqlquery.Parameters.AddWithValue("@G2T", G2TTextBox.Text)
           sqlquery.Parameters.AddWithValue("@G3M", G3MTextBox.Text)
           sqlquery.Parameters.AddWithValue("@G3F", G3FTextBox.Text)
           sqlquery.Parameters.AddWithValue("@G3T", G3TTextBox.Text)
           sqlquery.Parameters.AddWithValue("@G4M", G4MTextBox.Text)
           sqlquery.Parameters.AddWithValue("@G4F", G4FTextBox.Text)
           sqlquery.Parameters.AddWithValue("@G4T", G4TTextBox.Text)
           sqlquery.Parameters.AddWithValue("@G5M", G5MTextBox.Text)
           sqlquery.Parameters.AddWithValue("@G5F", G5FTextBox.Text)
           sqlquery.Parameters.AddWithValue("@G5T", G5TTextBox.Text)
           sqlquery.Parameters.AddWithValue("@G6M", G6MTextBox.Text)
           sqlquery.Parameters.AddWithValue("@G6F", G6FTextBox.Text)
           sqlquery.Parameters.AddWithValue("@G6T", G6TTextBox.Text)
           sqlquery.Parameters.AddWithValue("@ESTOTAL", ESTOTALTextBox.Text)
           sqlquery.Parameters.AddWithValue("@SY", SYTextBox.Text)


           MessageBox.Show("School Added")
       Catch ex As Exception
       End Try
Share this answer
Richard Deeming 29-Jul-21 5:46am    
If you want to update your question, click the green "Improve question" link and edit your question.

Or, if you've solved your problem and now have a new problem, mark Solution 1 as the accepted solution, and post a new question.

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