当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - fixing "more columns in insert than values specified"

问题描述:

A user got this error

DIAGNOSTICS: Error Executing Database Query. [Macromedia][SQLServer

JDBC Driver][SQLServer]There are more columns in the INSERT statement

than values specified in the VALUES clause. The number of values in

the VALUES clause must match the number of columns specified in the

INSERT statement. The error occurred on line 252. MESSAGE: Error

Executing Database Query. ROOT CAUSE:

coldfusion.tagext.sql.QueryTag$DatabaseQueryException: Error Executing

Database Query. Server: server2

There should be around 60 values/columns. Possible solution is adding <CFIF isDefined("VARIABLES.xxx")> to each of the values in INSERT and VALUES but this would take a long time. Is there a better way? Here is some of the code

 <CFQUERY name="addAgency" datasource="#REQUEST.dsn#">

INSERT INTO agency (agency_name, code, address1, address2, city_id, postal, phone, alternativePhone, fax, provincialRegistration,

<!--- contact, ---> firstname, lastname, username, password, relationship_id, editdate, adddate, email, URL_link,Airport_id,

header, teaser, full_desc, <CFIF isDefined("VARIABLES.specialty")>specialty,</CFIF> hours_1, hours_2, hours_3, hours_4,

hours_5, hours_6, hours_7, merchant_ID,

region_ID, fcApproved, agencyType_ID, agencystatus, crossview, insp_Approved, rbc_Approved,

branch_number, gp_entity_ID,

<CFIF VARIABLES.alias1 gt 100> alias1,</CFIF>

<CFIF VARIABLES.alias2 gt 100> alias2,</CFIF>

<CFIF Trim(VARIABLES.alias3) NEQ ""> alias3,</CFIF>

default_agent_id,LOCAL_PASSWORD,LOCAL_USERNAME,NATIONAL_PASSWORD,NATIONAL_USERNAME,VACATIONCLUB_PASSWORD,VACATIONCLUB_USERNAME,toll_free_number,display_toll_free ,MARKETINGREGIONID, searchPreference, bookingOption,customer_can_choose

<CFIF isDefined("variables.logo_filedata")>,logo_fileName,logo_fileData,logo_mimeType</CFIF>

,gds, pseudo_city, acv_affiliate_code, vip_ext,owner_manager_title_en,owner_manager_title_fr)

