Column count does't not match value count at row 1

Question

I'm having a problem about my code, i already tried to put the specified table for each column, In which part of my code was having a problem?

clientid = clientid.Substring(0, 3)
rnd = random.Next(100, 999)

clientid = clientid & "-" & Format(Now, "MMdd") & "-" & rnd
query = "SELECT COUNT(*) FROM tbl_clients WHERE clients_Record_Num ='" & clientid & "'"
sqlcmd = New MySqlCommand(query, conn)
chkclientid = sqlcmd.ExecuteScalar()

While chkclientid > 0
    clientid = clientid.Substring(0, 3)
    rnd = random.Next(100, 999)

    clientid = clientid & "-" & Format(Now, "MMdd") & "-" & rnd
    query = "SELECT COUNT(*) FROM tbl_clients WHERE clients_record_num ='" & clientid & "'"
    sqlcmd = New MySqlCommand(query, conn)
    chkclientid = sqlcmd.ExecuteScalar()
End While

query = "Insert into tbl_clients(clients_record_num,clients_client_id,clients_name,clients_contact_number,clients_address,clients_industry,clients_status,clients_delegate,clients_notes) values ('" & clientid.Substring(0, 3) &_ "-" & dttime & "-" & rnd &_ "','" & clientid & _
        "', '" & txtClientName.Text & "','" & txtClientContactNum.Text & "','" & txtClientAddress.Text & _
        "','" & cmbIndustry.Text & "', '" & cmbStatus.Text & "', '" & cmbDelegate.Text & "','" & txtNotes.Text & "','0')"
sqlCommand.Connection = conn
sqlCommand.CommandText = query
sqlCommand.ExecuteNonQuery()

The debugger would have told you exactly where the exception was thrown so the section of code that is the issue should have been obvious. It would have been the `ExecuteNonQuery` call that threw the exception so obviously it's the section leading up to that that is the problem. Given that the `INSERT` statement is the only place you're specifying columns and values, it should be fairly obvious that that is the root cause. Given that the error message specifies the counts don't match, it should be fairly obvious that you need to count the columns and the values you have specified. — jmcilhinney, Jun 14 '19 at 07:36
This is what comes from writing barely readable code. All that string concatenation makes it all but impossible to see what's going on. There are numerous ways that could be better written, even if you stuck with string concatenation but you shouldn't do that anyway. Learn how to use parameters in your ADO.NET code and more readable code will be just one of several benefits. — jmcilhinney, Jun 14 '19 at 07:38

score 0 · Answer 1 · answered Jun 14 '19 at 15:46

As @jmcilhinney commented, change your insert query to use parameters as it will be easier to read and to ensure that the number of values matches the number of columns.

Here's some 'template' code for you to expand on:

Dim InsertCmdText As String =
    "Insert into tbl_clients(clients_record_num,clients_client_id,clients_name,clients_contact_number,clients_address,clients_industry,clients_status,clients_delegate,clients_notes) " &
    "values (@clients_record_num,@clients_client_id,@clients_name,@clients_contact_number,@clients_address,@clients_industry,@clients_status,@clients_delegate,@clients_notes)"
Dim SqlConnectionString As String = "Your Connection Info"
Using cmd As New SqlCommand(InsertCmdText, New SqlConnection(SqlConnectionString))
    Try
        cmd.Parameters.AddWithValue("@clients_record_num", "Value Needed")
        cmd.Parameters.AddWithValue("@clients_client_id", "Value Needed")
        cmd.Parameters.AddWithValue("@clients_name", "Value Needed")
        'repeat above for all params

        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
    Catch ex As Exception
        'Handle your exception
    Finally
        cmd.Connection.Close()
    End Try
End Using

Alternatively use Parameters.Add instead of AddWithValue. For discussion on merits of each, see SqlCommand Parameters Add vs. AddWithValue

Only `Add(String, Object) is deprecated. .Add(String, SqlDBType, Size).Value = `is the preferred mehtod. See http://www.dbdelta.com/addwithvalue-is-evil/ and https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ and another one: https://dba.stackexchange.com/questions/195937/addwithvalue-performance-and-plan-cache-implications — Mary, Jun 14 '19 at 16:53

Column count does't not match value count at row 1

1 Answers1