I'm getting the following error after running my code:

OleDbException was unhandled

"No of query values and destination fields are not the same" Because of this query I'm unable to save data.


Please help, what could be the reason?

=======================================
Here is a part of the code which could be causing the problem:

private void button1_Click(object sender, EventArgs e)
{
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType= CommandType.Text;
cmd.CommandText=" Insert into InterviewDatabase(Participant_ID,Accepted,Not_Accepted,Interview_Comments,Last_Name,First_Name,Middle_Init,D_O_B,Age,SS_Number,DL_ID,State,Race_Ethinicity,Marital_Status,No_of_Dependents,Referred_by,Previous_participant,No_of_times,Date_Interviewed,Interviewer_no_one,Interviewer_no_two,Primary_Addiction,Last_Used_one,Injection_User,Secondary_Addiction,Last_Used_two,Tertiary_Addiction,Last_Used_three,Employed,Homeless,Taking_Meds,Waiver) Values ('"+textBox1+"','"+comboBox1+"','"+textBox2+"','"+interviewcomment+"','"+lastname+"','"+FIRSTname+"','"+MIDDLEinit+"','"+Dateofbirth+"','"+Age+"','"+SSN+"','"+dlid+"','"+state+"','"+raceethnicity+"','"+maritalstatus+"','"+noofdependents+"''"+referredby+"','"+previousparticipant+"','"+nooftimes+"','"+dateinterviewed+"','"+interviewerone+"','"+interviewertwo+"','"+primaryaddiction+"','"+lastusedone+"','"+injectionuser+"','"+secondaryaddiction+"','"+lastusedtwo+"','"+tertiaryaddiction+"','"+lastusedthree+"','"+employed+"','"+homeless+"','"+takingmeds+"','"+waiver+"')" ;


cmd.Connection=myCon;
myCon.Open();
cmd.ExecuteNonQuery();
myCon.Close();
}

The Participant_ID in Insert Into ( ) corresponds to textBox1 in Values ( ).

And I'm extracting the user input for Participant_ID as ' " +textBox1+ " ' . Isn't it correct?

Please correct me if I'm wrong.
Please find the attached image file of the message.

Thanks

Attachments Untitled.png 219.33 KB

"No of query values and destination fields are not the same"

That's a very clear error statement. Did you check that your fields and values do match in SQL statement?
You're storing everything as a string. Does your data contain apostrophe (') somewhere? If it does you have to replace it with two apostrophes in your SQL statement. For example "You're" should be "You''re".

HTH

Yes, there are 32 fields in the Insert option and 32 in the values option as well.

I'm entering every thing as a string just to be as simple as possible. There is not apostrophes in my data.

Could you please tell whether I'm extracting the data in a correct way?
For example, for Participant_ID I'm extracting it as follows:

'" +textBox1 "'

I'm new to C# hence trying to keep things simple.Please do let me know if there is an mistake.

you'll want to use textBox1.Text otherwise you'll just get the class name as your values. You'll need other parameters for the other controls, and I have no idea what types most of the other stuff is.

You'd also be better off learning to use Parameters. It will save you from a lot of input problems (like SQL injection attacks).

One thing you can do is hover your mouse over the command text variable and see what is actually being executed.

Edited 5 Years Ago by Momerath: n/a

Thanks for your reply!

I have added .Text in every option of Insert into like textBox1.Text and so on...

But I'm getting a new error this time.

It says, Could not find the out put table ' InterviewDatabase'.

I have already linked it properly and tested the connection but still I don't know why it's showing this message.

Could you please throw some light on this.

Edited 5 Years Ago by aadarsh_khare: n/a

Actually, the table name is InterviewInfo, I corrected it. But now I'm getting another execption which says
"oledb exception was unhandled overflow"

Could you please tell me what could be the reason? And even my forms are not closing, I have to close them every time using Task Manager !

Hi,

I have changed all the fields in my MS Access database file to text but still I'm getting an exception which display's the following message:
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

Please help what could be the reason?I'm searching t on google as well but please let me know if you feel something went wrong, Thanks for your itme.

Edited 5 Years Ago by aadarsh_khare: Attaching a file

Attachments ExceptionProblem._.png 196.84 KB

did you check if all the columns of your DB is string or does it support any other data types, if there is an 'int' or 'datatime' and if you are passing a string to it, you will surely get an exception in your DAL layer, check with this first.

in your query the syntax is correct but it is not executed i think it is becaus of ; in the last inside" is missing hop this will work

This article has been dead for over six months. Start a new discussion instead.