VALUES (<CFQUERYPARAM value="#Trim(sanitize(VARIABLES.agency_name))#" cfsqltype = "cf_sql_varchar" maxlength="150">,

<CFQUERYPARAM value="#Trim(sanitize(VARIABLES.code))#" cfsqltype = "cf_sql_varchar" maxlength="50">,

<CFQUERYPARAM value="#Trim(sanitize(VARIABLES.address1))#" cfsqltype = "cf_sql_varchar" maxlength="255">,

<CFQUERYPARAM value="#Trim(sanitize(VARIABLES.address2))#" cfsqltype = "cf_sql_varchar" maxlength="255">,

<CFQUERYPARAM value="#sanitize(VARIABLES.city_id,true,true,false,true,false)#" cfsqltype = "cf_sql_integer">,

<CFQUERYPARAM value="#Trim(sanitize(VARIABLES.postal))#" cfsqltype = "cf_sql_varchar" maxlength="10">,

<CFQUERYPARAM value="#Trim(sanitize(VARIABLES.phone))#" cfsqltype = "cf_sql_varchar" maxlength="20">,

<CFQUERYPARAM value="#Trim(sanitize(VARIABLES.fax))#" cfsqltype = "cf_sql_varchar" maxlength="20">,

<!--- <CFIF IsDefined("VARIABLES.contact")>#Trim(VARIABLES.contact)#, <CFELSE> ' ', </CFIF> --->

<CFQUERYPARAM value="#Trim(sanitize(VARIABLES.firstname))#" cfsqltype = "cf_sql_varchar" maxlength="40">,

<CFQUERYPARAM value="#Trim(sanitize(VARIABLES.lastname))#" cfsqltype = "cf_sql_varchar" maxlength="40">,

'-', '-',

<!--- Dummy un/pw --->

<CFQUERYPARAM value="#sanitize(VARIABLES.relationship_ID,true,true,false,true,false)#" cfsqltype = "cf_sql_integer">,

#CreateODBCDateTime(Now())#,

#CreateODBCDateTime(Now())#,

<CFQUERYPARAM value="#Trim(sanitize(VARIABLES.email))#" cfsqltype = "cf_sql_varchar" maxlength="40">,

网友答案:

Each of your s in the "columns" area MUST be matched with corollary CFIF in the "values" section as long as you are doing it this way. While there are other ways of doing it, they are not necessarily less messy. You could, for example:

  1. set defaults for all columns with some cfparams so you did not have to have any "cfifs"
  2. Write multiple "cleaner" queries
  3. Move the whole thing to a stored procedure and pass what you have into it - the SP would then worry about the logic.
  4. Save a small subset of values then "update" based on your cfif logic (don't like that one!).

As you can see they all have a downside.


Elaboration on 1)

Instead of doing something arduous like:

<CFIF isDefined("VARIABLES.specialty")> Specialty,</cfif>

Before you start your query make sure you have a value for all the variables you need using cfparam as in:

<Cfparam name="Specialty" default=""/>

(of course the default for specialty might be 0 or whatever).

The cfparam tag checks to see IF the variable exist and if it does not exist it creates it with the default you specify. This would mean you can "guarantee" that the variable will indeed exist - meaning you can eliminate the CFIF statement in your query.

One cavaet has to do with NULLs in the database. If you are depending on nulls (meaning if specialty does not exist you wish for the column to remain null), then you will need to add the "null" attribute to your cfqueryparam as in:

<CFQUERYPARAM 
  value="#Trim(sanitize(VARIABLES.specialty))#" 
  cfsqltype = "cf_sql_varchar" 
  null="#mynullfunction(variables.specialty)#"/>,

Note the "mynullfunction" - I ususally create a utility UDF for this that simply returns YES if the value is blank or zero (or whatever I define) and "no" if it's populated.

网友答案:

The best solution is to use the null attribute of cfqueryparam for columns where a default has not be set in the database, this means you can avoid adding conditions to the INSERT INTO and keeps the VALUE to one line per column. When a default does exist for a column then you need to use the long winded condition. This is the simplest, least obtrusive change.

Example where Alias1 has a database default, speciality does not.

`   <cfset Variables.IsValidAlias1 = Variables.alias1 gt 100> 
    <cfquery name="addAgency" datasource="#REQUEST.dsn#">
        INSERT INTO agency (agency_name, 
                            specialty
                            <cfif Variables.IsValidAlias1>
                            , Alias1
                            </cfif>)
        VALUES (<cfqueryparam value="#Variables.agency_name#" cfsqltype="cf_sql_varchar" maxlength="150">, 
                <cfqueryparam value="#Variables.specialty#" cfsqltype="cf_sql_varchar" null="#StructKeyExists(Variables,'Specialty') EQ false#" maxlength="199">
                <cfif Variables.IsValidAlias1>
                    ,<cfqueryparam value="#Variables.Alias1#" cfsqltype="cf_sql_varchar" maxlength="199">
                </cfif>)
     </cfquery>`

As an aside you should use StructKeyExists() instead of IsDefined as the former is faster and can be clearer to read. You should put this code inside a function, and that function inside a component so it can be re-used. This is assuming your using CF6 or later.

网友答案:

I would avoid setting empty string defaults for each field or setting values to NULL for non-existent keys. The reason is that you may now (or decide to have in the future) default values in your database columns. As the resulting NULL values would be explicit, they would override your database defaults. If you had passed in an empty string, this might be valid but as a default it likely is not.

My main advice would be to be keep each column on one line so that you easily see that you are being consistent in your conditionals. As Mark said, you must be careful to have the exact same conditionals in the "columns" area as the "values" area of your query.

Somewhat as an aside, I have a free tool called DataMgr that you could use to manage all of this sort of thing for most simple queries.

http://www.bryantwebconsulting.com/docs/datamgr/replace-sql-inserts-and-updates.cfm http://www.bryantwebconsulting.com/docs/datamgr/getting-started.cfm

The main thing, however, is to be consistent in your conditionals and to format your query in such a way as to make it obvious when you are not.

分享给朋友:
您可能感兴趣的文章:
随机阅读: