Disappearing data

Report · Aug 03, 2010

I have an Update query to update a MySQL database. Everything seems to work fine except one form field..."bulbDesc". When I enter data into this field, which is longText type in MySQL, it disappears. Meaning when I go back to my view page, the bulbDesc in that particular record is blank. like I never entered any data. Here is my Update query code.

<cfparam name="URL.id" default="1">
<cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>
<cfif IsDefined("FORM.MM_UpdateRecord") AND FORM.MM_UpdateRecord EQ "form1">
<cfquery datasource="rlbulbs">
UPDATE rlbbulbs
SET item=<cfif IsDefined("FORM.item") AND #FORM.item# NEQ "">
<cfqueryparam value="#FORM.item#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, brandID=<cfif IsDefined("FORM.brandID") AND #FORM.brandID# NEQ "">
<cfqueryparam value="#FORM.brandID#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
, bulbDesc=<cfif IsDefined("FORM.bulbDesc") AND #FORM.bulbDesc# NEQ "">
<cfqueryparam value="#FORM.bulbDesc#" cfsqltype="cf_sql_char" maxlength="2147483647">
<cfelse>
NULL
</cfif>
, cost=<cfif IsDefined("FORM.cost") AND #FORM.cost# NEQ "">
<cfqueryparam value="#FORM.cost#" cfsqltype="cf_sql_varchar">
<cfelse>
NULL
</cfif>
, price=<cfif IsDefined("FORM.price") AND #FORM.price# NEQ "">
<cfqueryparam value="#FORM.price#" cfsqltype="cf_sql_varchar">
<cfelse>
NULL
</cfif>
, qtyPerPrice=<cfif IsDefined("FORM.qtyPerPrice") AND #FORM.qtyPerPrice# NEQ "">
<cfqueryparam value="#FORM.qtyPerPrice#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
, qtyInStock=<cfif IsDefined("FORM.qtyInStock") AND #FORM.qtyInStock# NEQ "">
<cfqueryparam value="#FORM.qtyInStock#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
, wattage=<cfif IsDefined("FORM.wattage") AND #FORM.wattage# NEQ "">
<cfqueryparam value="#FORM.wattage#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, voltage=<cfif IsDefined("FORM.voltage") AND #FORM.voltage# NEQ "">
<cfqueryparam value="#FORM.voltage#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, acDC=<cfif IsDefined("FORM.acDC") AND #FORM.acDC# NEQ "">
<cfqueryparam value="#FORM.acDC#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, base=<cfif IsDefined("FORM.base") AND #FORM.base# NEQ "">
<cfqueryparam value="#FORM.base#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, glass=<cfif IsDefined("FORM.glass") AND #FORM.glass# NEQ "">
<cfqueryparam value="#FORM.glass#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, filament=<cfif IsDefined("FORM.filament") AND #FORM.filament# NEQ "">
<cfqueryparam value="#FORM.filament#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, avgLife=<cfif IsDefined("FORM.avgLife") AND #FORM.avgLife# NEQ "">
<cfqueryparam value="#FORM.avgLife#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, beamAngle=<cfif IsDefined("FORM.beamAngle") AND #FORM.beamAngle# NEQ "">
<cfqueryparam value="#FORM.beamAngle#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, oldPage=<cfif IsDefined("FORM.oldPage") AND #FORM.oldPage# NEQ "">
<cfqueryparam value="#FORM.oldPage#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, keyWords=<cfif IsDefined("FORM.keyWords") AND #FORM.keyWords# NEQ "">
<cfqueryparam value="#FORM.keyWords#" cfsqltype="cf_sql_clob" maxlength="2147483647">
<cfelse>
''
</cfif>
, image1=<cfif IsDefined("FORM.image1") AND #FORM.image1# NEQ "">
<cfqueryparam value="#FORM.image1#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
WHERE bulbID=<cfqueryparam value="#FORM.bulbID#" cfsqltype="cf_sql_numeric">
</cfquery>
<cflocation url="bulbView.cfm">
</cfif>
<cfquery name="rsBulbs" datasource="rlbulbs">
SELECT *
FROM rlbbulbs
WHERE bulbID=#URL.id#
</cfquery>

It seems I can change or update every field except the #FORM.bulbDesc# field. I'm sure it's something simple I'm overlooking! Any help would be greatly appreciated.

Tony

Report · Aug 03, 2010

Ok so I tried once more to add data in the bulbDesc field using my update query. Didn't work. Then I went directly into the table in MySQL and added the same data in the same field and then refreshed my bulbView.cfm template in my browser and it showed up. This to me would suggest something is definately wrong with my CF code. What I dont know. Thanks in advance for any help.

Tony

Report · Aug 03, 2010

You have confirmed that CLOB and|or BLOB data are enabled in the Data Source connection? These are not enabled IIRC by default and|or may have a limit on how much data can be passed through the connector.

Look under the "Advanced" button in the Data Source Name configuration screen.

Report · Aug 03, 2010

Hi ilssac, thanks for the suggestions. Yes I checked that out as well. I did find the problem though...lol I mispelled my form name,....bulbDescs instead of bulbDesc. Seems to be working great now! AAAgggghh what a head ache...lol Now my other post..."MySQL error I think?" is another story. I'm really frustrated by that problem. Anyway, Thanks again