disappearing data.

[teedoff]

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

[teedoff]

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

[arrgh]

Well data can't just disappear ;) So either

A) You're looking at a cached page instead of the latest data OR
B) The value never being updated at all OR
C) something is overwriting it

When you have a problem like this the first thing to do is confirm the data is actually being updated. Enable debugging in the CF Admin. Temporarily remove the <cflocation url="bulbView.cfm">. Then test your UPDATE and look at the generated sql. What's the UPDATED value for that field?

On a side note, do yourself a favor and don't use Dreamweaver generated code. Honestly it's poor code and is hard to read and debug.

[teedoff]

Hi aargh, thanks for the reply. I removed the <cflocation url> tag and then tried to update the same record again. The update.cfm template reloaded with bulbID 1 records loaded in the form. I didnt see any generated sql. Again, I would like to point out that I can update any other data for this record EXCEPT the form.bulbDesc field. I feel like its something to do with data types in cf conflicting with datatypes in MySQL, but maybe I'm wrong. If i use a <cfdump>, where would I put it in the updatebulb.cfm template? When I put it at the bottom of the page after the form, it gives me an invalid CFML construct error.

[teedoff]

Hi aargh, thanks for the reply. I removed the <cflocation url> tag and then tried to update the same record again. The update.cfm template reloaded with bulbID 1 records loaded in the form. I didnt see any generated sql. Again, I would like to point out that I can update any other data for this record EXCEPT the form.bulbDesc field. I feel like its something to do with data types in cf conflicting with datatypes in MySQL, but maybe I'm wrong. If i use a <cfdump>, where would I put it in the updatebulb.cfm template? When I put it at the bottom of the page after the form, it gives me an invalid CFML construct error.

oops sorry, I left out the #...ok now when I type something in the form.bulbDesc....it shows in the <cfdump> but when I go back and view the table in my bulbView template...it's not there. lol

[arrgh]

Did you see my comments about enabling debugging and looking at the generated sql? There's no point guessing what's going on when you can see what SQL CF is generating and sending to your db ;) So enable debugging and look at the sql statements at the bottom of the page.

What value is used for that field?

[teedoff]

Hi aargh yes I saw that...not sure what you mean exactly. I have CF debugging enabled. Is ther a different debugger for MySQL??

[arrgh]

Sorry I didn't see your "I didnt see any generated sql" comment. My bad. But as I mentioned, you have to enable debugging in the CF Administrator. OR try adding the result attribute to the cfquery. I think it will include the sql too...

<cfquery name="theResult" ...>
INSERT INTO ... blah blah
</cfquery>

<cfdump var="#theResult#">

[arrgh]

I have CF debugging enabled. Is ther a different debugger for MySQL??

No, it should show at the bottom of the page by default unless you've a) disabled debugging on that page b) have restricted the output by IP OR c) you're redirecting the debug output it elsewhere. Try the "result" attribute. Any difference?

[teedoff]

Sorry I didn't see your "I didnt see any generated sql" comment. My bad. But as I mentioned, you have to enable debugging in the CF Administrator. OR try adding the result attribute to the cfquery. I think it will include the sql too...

<cfquery name="theResult" ...>
INSERT INTO ... blah blah
</cfquery>

<cfdump var="#theResult#">

Aargh, thanks again. Yes I do have robust debugging enabled in CF admin. It showed the values the forms were inserting. I found the problem though! I feel stupid now...lol I had misspelled the form name...bulbDescs..instead on bulbDesc. I guess it was holding a value in the form, but wasnt going to my form. Sorry to be so much trouble! Thanks again. Sometimes a person can look at something so long, then someone else can come along and see or say the right thing, then its plain as day. Thanks again! I do have another issue, but I'll post a new topic.

[arrgh]

No problem. Happens to all of us. Though it's a good reason to get rid of that DW code. If it weren't for all those IsDefined() statements, it would've thrown the right error ;) ie Form field not defined.

[teedoff]

No problem. Happens to all of us. Though it's a good reason to get rid of that DW code. If it weren't for all those IsDefined() statements, it would've thrown the right error ;) ie Form field not defined.

Hi arrgh. I knew I could do an insert query without all those <cfif>'s, so I always remove them or code it myself. I didnt know you could remove the cfif's in an update query. Not sure the correct syntax for the either. Could you give me some guidance for this? Thanks

[arrgh]

You can use most any tag you want within a query. The only thing that's important is the variables used are valid and the end result is valid sql.

, oldPage=<cfif IsDefined("FORM.oldPage") AND #FORM.oldPage# NEQ "">
<cfqueryparam value="#FORM.oldPage#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
[/quote]

Most of the generated DW code is garbage and the logic is pretty pointless. Fields like text boxes always exist. So the IsDefined("FORM.textBoxName") statements are useless for those types of form fields because they're always defined. You only need isDefined() is for fields that may not always exist. Like checkboxes, radio buttons or multiple select lists. But you can also use <cfparam ..> to define default values. That keeps the code cleaner IMO.

<cfif #FORM.oldPage# NEQ "">
<cfqueryparam value="#FORM.oldPage#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>

Now even if you eliminate the unnecessary IsDefined()'s, what you're left with still doesn't make much sense. It's equivalent to saying if the value is not an empty string, use the value. Otherwise, still use the value. See what I mean? Just say it directly, without all the extra pound # signs, and using the correct data type. There's no reason to use CLOB for varchar fields.

, oldPage= <cfqueryparam value="#FORM.oldPage#" cfsqltype="cf_sql_varchar" maxlength="255">

The one place CFIF does make sense is if you need to insert NULL for some reason. Alternatively, you could use the "null" attribute instead

, brandID= <cfqueryparam value="#FORM.brandID#" cfsqltype="cf_sql_numeric"
      null="#not isNumeric(FORM.brandID)#">

[samaru]

Ah, I read all that for nothing! J/k.

As an aside, if you're dealing with extremely large strings, you may have to configure large character objects in your datasource in the cfadmin.

[arrgh]

Always jump to the end 1st ;)