|
P: 1
|
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() which says 'OleDbException was unhandled: Number of query values and destination fields are not the same.'
Please help me. Any suggestions would be appreciated.
| |
Share this Question
| Expert Mod 5K+
P: 9,731
|
The Insert statement expects the number of values provided to match the number of columns that you are populating.
This is what you have: -
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 & "')
Notice how there are 47 column names and only 45 values supplied?
This is the root of your problem.
Please note that it is very hard to identify if you what you are supplying as the values to your SQL insert command.
You should be using OleDbCommand.Parameters Property instead. This not only helps with readability and debugging but also avoids SQL insertion attacks and uses cache...
Consider the following: -
Dim cmdText As String = "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(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
-
Dim cmd As OleDbCommand = New OleDbCommand(cmdText, con)
-
cmd.CommandType = CommandType.Text ‘ The default is CommandType.Text
-
With cmd.Parameters
-
.Add("@Client", OleDbType.VarChar).Value = ComboBoxEx1.Text
-
.Add("@Address", OleDbType.VarChar).Value = TextBoxX1.Text
-
'...
-
' Continue adding parameters with values that match...
-
' Please note that the order in which you add your
-
' parameters must match the order of the columns
-
' in your insert statement
-
End With
-
cmd.ExecuteNonQuery()
| | | |  Question stats - viewed: 1290
- replies: 1
- date asked: Dec 28 '14
|
