Insert query error: Column count doesn't match value count at row 1

Question

I have a cfquery that I'm looping over the values to insert multiple rows into the database. I have to do it this way, instead of generating multiple INSERT queries within a loop, because I need a list of the generatedKeys after the INSERT. However my query is now throwing the error below. I know what the error means, but it doesn't make sense based off my query.

Query:

<cfquery datasource="#OLMSdatasourceWrite#" result="myResult">
    INSERT INTO OLMS_Data_RatioScenarios
    (
        OLMS_Account_ID,
        OLMS_RatioScenario_Name
    )
    VALUES
    (
    <cfset numItems = ListLen(AccountListWithSettings)>
    <cfset i = 1>

    <cfloop list="#AccountListWithSettings#" index="CurrentAccount">
        (<cfqueryparam cfsqltype="cf_sql_numeric" value="#CurrentAccount#" maxlength="255">
        , <cfqueryparam cfsqltype="cf_sql_clob" value="#requestBody.value#" maxlength="255">
        )
        <cfif i lt numItems>
            ,
        </cfif>

        <cfset i++>
    </cfloop>
    )
</cfquery>

<cfoutput>Inserted ID is: #myResult.generatedkey#</cfoutput>

Error:

Root Cause:java.sql.SQLException: Column count doesn't match value count at row 1
SQL: INSERT INTO OLMS_Data_RatioScenarios ( OLMS_Account_ID, OLMS_RatioScenario_Name ) 
VALUES ( ( (param 1) , (param 2) ) , ( (param 3) , (param 4) ) , ( (param 5) , (param 6) ) )

Take a look at your generated VALUES clause. It is not valid sql. You have got too many parenthesis. When generating SQL dynamically, I often output the generated string first, `INSERT INTO .... `, to spot that kind of syntax error more easily. Plus, I can copy and paste the sql into an IDE if needed. — Leigh, Feb 06 '17 at 20:35
Copy the sql part of the error message to a text editor and format it so that you can see the problem with parentheses. — Dan Bracuk, Feb 06 '17 at 20:39
Take a look at the [MySQL docs](https://dev.mysql.com/doc/refman/5.5/en/insert.html). A valid VALUES clause looks like this: `VALUES(1,2),(4,5),(7,8);`. Ignoring the parameters, ie "(param X)", notice yours has extra parenthesis? — Leigh, Feb 06 '17 at 20:41

score 0 · Accepted Answer · edited Feb 06 '17 at 20:57

Corrected Code:

<cfquery datasource="#OLMSdatasourceWrite#" result="myResult">
    INSERT INTO OLMS_Data_RatioScenarios
    (
        OLMS_Account_ID,
        OLMS_RatioScenario_Name
    )
    VALUES
    <cfset numItems = ListLen(AccountListWithSettings)>
    <cfset i = 1>
    <cfloop list="#AccountListWithSettings#" index="CurrentAccount">
        (
          <cfqueryparam cfsqltype="cf_sql_numeric" value="#CurrentAccount#" maxlength="255">
         , <cfqueryparam cfsqltype="cf_sql_clob" value="#requestBody.value#" maxlength="255">
        )
        <cfif i lt numItems>
            ,
        </cfif>

        <cfset i++>
    </cfloop>
</cfquery>

Insert query error: Column count doesn't match value count at row 1

1 Answers1