Click here to Skip to main content
15,034,598 members
Please Sign up or sign in to vote.
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
Updated 29-Jul-21 18:03pm
Comments
SouthP@w 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.
SouthP@w 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

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.
   
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#.
SouthP@w 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.
SouthP@w 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.
SouthP@w 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
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.
   
Comments
Code Fan 30-Jul-21 0:20am
   
You didn't read his comment, did you?
SouthP@w 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.
SouthP@w 30-Jul-21 1:45am
   
I Did do what you Advise, My Update Button is now Working. Thank you
SouthP@w 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.
SouthP@w 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.
SouthP@w 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?
SouthP@w 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
SouthP@w 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.

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