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

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.
 
Share this answer
 
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?
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.
 
Share this answer
 
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 ......

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