Error in my codes; Where and How do I do away with it pls?

Question

0.00/5 (No votes)

See more:

This set of codes is intended to modify or update values in access Database.

C#

query = " update myDbase set NAMES =  '"+ txtName+"', CLASS = '"+txtClass+"', AGE = '"+txtAge+"' where YEAR = '"+txtYear+"', COURSE = '"+txtCourse+"'";

oleCon.Open();
oleAdpt.UpdateCommand = oleCon.CreateCommand();
oleAdpt.UpdateCommand.CommandText = query;
oleAdpt.UpdateCommand.ExecuteNonQuery();
oleCon.Close();

Always showing Error: " No value given for one or more required parameters."
It was perfect yesterday with only one column but since 6:00GMT, I don't know what happened. Nothing is working.
Please help!
Thank you.

Posted 28-Dec-14 22:20pm

Salisu Shaibu

Updated 28-Dec-14 22:37pm

ridoy

v4

Add a Solution

Comments

Salisu Shaibu 29-Dec-14 4:28am

Note: All the txt(s) in the query are with .Text. THANK YOU!

Richard MacCutchan 29-Dec-14 4:33am

Obviously one of the text fields does not contain any data. You should use your debugger to find out why. You should also learn how to use proper parameterised queries in your commands.

Salisu Shaibu 29-Dec-14 7:10am

Appreciating you all for the time and effort, neither of your opinions got me to destination.
But I noticed something after discarding the query, build it bit by bit while I test all my moves.
Problem only set in whenever I include "where ....." but I need to specify where to edit else all cells in a column ends up updated.
Assist me to move on please.
Thanks.

Richard MacCutchan 29-Dec-14 9:46am

If you only wish to edit certain cells, then you need to modify your SQL statement. There are many examples to be found on the internet, for example http://www.w3schools.com/sql/sql_update.asp.

Salisu Shaibu 29-Dec-14 10:17am

Thanks for the info.
I'm going right away.
Thuogh I've solve it myself.
Thanks.

6 solutions

Solution 2

First of all - you should be off reading about "Sql Injection".

When you are back: replace , with AND in your WHERE clause and make sure all the test-fields have data. That should do it.

Posted 28-Dec-14 22:34pm

Agent__007

Updated 28-Dec-14 22:35pm

v2

Comments

Salisu Shaibu 29-Dec-14 7:13am

I replaced the ',' with 'and'.
It didn't work.
Appreciating you all for the time and effort, I noticed something after discarding the query, build it bit by bit while I test all my moves.
Problem only set in whenever I include "where ....." but I need to specify where to edit else all cells in a column ends up updated.
Assist me to move on please.
Thanks.

Solution 3

Um.

SQL

... where YEAR = '"+txtYear+"', COURSE = '"+txtCourse+"'";

Did you mean

SQL

... where YEAR = '"+txtYear+"' AND COURSE = '"+txtCourse+"'";

But please, don't do that. Do not 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. Use Parametrized queries instead.

Posted 28-Dec-14 22:35pm

OriginalGriff

Solution 4

Hi,

Here in your code you have used like "txtName". If this is a text box then you need to use the "txtName.Text" to get the values rendered.

Please check in your code and replace the things properly there in the query and try again.

Thanks
Sisir Patro

Posted 28-Dec-14 22:37pm

Sisir Patro

Comments

Salisu Shaibu 29-Dec-14 7:20am

I use phone, I skiped some, they are all .Text.
Appreciating you all for the time and effort, I noticed something after discarding the query, I build it bit by bit while I test all my moves.
Problem only set in whenever I include "where ....." but I need to specify where to edit else all cells in a column ends up updated.
Assist me to move on please.
Thanks.

[no name] 30-Dec-14 0:35am

You have to use the where clause here at any cost. I think your code is having some issues with the parameters in the where clause. You can go for the parameterized query like the following.

var con = new OleDbConnection(connectionString);
var cmd = new OleDbCommand("select * from tableName where [MRN#]=@c", con);
cmd.Parameters.Add("@c", "33264");

Try this one and check again.

Thanks

Solution 6

can you try removing the single quote in the query

C#

query = " update myDbase set NAMES =  "+ txtName+", CLASS = "+txtClass+", AGE = "+txtAge+" where YEAR = "+txtYear+", COURSE = "+txtCourse;

Posted 28-Dec-14 22:55pm

Vibusuna

Comments

Salisu Shaibu 29-Dec-14 7:09am

Appreciating you all for the time and effort, neither of your opinions got me to destination.
But I noticed something after discarding the query, build it bit by bit while I test all my moves.
Problem only set in whenever I include "where ....." but I need to specify where to edit else all cells in a column ends up updated.
Assist me to move on please.
Thanks.

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)

Manoj Kumar Choubey · Accepted Answer · 2014-12-28T22:44:00

Solution 5

I think, In the excel sheet remove blank space from the name of column/Fields .....
also check the values you are trying to update check any character creating problem.

Posted 28-Dec-14 22:44pm

Manoj Kumar Choubey

Comments

Salisu Shaibu 29-Dec-14 7:21am

I've checked but all is intact.Appreciating you all for the time and effort, I noticed something after discarding the query, I build it bit by bit while I test all my moves.
Problem only set in whenever I include "where ....." but I need to specify where to edit else all cells in a column ends up updated.
Assist me to move on please.
Thanks.

Manoj Kumar Choubey 29-Dec-14 7:59am

I think you replaced comma (, ) with and in where
Please check year and COURSE values in excel sheet or database and is this numeric or text test with simple and small data (with 2 or 4 rows of data )
first and then check with big sheet and try to find the value that occurred problem. put try catch and find the position of row where error occurred. and check manually in sheet at the row number.

Salisu Shaibu 29-Dec-14 8:17am

I understand; still remain.
Let me put it this way:
My query is working well without "where...." even if I use only " .... where YEAR = 'year'"; or ".... where [YEAR] = 'year'"; in string format.
If dont specify a row, the whole field gets update automatically.

Manoj Kumar Choubey 29-Dec-14 9:24am

Open excel sheet select Year column and check its data type , fix the datatype of the columns and then check.....

Salisu Shaibu · Accepted Answer · 2014-12-29T03:31:00

Thank you all guys!
I finally found out the cause of mh problem after 6 hours atleast.
I don't know if I'm dull.
Is this how others suffer for too long to figure out solutions personally?
I listed field names on the query, non serial to the order found on the database.
It a pleasure.
Thanks a bunch.

Error in my codes; Where and How do I do away with it pls?

6 solutions

Solution 2

Solution 3

Solution 4

Solution 5

Solution 6

Solution 7

Add your solution here

Preview 0

Existing Members

...or Join us