Results 1 to 3 of 3

Thread: Column count doesn't match value count at row 1

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jan 2012
    Posts
    25

    Exclamation 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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    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.

  3. #3
    Registered User
    Join Date
    Feb 2018
    Posts
    1

    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
  •  



Click Here to Expand Forum to Full Width