CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 12 of 12

Thread: Exception in C# 2010

  1. #1
    Join Date
    Sep 2011
    Location
    Los Angeles,United States of America.
    Posts
    11

    Exception in C# 2010

    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();
    }
    Last edited by Jack_Tauson_Sr; September 18th, 2011 at 09:29 PM.

  2. #2
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,361

    Re: Exception in C# 2010

    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.
    Last edited by DataMiser; September 18th, 2011 at 09:37 PM.
    Always use [code][/code] tags when posting code.

  3. #3
    Join Date
    Sep 2011
    Location
    Los Angeles,United States of America.
    Posts
    11

    Re: Exception in C# 2010

    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.
    Attached Images Attached Images  
    Last edited by Jack_Tauson_Sr; September 18th, 2011 at 10:19 PM.

  4. #4
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,361

    Re: Exception in C# 2010

    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.
    Always use [code][/code] tags when posting code.

  5. #5
    Join Date
    May 2007
    Location
    Denmark
    Posts
    623

    Re: Exception in C# 2010

    '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:

    Code:
            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.
    It's not a bug, it's a feature!

  6. #6
    Join Date
    Sep 2011
    Location
    Los Angeles,United States of America.
    Posts
    11

    Re: Exception in C# 2010

    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.

  7. #7
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,361

    Re: Exception in C# 2010

    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.
    Always use [code][/code] tags when posting code.

  8. #8
    Join Date
    Sep 2011
    Location
    Los Angeles,United States of America.
    Posts
    11

    Re: Exception in C# 2010

    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?

  9. #9
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,361

    Re: Exception in C# 2010

    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.
    Always use [code][/code] tags when posting code.

  10. #10
    Join Date
    Sep 2011
    Location
    Los Angeles,United States of America.
    Posts
    11

    Re: Exception in C# 2010

    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 !

  11. #11
    Join Date
    Sep 2011
    Location
    Los Angeles,United States of America.
    Posts
    11

    Re: Exception in C# 2010

    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.
    Attached Images Attached Images  
    Last edited by Jack_Tauson_Sr; September 20th, 2011 at 10:00 PM.

  12. #12
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,361

    Re: Exception in C# 2010

    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.
    Always use [code][/code] tags when posting code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)