Inserting data to MS access from VB.NET

Question

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 25-Jul-21 14:47pm

Beginner213456

Updated 29-Jul-21 20:08pm

v2

Add a Solution

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.

2 solutions

Solution 1

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.

Posted 25-Jul-21 19:25pm

OriginalGriff

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/

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)

Beginner213456 · Accepted Answer · 2021-07-26T14:27:00

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