Updating MS access using VB.NET

Question

0.00/5 (No votes)

See more:

Problem:
Can't Update my Access Database

Error: No value Given for one or more required parameters.

Additional Info:
I'm using SchoolName as my PK. so iremoved it from the Update SQL.
Also I dont know if my where clause is correct or not.
OR the whole Update SQL is Wrong.
PLease Help

What I have tried:

Dim cmd As OleDbCommand
       Dim con As OleDbConnection

       Try
           con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\PLAN.mdb")
           cmd = New OleDbCommand("Update Elementary SET KM=?,KF=?,KT=?,G1M=?,G1F=?,G1T=?,G2M=?,G2F=?,G2T=?,G3M=?,G3F=?,G3T=?,G4M=?,G4F=?,G4T=?,G5M=?,G5F=?,G5T=?,G6M=?,G6F=?,G6T=?,ESTOTAL=?,SY=? where KM=?", con)
           con.Open()


           cmd.Parameters.AddWithValue("@KM", KMTextBox.Text)
           cmd.Parameters.AddWithValue("@KF", KFTextBox.Text)
           cmd.Parameters.AddWithValue("@KT", KTTextBox.Text)
           cmd.Parameters.AddWithValue("@G1M", G1MTextBox.Text)
           cmd.Parameters.AddWithValue("@G1F", G1FTextBox.Text)
           cmd.Parameters.AddWithValue("@G1T", G1TTextBox.Text)
           cmd.Parameters.AddWithValue("@G2M", G2MTextBox.Text)
           cmd.Parameters.AddWithValue("@G2F", G2FTextBox.Text)
           cmd.Parameters.AddWithValue("@G2T", G2TTextBox.Text)
           cmd.Parameters.AddWithValue("@G3M", G3MTextBox.Text)
           cmd.Parameters.AddWithValue("@G3F", G3FTextBox.Text)
           cmd.Parameters.AddWithValue("@G3T", G3TTextBox.Text)
           cmd.Parameters.AddWithValue("@G4M", G4MTextBox.Text)
           cmd.Parameters.AddWithValue("@G4F", G4FTextBox.Text)
           cmd.Parameters.AddWithValue("@G4T", G4TTextBox.Text)
           cmd.Parameters.AddWithValue("@G5M", G5MTextBox.Text)
           cmd.Parameters.AddWithValue("@G5F", G5FTextBox.Text)
           cmd.Parameters.AddWithValue("@G5T", G5TTextBox.Text)
           cmd.Parameters.AddWithValue("@G6M", G6MTextBox.Text)
           cmd.Parameters.AddWithValue("@G6F", G6FTextBox.Text)
           cmd.Parameters.AddWithValue("@G6T", G6TTextBox.Text)
           cmd.Parameters.AddWithValue("@ESTOTAL", ESTOTALTextBox.Text)
           cmd.Parameters.AddWithValue("@SY", SYTextBox.Text)

           cmd.ExecuteNonQuery()
           MsgBox("Record Updated")
           con.Close()

       Catch ex As Exception
           MessageBox.Show(ex.Message)
       End Try

Posted 29-Jul-21 15:24pm

Beginner213456

Updated 29-Jul-21 18:03pm

Add a Solution

Comments

Beginner213456 29-Jul-21 23:32pm

I have changed the KM in the Where Clause to SY=? and removed the SY=? in the Update Command,,,, it updated the database, now the problem is when i updated 1 record, it also updates the value of some record in the cloumn. Can anybody Explain what have i encountered,

Dave Kreskowiak 30-Jul-21 0:22am

What's the primary key of the table? That's what you should be searching for in the WHERE clause. You should also NOT be setting that value in the SET clause.

Beginner213456 30-Jul-21 1:46am

I am using the SchoolName as my primary key and i replaced my where clause with schoolname and supplied it using the parameters. now its working fine . thank you

2 solutions

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)

Code Fan · Accepted Answer · 2021-07-29T17:58:00

Solution 1

If SchoolName is your primary key & you want to ensure you update 1 record at a time, you probably want to use it in your WHERE clause instead of anything else. Also, it's a good idea to explicitly name your parameters rather than use ? for them, like SY = @SY. It's harder to miss parameters that way, as you previously did.

Posted 29-Jul-21 17:58pm

Code Fan

Comments

Dave Kreskowiak 30-Jul-21 0:13am

Access doesn't use named parameters. You have to specify them in the order you want them put into the ? in the query string.

Code Fan 30-Jul-21 0:18am

That bad? Oh my goodness. Sorry about my incorrect advice. However, he probably does want to use the primary key in his WHERE clause.

Dave Kreskowiak 30-Jul-21 0:21am

Look closer at the statement. He's using KM in the WHERE clause and setting it in the SET clause. That's usually a no no.

Code Fan 30-Jul-21 0:24am

Based on his comment, he wants to ensure 1 row to change only. That usually means filtering by the primary key.

