-
Feb 3rd, 2014, 10:36 PM
#1
Thread Starter
Junior Member
Column count doesn't match value count at row 1
Hi, I'm having a problem inserting data from the list view to MySQL. This is what i've got so far. The error says column count doesn't match value count at row 1
Code:
Dim sql As String
Dim con = New MySqlConnection("data source=localhost; user id=root; password=pwd; database=db;")
Dim cmd = New MySqlCommand
Dim lvitem As Object
Dim iCount As Integer
Dim iLoop As Integer
iCount = ListPayroll.Items.Count()
Try
If Not ListPayroll.Items.Count = 0 Then
Do Until iLoop = ListPayroll.Items.Count
lvitem = ListPayroll.Items.Item(iLoop)
With lvitem
con.Open()
sql = "INSERT INTO attendancelist (empno, line1, time1, line2, time2, line3, time3, line4, time4, line5, time5, line6, time6) values('" & .SubItems(0).text & "','" & .SubItems(1).text & "','" & .SubItems(2).Text & "','" & .SubItems(3).Text & "','" & .SubItems(4).Text & "','" & .SubItems(5).Text & "', '" & .SubItems(6).Text & "', '" & .SubItems(7).Text & "', '" & .SubItems(8).Text & "','" & .SubItems(9).Text & "', '" & .SubItems(10).Text & "', '" & .SubItems(11).Text & "', '" & .SubItems(12).Text & "', '" & .SubItems(13).Text & "')"
cmd.Connection = con
cmd.CommandText = sql
cmd.ExecuteNonQuery()
End With
iLoop = iLoop + 1
lvitem = Nothing
con.Close()
MessageBox.Show("Record Saved!")
Loop
End If
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try
-
Feb 3rd, 2014, 10:45 PM
#2
Re: Column count doesn't match value count at row 1
This is a fine example of one of the reasons that you should parameters when inserting values into SQL code: it makes the code much more readable and therefore less error-prone. Just as the error message says, the number of values doesn't match the number of columns. Did you count them? You are specifying that data should be inserted into 13 columns but you've provided 14 values. Those counts don't match by my reckoning.
Follow the Blog link in my signature below and check out my post on Parameters In ADO.NET. If you use parameters with the same names as the columns they are intended for then it's bleedingly obvious if they don't match.
-
Feb 20th, 2018, 07:46 AM
#3
Registered User
Re: Column count doesn't match value count at row 1
Hi! Im getting the same error when I try to edit the employee information.
here is the code
rsworkinfo.AddNew()
rsworkinfo("EmpID").Value = txtid.Text
rsworkinfo("Lastname").Value = txtsname.Text
rsworkinfo("FirstName").Value = txtfname.Text
rsworkinfo("MiddleName").Value = txtmname.Text
rsworkinfo("ExtensionName").Value = txtename.Text
rsworkinfo("Department").Value = cmbdept.Text
rsworkinfo("Position").Value = cmbpos.Text
rsworkinfo("BasicSalary").Value = txtbasic.Text
rsworkinfo("DateHired").Value = DateHired.Text
rsworkinfo("Status").Value = cmbstatus.Text
rsworkinfo("Photo").Value = rawPic
rsworkinfo.Update()
MsgBox("Record successfully saved!")
connectempCtr("SELECT * FROM tblempctr WHERE ID='1'")
If rsempctr.EOF = False Then
rsempctr("Ctr").Value = strempctr
rsempctr.Update()
End If
connectwork("SELECT EmpID, LastName, FirstName, MiddleName, ExtensionName, Department, Position, BasicSalary, DateHired, Status, Photo FROM tblempworkinfo")
dsworkinfo.Tables("tblempworkinfo").Clear()
daworkinfo.Fill(dsworkinfo, rsworkinfo, "tblempworkinfo")
dtinfo.DataSource = dsworkinfo.Tables("tblempworkinfo").DefaultView
Call dipwede()
cmdbrowsw.Enabled = False
cmdsave.Enabled = False
cmdedit.Enabled = False
End If
End If
Else
rsworkinfo("LastName").Value = txtsname.Text ---it starts here
rsworkinfo("FirstName").Value = txtfname.Text
rsworkinfo("MiddleName").Value = txtmname.Text
rsworkinfo("ExtensionName").Value = txtename.Text
rsworkinfo("Department").Value = cmbdept.Text
rsworkinfo("Position").Value = cmbpos.Text
rsworkinfo("BasicSalary").Value = txtbasic.Text
rsworkinfo("DateHired").Value = DateHired.Text
rsworkinfo("Status").Value = cmbstatus.Text
rsworkinfo("Photo").Value = rawPic
rsworkinfo.Update()
MsgBox("Record successfully edited!")
connectwork("SELECT EmpID, LastName, FirstName, MiddleName, ExtensionName, Department, Position, BasicSalary, DateHired, Status, Photo FROM tblempworkinfo")
dsworkinfo.Tables("tblempworkinfo").Clear()
daworkinfo.Fill(dsworkinfo, rsworkinfo, "tblempworkinfo")
dtinfo.DataSource = dsworkinfo.Tables("tblempworkinfo").DefaultView
Call dipwede()
cmdbrowsw.Enabled = False
cmdsave.Enabled = False
cmdedit.Enabled = False
End If
On Error Resume Next
connecthistory("CREATE TRIGGER trig_Update_Employee BEFORE UPDATE ON tblempworkinfo FOR EACH ROW INSERT INTO tblemphistory VALUES(Old.EmpID, Old.LastName, Old.FirstName, Old.MiddleName, Old.ExtensionName, Old.Department, Old.Position, Old.BasicSalary, Old.DateHired, Old.Status, Old.Photo)")
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|