Click here to Skip to main content
15,075,944 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


       Try
           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)
           Myconnection.Open()

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

           cmd.ExecuteNonQuery()
           Update()

           Myconnection.Close()
           MsgBox("Data Updated")
       Catch ex As Exception
           MsgBox(ex.Message)
       End Try
Posted
Updated 29-Jul-21 20:08pm
v2
Comments
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:
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?

Fix that throughout your whole app and the problem you have noticed will go away at the same time.
   
Comments
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
OriginalGriff 28-Jul-21 1:29am
   
Try what I suggested, and tell us what you find at each step?
Richard Deeming 29-Jul-21 5:47am
   
At a guess, is there an empty copy of your Access database in your project, with the properties set to "copy to output directory: always"?
OriginalGriff 29-Jul-21 6:07am
   
That would be my guess too - but unless he looks closely ... :sigh:
Beginner213456 30-Jul-21 1:37am
   
I just read about the copy to output directory , so I changed the property of the database to "Copy If Newer"

Now it Inserts and Updates Data in my Database, even after i exit and open the app again
OriginalGriff 30-Jul-21 1:59am
   
For starters, don't store your data in the application folder.
It's a bad idea, because in production that folder is write protected to prevent virus activity and your app will fail.
Have a look here: https://www.codeproject.com/Tips/370232/Where-Should-I-Store-My-Data
Beginner213456 30-Jul-21 4:08am
   
I have read about your article but please help how to use c# in vb.net
OriginalGriff 30-Jul-21 4:39am
   
Try here:
http://converter.telerik.com/
Try
           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
           sqlconn.Open()
           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)


           sqlquery.ExecuteNonQuery()
           sqlconn.Close()

           ElementaryTableAdapter.Fill(PlanDataSet.Elementary)
           MessageBox.Show("School Added")
           Update()
       Catch ex As Exception
           MessageBox.Show(ex.Message)
       End Try
   
Comments
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