Dave Kreskowiak 30-Jul-21 12:00pm

Yeah, I know that.

The problem is this:

UPDATE table SET column1 = someValue WHERE column1 = someValue

Not very useful, is it?

Code Fan 30-Jul-21 13:11pm

I tend to be very tolerant when answering questions here. You can write a million rules as the best practices, only to overwhelm a developer. Your example, while demonstrating how useless this practice is, does no harm or whatsoever, absolutely something I can live with. In real life, I simply don't recommend developers to write SQL. They either use Linq to generate SQL or make their own abstract syntax trees for SQL. This way, SQL gets some compile-time checks in C#.

Beginner213456 1-Aug-21 20:27pm

you seem very knowledgeable sir, so may we know the useful line of codes you got there? or better code than these ones?

Code Fan 1-Aug-21 21:40pm

I might cover that in my future articles. It's a big job to create abstract syntax trees for SQL. Linq while good doesn't support INSERT/UPDATE/DELETE. For a small project & in a small company, you're already doing a very good job. In a big company, you'll have DBA's writing stored procedures for you. You just have to call.

Beginner213456 30-Jul-21 0:43am

I also tried it before but i have read some answers saying access doesn't support named parameters. so i'm not sure if "SY=@SY" is considered a Named Parameter?

Code Fan 30-Jul-21 1:30am

He's probably right. Access may not support named parameters, so you must use question marks. It's just too bad.

Beginner213456 30-Jul-21 1:49am

I still followed the
Update elementary Set KM=? where SchoolName=?
cmd.parameters.AddWithValue("@KM",KMTextbox.Text)
cmd.parameters.AddWithValue("SchoolName",SchoolNameTextBox.Text)

and its working fine, Thank you

Dave Kreskowiak · Accepted Answer · 2021-07-29T18:03:00

Solution 2

Access doesn't use named parameters. If you've got a value that's used more than once, like your KM in the UPDATE and the WHERE clause, you have to specify it twice.

You query string has 24 parameters and you supplied 23.

Oh, and you do not need to set the value of KM if you're going to use it in the WHERE clause.

Posted 29-Jul-21 18:03pm

Dave Kreskowiak

Comments

Code Fan 30-Jul-21 0:20am

You didn't read his comment, did you?

Beginner213456 30-Jul-21 0:48am

Thank you both for the responses.
I have a question to ask,
If I will use the SchoolName in the Where Clause instead of the KM
should i also Supply it
like :
cmd.parameters.addwithvalue("@SchoolName",SchoolName.Text)

Code Fan 30-Jul-21 1:32am

Of course, you must supply them all in order. Without named parameters, only order matters.

Beginner213456 30-Jul-21 1:45am

I Did do what you Advise, My Update Button is now Working. Thank you

Beginner213456 30-Jul-21 0:41am

Thank you for your quick responses.
So is it Okay if i replace KM with SchoolName=? .
or should i change all into KM=@KM,KF=@KF ......

Dave Kreskowiak 30-Jul-21 12:02pm

You should not be using a school name, or any other field dedicated to your data, as a primary key. In your case, it's not possible to change the name of the school if it gets renamed.

The primary key should be an autonumber field. That way, the database generates the key for each record itself.

Beginner213456 1-Aug-21 20:35pm

Yes , i have updated my database, im now using ID as PK

Now my problem is windows defender is blocking my app from accessing my database, what should i do?
or are there articles on how to deal with it?

Dave Kreskowiak 2-Aug-21 9:55am

I have no idea what Defender is complaining about and have never heard of it blocking an app for writing to an Access database.

Beginner213456 2-Aug-21 23:15pm

The warning that Pops-up from the Wndows Defender:

Unauthorized Changed Blocked
Controlled Folder Access Plan.vhost.exe from making changes

It happens when i Start the Debugger from the vs10 and insert data to ms access.

Dave Kreskowiak 2-Aug-21 23:27pm

OK, so what's the file path to the Access database?

Beginner213456 3-Aug-21 2:29am

C:\Users\P A R\Documents\Visual Studio 2010\Projects\Planz\Planz\bin\Debug\Plan.mdb

\\"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\PLANz.mdb"\\

Dave Kreskowiak 3-Aug-21 9:16am

Turn off Controlled Folder Access.

https://answers.microsoft.com/en-us/protect/forum/all/windows-10-home-unauthorized-changes-blocked/1feba668-4273-4a1b-8cd3-ac6485cb4280

Beginner213456 3-Aug-21 21:21pm

It Worked Thanks

Dave Kreskowiak 3-Aug-21 22:21pm

Seriously? Read what you typed again. Assume I know nothing about what you're doing (because I don't.)

Who is "they"? What is "better to use the TextChanged Event"? Better than what? What is this code snippet doing? What is it not doing? What is it that you're trying to achieve?

Context in a conversation is everything, and you left it out of this post.