Exception in C# 2010

[Jack_Tauson_Sr]

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();
}

[DataMiser]

As the message suggests you have a mismatched number of fields. Either you are missing one of the first 3 in the top part below or you have one that should not be there in the second part below.

Participant_ID,Accepted,Not_Accepted,Interview_Comments
textBox1+"','"+comboBox1+"','"+textBox2+"','"+interviewcomment


Also if those are supposed to be values from textboxes then you need to use the .Text property on them as well.

[Jack_Tauson_Sr]

Thanks for the reply DataMiser.

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?


There are 32 fields in the Insert into ( ) option and corresponding to each 32 fields there are 32 fields in the Values ( ) option also.I'm still not understanding why I'm unable to store the data.

Please correct em if I'm wrong.


I have attached image of my error as an attachment.Please check if possible.

[DataMiser]

My eye sight is not as good as it used to be and your SQL string is almost impossible to read as written. I did not see the coma between accepted,notaccepted.


Here is your issue
,'"+noofdependents+"''"+referredby+"','"+


You really should break that up into more lines and use some spaces to make it readable.

[foamy]

'textBox1' gets a reference to your textbox, not the text in it. You would need to use textBox1.Text to get the text.

Also, I would recommend you use parameters to hold the values of your INSERT statement, like so:

        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 (@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)";

            cmd.Parameters.Add(new OleDbParameter("@Participant_ID", textBox1.Text));
            cmd.Parameters.Add(new OleDbParameter("@Accepted", comboBox1.Text));
            cmd.Parameters.Add(new OleDbParameter("@Not_Accepted", textBox2.Text));
            cmd.Parameters.Add(new OleDbParameter("@Interview_Comments", interviewcomment));
            cmd.Parameters.Add(new OleDbParameter("@Last_Name", lastname));
            cmd.Parameters.Add(new OleDbParameter("@First_Name", FIRSTname));
            cmd.Parameters.Add(new OleDbParameter("@Middle_Init", MIDDLEinit));
            cmd.Parameters.Add(new OleDbParameter("@D_O_B", Dateofbirth));
            cmd.Parameters.Add(new OleDbParameter("@Age", Age));
            cmd.Parameters.Add(new OleDbParameter("@SS_Number", SSN));
            cmd.Parameters.Add(new OleDbParameter("@DL_ID", dlid));
            cmd.Parameters.Add(new OleDbParameter("@State", state));
            cmd.Parameters.Add(new OleDbParameter("@Race_Ethinicity", raceethnicity));
            cmd.Parameters.Add(new OleDbParameter("@Marital_Status", maritalstatus));
            cmd.Parameters.Add(new OleDbParameter("@No_of_Dependents", noofdependents));
            cmd.Parameters.Add(new OleDbParameter("@Referred_by", referredby));
            cmd.Parameters.Add(new OleDbParameter("@Previous_participant", previousparticipant));
            cmd.Parameters.Add(new OleDbParameter("@No_of_times", nooftimes));
            cmd.Parameters.Add(new OleDbParameter("@Date_Interviewed", dateinterviewed));
            cmd.Parameters.Add(new OleDbParameter("@Interviewer_no_one", interviewerone));
            cmd.Parameters.Add(new OleDbParameter("@Interviewer_no_two", interviewertwo));
            cmd.Parameters.Add(new OleDbParameter("@Primary_Addiction", primaryaddiction));
            cmd.Parameters.Add(new OleDbParameter("@Last_Used_one", lastusedone));
            cmd.Parameters.Add(new OleDbParameter("@Injection_User", injectionuser));
            cmd.Parameters.Add(new OleDbParameter("@Secondary_Addiction", secondaryaddiction));
            cmd.Parameters.Add(new OleDbParameter("@Last_Used_two", lastusedtwo));
            cmd.Parameters.Add(new OleDbParameter("@Tertiary_Addiction", tertiaryaddiction));
            cmd.Parameters.Add(new OleDbParameter("@Last_Used_three", lastusedthree));
            cmd.Parameters.Add(new OleDbParameter("@Employed", employed));
            cmd.Parameters.Add(new OleDbParameter("@Homeless", homeless));
            cmd.Parameters.Add(new OleDbParameter("@Taking_Meds", takingmeds));
            cmd.Parameters.Add(new OleDbParameter("@Waiver", waiver));

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

Furthermore, it seems like poor database design to have all the information stored in one table (unfortunately, it's not that uncommon). You should look into splitting things up a bit.

[Jack_Tauson_Sr]

Thank you Datamiser and Foamy.

Datamiser, Thank you for pointing out the error.I really appreciate it and sorry for the inconvenience in viewing my data.

I have added .Text in every option of Insert into like textBix1.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.

[DataMiser]

What do you mean by "linked it properly"?

Does your database contain a table named Interviewdatabase?
If you are using SQL Server do you have the initial database set to the one you should be using?

Most likely it ios one of those, or the spelling of the table name does not match that in the db.

[Jack_Tauson_Sr]

DataMiser,

I forgot to mention that I'm using MS Access to store the data. Yes, the database name is InterviewDatabase.

Do,I need to check any connection string in this case?

[DataMiser]

If the database name is InterviewDatabase then you have a problem or perhaps you are just confused on the proper terms to use.

Database = Collection of Tables containing rows of fields as well as other objects, views, procedures and so on.

Table= where the rows of data are located.

You specify the Database in your connection string.
You specify the Table in your SQL Statements.

So unless you have a table in your database and that table name is interviewdatabase then you have an error there as is mentioned in the error message when it tells you that table does not exist.

btw InterviewDatabase is a fine name for a database. It would be a horrible name for a table.

[Jack_Tauson_Sr]

Datamiser,

Actually, the table name is InterviewInfo, I corrected it. Thanks for the suggestion. 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 !

[Jack_Tauson_Sr]

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.

[DataMiser]

The first error you mention, Overflow is caused when you try to put a number into a space that is not large enough to hold that number.

The second error indicates that you have one or more fields set in your database to require unique data and you are trying to insert something that is already there. The message pretty much tells you what the problem is and what you need to do about it.

As for your forms not closing hard to say without seeing the code but most likely you are not disposing of something you have created and it is holding the form(s) open.