CF MYSQL insert query error

Check if field_186 can be null, cause you're trying to insert an empty value into it.

HTH,
Dan

Are mlsnum and address allowed to be null ?

It seems it has no data to insert for those two columns into that row.

Just tested and this is illegal:
INSERT INTO tbloffmarket (field_sysid,NumImages,MLSNum,address) VALUES (260818963,,'','');

You need a value for all the fields when using INSERT INTO VALUES. If it's null you have to specifically specify it. So the correct syntax would be

INSERT INTO tbloffmarket (field_sysid,NumImages,MLSNum,address) VALUES (260818963,NULL,'','');

Select allOpen in new window

Quote the numeric value and the query will work even when empty:

INSERT INTO
tbloffmarket
(field_sysid,NumImages,MLSNum,address)
VALUES
(#field_sysid#,'#field_186#','#field_248#','#field_14#');

Select allOpen in new window

@myselfrandhawa: MySQL will happily accept quoted values in numeric fields. It will do an automatic conversion.

'' will get converted to 0.

@izweig - The problem is exactly what Dan explained here.  While you could get around the issue by enclosing all values in quotes, IMO a more robust solution is to switch to using cfqueryparam. For several reasons:

1) Your original query is vulnerable to sql injection. Cfqueryparam helps protect against that. It should always be used on ALL variable query parameters.

2) When executing queries multiple times, it helps boost performance by using bind variables.

Something along the lines of the query below. I don't know the data types of your columns, so I guessed: integer and varchar. Adjust the cfsqltype's if needed.

INSERT INTO tbloffmarket ( field_sysid, NumImages, MLSNum, address )
VALUES (
   <!--- VAL() converts non-numeric values to zero --->
   <cfqueryparam value="#VAL(field_sysid)#" cfsqltype="cf_sql_integer">
  , <cfqueryparam value="#VAL(field_186)#" cfsqltype="cf_sql_integer">
   , <cfqueryparam value="#field_248#" cfsqltype="cf_sql_varchar">
  ,  <cfqueryparam value="#field_14#" cfsqltype="cf_sql_varchar">
)

Select allOpen in new window

EDIT Note, the exact syntax depends on what you want to happen when one of the numeric values is empty. The example above inserts "0", but you could easily insert NULL instead, using the tag's "null" attribute, ie


   <cfqueryparam value="#field_sysid#"
                   cfsqltype="cf_sql_integer"
                   null="#NOT IsNumeric(field_sysid)#" >

Thanks Dan for clarification

Thanks all.
Making the query conditional was the solution.
However all other solutions seemed like they should have worked but were still causing errors.
Many thanks!

However all other solutions seemed like they should have worked but were still causing errors.

Then there's almost certainly an error in your code, or possibly my (untested) example, because cfqueryparam definitely works :)

Respectfully ... I'd strongly recommend not using that query because it exposes your db to sql injection! If still want help with switching to cfqueryparam, just post the data types of your 4 columns and I'd be happy to post a working example.

Thanks..
Data types: first 2 are int, second 2 are varchar.

I think there was just an error in your code. I copied and pasted my 1st example, and it worked fine:

Test Table:

create table tbloffmarket
(field_sysid INT
,NumImages INT
,MLSNum varchar(255)
, address varchar(255)
)

Select allOpen in new window

Code:

<cfset yourDSN = "Name of your DSN">
<!--- for testing only --->
<cfset field_sysid ="260818963">
<cfset field_186 ="">
<cfset field_248 = "">
<cfset field_14= "">

<cfquery datasource="#yourDSN#">
INSERT INTO tbloffmarket ( field_sysid, NumImages, MLSNum, address )
VALUES (
   <!--- VAL() converts non-numeric values to zero --->
   <cfqueryparam value="#VAL(field_sysid)#" cfsqltype="cf_sql_integer">
  , <cfqueryparam value="#VAL(field_186)#" cfsqltype="cf_sql_integer">
   , <cfqueryparam value="#field_248#" cfsqltype="cf_sql_varchar">
  ,  <cfqueryparam value="#field_14#" cfsqltype="cf_sql_varchar">
)
</cfquery>

Select allOpen in new window

If you prefer to insert NULL, instead of 0, when field_186 is empty, then use the 2nd example.

<cfquery datasource="#yourDSN#">
INSERT INTO tbloffmarket ( field_sysid, NumImages, MLSNum, address )
VALUES (
   <!--- VAL() converts non-numeric values to zero --->
   <cfqueryparam value="#VAL(field_sysid)#" cfsqltype="cf_sql_integer">
  , <cfqueryparam value="#field_186#" cfsqltype="cf_sql_integer" null="#NOT IsNumeric(field_186)#">
   , <cfqueryparam value="#field_248#" cfsqltype="cf_sql_varchar">
  ,  <cfqueryparam value="#field_14#" cfsqltype="cf_sql_varchar">
)
</cfquery>

Select allOpen in new window

Thanks, much appreciated.

Is all working fine, except now...
(separate issue)
After my successful insert of the data into  tbloffmarket
I now am trying to delete the data that was inserted from the table (property11) where the data is from.

I am getting an error which references a column name which does not exist in the table.

Error
SQL query:

Delete FROM `property11` WHERE `Class`="11 off market"
MySQL said: Documentation

#1048 - Column 'mls' cannot be null


There is no column named 'mls' in property11

Do you know what this is?

Check if you have a FK to another table using the mls column.

Your are right! Thanks so much.

INSERT INTO tbloffmarket (field_sysid,NumImages,MLSNum,address) VALUES (260818963,,'','');
no value for NumImages, you transfer null to field_186 at the 26th times.

Hi, You can even use cfqueryparam too for this:

INSERT INTO
tbloffmarket
(field_sysid<cfif isDefined('field_186') and field_186 neq ''>,NumImages</cfif>,
MLSNum,address)
VALUES
(<cfqueryparam value="#VAL(field_sysid)#" cfsqltype="cf_sql_integer">
<cfif isDefined('field_186') and field_186 neq ''>,
<cfqueryparam value="#field_186#" cfsqltype="cf_sql_integer">
</cfif><cfqueryparam value="#field_248#" cfsqltype="cf_sql_varchar">,<cfqueryparam value="#field_14#" cfsqltype="cf_sql_varchar">);

Select allOpen in new window

INSERT INTO
tbloffmarket
(field_sysid<cfif isDefined('field_186') and field_186 neq ''>,NumImages</cfif>,
MLSNum,address)
VALUES
(<cfqueryparam value="#VAL(field_sysid)#" cfsqltype="cf_sql_integer">
<cfif isDefined('field_186') and field_186 neq ''>,
<cfqueryparam value="#field_186#" cfsqltype="cf_sql_integer">
</cfif><cfqueryparam value="#field_248#" cfsqltype="cf_sql_varchar">,<cfqueryparam value="#field_14#" cfsqltype="cf_sql_varchar">);

CFIF's are kind of bulky and less readable. Simpler to just use the "null" attribute. Then there's no messing around with column list.  Also, if values must be numeric then IsNumeric() is a more robust than just checking for an empty string.

VALUES (
    <cfqueryparam value="#field_186#"
            cfsqltype="cf_sql_integer"
            null="#NOT IsNumeric(field_186)#">
      , ....
)

I agree with your point, but sometime in cases ifdefined works good, if isdefined is bulky and heavy as it is, we can always switch to structkeyexists

Yeah, sometimes you do need structKeyExists, but this isn't one of those times ;-). In this specific case, using a cfif in both the column list and values() clause creates extra clutter, decreasing readability IMO. That's why I suggest using the simpler "null" attribute.