OleDbException was unhandled: Number of query values and destination fields are not the same in VB.NET with MS Access

Question

0.00/5 (No votes)

See more:

Dear fellow programmers,

I am coping with this problem for 6 days already and nearly gonna give up. All I want is when I click the 'Save' button it will add to the database and datagridview. This is my following code:

In Module 2:

Module Module2
    Public con As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\EncodingComplete.accdb")
End Module

In frmEncode:

Public Class frmEncode
    Public Sub Add_Data()

        con.Open()

        Dim rs As New OleDb.OleDbCommand("Insert Into EncodingComplete(Client, Address, Date_Bill, BusStyle_Name, Vessel, TIN, Port, [Pier No], InterPleader1, InterPleader2, Call_Date, Call_Time, Call_VoyageNo, Call_ReceiveBy, VesselType, VesselCalls, FixedFee_ExcessCum, FixedFee_Amount, FixedFee_Rate, FixedFee_Total, OilyExcessCum, OilyRate, OilyAmnt, OilyTotal, GarbageExcessCum, GarbageRate, GarbageAmnt, GarbageTotal, NoxiousExcessCum, NoxiousRate, NoxiousAmnt, NoxiousTotal, Other, OtherExcessCum, OtherRate, OtherAmnt, OtherTotal, DollarConvers, AmountDue, PaymentDetails, TypeofPayment, DateofPayment, OrNo, Amount, Collector, BI, CSR) VALUES('" & ComboBoxEx1.Text & "', '" & TextBoxX1.Text & "', '" & DateTimePicker1.Text & "', '" & TextBoxX11.Text & "', '" & TextBoxX2.Text & "', '" & TextBoxX3.Text & "', '" & ComboBoxEx2.Text & "', '" & TextBoxX12.Text & "', '" & ComboBoxEx4.Text & "', '" & ComboBoxEx5.Text & "', '" & TextBoxX6.Text & "', '" & TextBoxX9.Text & "', '" & TextBoxX4.Text & "', '" & TextBoxX10.Text & "', '" & ComboBoxEx3.Text & "', '" & ComboBoxEx8.Text & "', '" & TextBoxX14.Text & "', '" & TextBoxX40.Text & "', '" & TextBoxX31.Text & "', '" & TextBoxX49.Text & "', '" & TextBoxX16.Text & "', '" & TextBoxX29.Text & "', '" & TextBoxX38.Text & "', '" & TextBoxX47.Text & "', '" & TextBoxX15.Text & "', '" & TextBoxX30.Text & "', '" & TextBoxX39.Text & "', '" & TextBoxX48.Text & "', '" & TextBoxX17.Text & "', '" & TextBoxX28.Text & "', '" & TextBoxX37.Text & "', '" & TextBoxX46.Text & "', '" & TextBoxX13.Text & "', '" & TextBoxX18.Text & "', '" & TextBoxX8.Text & "', '" & TextBoxX36.Text & "', '" & TextBoxX45.Text & "', '" & TextBoxX50.Text & "', '" & TextBoxX51.Text & "', '" & TextBoxX52.Text & "', '" & ComboBoxEx7.Text & "', '" & TextBoxX53.Text & "', '" & TextBoxX54.Text & "', '" & TextBoxX55.Text & "', '" & TextBoxX56.Text & "')", con)
        rs.ExecuteNonQuery()
        con.Close()
        Display_Data()
    End Sub

    Private Sub Display_Data()
        con.Open()
        Dim dt As New DataTable("EncodingComplete")
        Dim rs As New OleDb.OleDbDataAdapter("Select * from EncodingComplete", con)
        rs.Fill(dt)
        DataGridViewX1.DataSource = dt
        DataGridViewX1.Refresh()

        rs.Dispose()

        con.Close()

    End Sub
    
    Private Sub ButtonX10_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX10.Click
        Add_Data()

        
    End Sub

The error throws at

rs.ExecuteNonQuery()

saying OleDbException was unhandled: Number of query values and destination fields are not the same.

Please help me. Any suggestions would be appreciated.

Posted 27-Dec-14 23:14pm

Member 11330734

Add a Solution

1 solution

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)

DamithSL · Accepted Answer · 2014-12-28T00:03:00

you have 47 column names in your insert statements but you only provide 45 values. it is difficult to check which column value is missing because of you haven't follow meaningful names for the controls. change the control names to meaningful names and at the sametime create the insert statement with giving column name and value.
Also try to use Parameterized Query, read
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters(v=vs.110).aspx[^]