Solved

How can I update column "ParentID" with value "NULL"?

Posted on 2011-03-23
27
563 Views
Last Modified: 2012-05-11
ColdFusion 9
SQL Server 2005

In brief: How can I update column "ParentID" with value "NULL"?

In detail:

* I have a navigation menu here: http://www.coalcountryteam.org/

* The navigation menu has seven items, as you can see: Home, About Us, Media, etc.

* All of these seven "main navigation" menu items have ParentID = 0.

* Any database record that has ParentID = 0 gets included in this main navigation menu.

* I have built an update page that updates database records, and allows the user to set a ParentID and SortOrder for a record. It works well, except:

* The update page will not allow me to set ParentID = NULL.

* Some web pages should not appear in the navigation menu; thus, they are not assigned a ParentID or a SortOrder; the value is NULL for these columns when a page should not appear in the navigation menu.

When I process the edit form, the OPTION VALUE = NULL gets updated as value "0" rather than NULL in the database. See the code, below. How can I get this SELECT form to update column ParentID with value NULL?

And, I bet there is a better way to do all of this. =) I welcome advice. Please let me know if I need to supply more information. Thank you for any advice.

Eric

<!--- use this select menu to display and assign Parent Page Titles --->
<!--- user should see a human-readable list of parent page titles --->
 <p>
     <cfselect size="8" name="ParentID" value="PageID" display="PageTitle" multiple="no" query="GetParents" queryPosition="below" selected="#selectedParents#" id="ParentID">
                <option value="NULL"> <strong>Select Parent Page</strong> </option>
                </cfselect>
<!--- when form is processed, the correct ParentID is populated into tbl_acct_navigation --->
<!--- ParentID value NULL means the record / web page is not included in navigation menu --->

Open in new window

0
Comment
Question by:Eric Bourland
  • 13
  • 10
  • 3
  • +1
27 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 35204213
It's probably your UPDATE query that's converting the "NULL" to 0.  I'd guess you're using the val() function, which will convert any non-numeric string (such as the text "NULL") into a 0.

> <option value="NULL">

That might get confusing.  You could assign a negative value like "-1" to represent nulls.  

...
    <option value="-1"> <strong>Select Parent Page</strong> </option>

Open in new window


Then use cfqueryparam's "null" attribute to insert NULL whenever #form.parentID# equals -1.  


UPDATE YourTable
SET        ParentID = <cfqueryparam value="#form.parentID#" 
                                      cfsqltype="cf_sql_integer"
                                      null="#form.parentID eq -1#" />
WHERE   ......

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 35204430
Good idea. I'm working on this.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 35204806
_agx_,

It is good to hear from you.

I am making progress.

In effect, setting <option value="-1"> means a value of NULL is entered in column ParentID, and the page is removed from navigation. That works perfectly and is exactly what I wanted.

My new problem: my SELECT menu needs to assign other ParentID values, including:
<option value="0"> (add page to top level navigation)
<option value="1"> (add page as a menu item under the Home menu)
... etc.

When I select any menu item other than <option value="-1">, I get this very familiar error:

Invalid data '' for CFSQLTYPE CF_SQL_INTEGER. 
 The error occurred in C:/websites/www.coalcountryteam.org/admin/editPages_test.cfm: line 71
Called from C:/websites/www.coalcountryteam.org/admin/editPages_test.cfm: line 60
Called from C:/websites/www.coalcountryteam.org/admin/editPages_test.cfm: line 55
Called from C:/websites/www.coalcountryteam.org/admin/editPages_test.cfm: line 44
Called from C:/websites/www.coalcountryteam.org/admin/editPages_test.cfm: line 1

69 :            ParentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.parentID#" null="#form.parentID eq -1#">,
70 :            SortOrder = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.SortOrder#" null="#form.parentID eq -1#">
71 : 			WHERE PageID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
72 : 			</cfquery>

Open in new window


ColdFusion thinks the value it receives for ParentID is not an integer. Previously, I used the val() function to solve this problem:

UPDATE tbl_acct_navigation
SET
ParentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(parentID)#">,

But now I have:

UPDATE tbl_acct_navigation
SET
ParentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.parentID#" null="#form.parentID eq -1#">,

So I get the Invalid data error.

Is there a workaround? Or do I really need to rethink this application? (I hope not; the application is in production.) =) Thanks again for your help. Hope you are well.

Eric
Query:

   <cfquery name="UpdatePage" datasource="#ds#">
				  UPDATE tbl_acct_navigation
				  SET
           PageTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(form.PageTitle)#">,   
           PageContentLeft = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(form.PageContentLeft)#">,
           PageContentRight = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(form.PageContentRight)#">,
           DateModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">,
           ParentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.parentID#" null="#form.parentID eq -1#">,
           SortOrder = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.SortOrder#" null="#form.parentID eq -1#">
			WHERE PageID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
			</cfquery>


Form:


<!--- use this select menu to display and assign Parent Page Titles --->
<!--- user should see a human-readable list of parent page titles --->
     <cfselect size="10" name="ParentID" value="PageID" display="PageTitle" multiple="no" query="GetParents" queryPosition="below" selected="#selectedParents#" id="ParentID">
			    <option value="0"> 1. Add this page to top-level navigation </option>
                <option value="-1"> 2. Remove this page from navigation </option>
                <option value="-1"> 3. Position this page beneath a Parent Page, below: </option>
     </cfselect>                

<!--- when form is processed, the correct ParentID is populated into tbl_acct_navigation --->
<!--- ParentID value NULL means the record / web page is not included in navigation menu --->

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 35204873
Sorry for the delay. Got caught up in work :)

ColdFusion thinks the value it receives for ParentID is not an integer.

Hm... that doesn't make much sense since all of three of those values (-1, 0,1) are integers.

Dump the FORM scope before the UPDATE query.  So you can see what values are passed. What are the results.

0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 500 total points
ID: 35204893
Previously, I used the val() function to solve this problem:

I'm pretty sure you can still do that.  

<cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.parentID)#"
          null="#form.parentID eq -1#">   <!--- don't change the "null" comparison --->

But I'd figure out the cause of the error first..
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35205404
- what database are you using? just a thought, i think you can simply update the field to null this way. you might want to restructure or tune it a bit the code:

UPDATE YourTable

<cfif form.parentID eq "-1"'>
     SET        ParentID = NULL
<cflese>
     SET        ParentID = <cfqueryparam value="#form.parentID#"
                                      cfsqltype="cf_sql_integer" />
</cfif>

WHERE   ......
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35205419
- i have extra ' there on this line : <cfif form.parentID eq "-1">
- so kindly remove it...
0
 
LVL 15

Expert Comment

by:myselfrandhawa
ID: 35206073
where you are using

<cfqueryparam cfsqltype="cf_sql_integer" value="#form.parentID#" null="#form.parentID eq -1#">,

can you try using this

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.parentID#" null="#form.parentID eq -1#">,

Hope it might helps
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 35206839
OP_Zarahin, the database server is SQL Server 2005. I was thinking about a CFIF setup much like you suggest.

myselfrandhawa: I thought about changing the CFSQLTYPE but I think it needs to be an integer.

_agx_: I agree, the cause of the error, Invalid data '' for CFSQLTYPE CF_SQL_INTEGER, is troubling. I was patching this error using the val() function. What are some steps I can take to troubleshoot this problem of invalid data?

Thank you again.

Eric
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35207043
hi eric,
- if its sql server then SET ParentID = NULL will definitely work.
- give it a try. do not worry about sql injection on that portion because you are not setting it using any variable.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 35207436
OP_Zaharin,

Thanks for your note. The val() function definitely works, per _agx_'s note (03/24/11 02:37 AM, ID: 35204893) that there is no reason I could not still use the val() function. I applied the val() function again and the application now works as expected. It si working very well.

But as _agx_ also pointed out, I should be concerned about the reason that the invalid data error occurs in the first place.

I'll probably open that problem in a new question -- it is outside the scope of this question. I have gotten this invalid data error in other applications too.

I'm going to close this question in a little while. Thank you again for your ideas.

Best from Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 35207874
>>>Dump the FORM scope before the UPDATE query.  So you can see what values are passed. What are the results.

I just saw this from _agx_. I did so, in the code you see below. The output I get is just the name of the query, UpdatePage:

UpdatePage
The web site you are accessing has experienced an unexpected error.
 Please contact the website administrator.
The following information is meant for the website developer for debugging purposes.
Error Occurred While Processing Request
Invalid data '' for CFSQLTYPE CF_SQL_INTEGER.

Can I format my CFDUMP better, or place it in a better place? Thank you!

Eric
<!---Dump the FORM scope before the UPDATE query.  So you can see what values are passed. --->

<cfdump var="UpdatePage" expand="yes">
                
            <cfquery name="UpdatePage" datasource="#ds#">
				  UPDATE tbl_acct_navigation
				  SET
           PageTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(form.PageTitle)#">,   
           PageContentLeft = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(form.PageContentLeft)#">,
           PageContentRight = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(form.PageContentRight)#">,
           DateModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">,
           ParentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.parentID#" null="#form.parentID eq -1#">,
           SortOrder = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.SortOrder#" null="#form.parentID eq -1#">
			WHERE PageID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
			</cfquery>

Open in new window

0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 500 total points
ID: 35208428
> <cfdump var="UpdatePage" expand="yes">

That should be the #FORM# scope. We want to see the values used in the query so we can figure out which one is causing the "not an integer .." error.

ie <cfdump var="#FORM#" expand="yes">

I thought about changing the CFSQLTYPE but I think it needs to be an integer

Yeah, don't change the type. If the db column is an integer, use cf_sql_integer not varchar.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 3

Author Comment

by:Eric Bourland
ID: 35208474
<cfdump var="#FORM#" expand="yes">

Of course. Sorry. I am working too fast.

OK, I've got more information now. The form is submitting an Empty String instead of an integer.

 empty string
So I need to update my query to account for the empty string; or, should I edit my form in some way?

I am trying a couple of ideas. Thank you again. Eric
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35208585
IF it's a single <select> list (not multiple) the value shouldn't be an empty string.  Are any of your select list <option>'s an empty string OR are you using <cfparam> possibly?
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 35208774
I was thinking those exact questions too.

All select options have an integer value; there are no empty strings in the options. I can view source and see all of the options -- they are integers.

The Select multiple attribute is set to No.

Wait, I see what is going on. It's the SortOrder field. If I submit the form without entering a value in the SortOrder field, then an empty string gets submitted.

I will set a default value for the SortOrder field.

I'll try this and let you know if the problem resolves. Be right back.

Eric
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35208882
Sounds good.

I was thinking about a CFIF setup much like you suggest

You don't need it.  That's what null="#form.parentID eq -1#" does already :) Just with less code.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 35208939
Hmmm. When I process the form, but do not select anything from the Select menu, and do not enter a value in the SortOrder field, I get the Empty String error.

I set these parameters at the beginning of the document:

<cfparam name="form.ParentID" default="">
<cfparam name="form.SortOrder" default="0">

It seems the val() function really is required in the update query.

I attach my query; the form HTML; and the View Source that shows the Select Menu.

Can you think of any way to process this form without using the val() function in the update query?

(I'm comforted in knowing, at least, the cause of the Invalid data error.

I see in my email there is a new comment, which I will read. Eric
update query:
     <cfquery name="UpdatePage" datasource="#ds#">
				  UPDATE tbl_acct_navigation
				  SET
           PageTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(form.PageTitle)#">,   
           PageContentLeft = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(form.PageContentLeft)#">,
           PageContentRight = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(form.PageContentRight)#">,
           DateModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">,
           ParentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.parentID#" null="#form.parentID eq -1#">,
           SortOrder = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.SortOrder#">
			WHERE PageID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
			</cfquery>

Form:

<!--- use this select menu to display and assign parent page Titles --->
<!--- user should see a human-readable list of parent page titles --->
     <cfselect size="10" name="ParentID" value="PageID" display="PageTitle" multiple="no" query="GetParents" queryPosition="below" selected="#selectedParents#" id="ParentID">
			    <option value="0"> 1. Add this page to top-level navigation </option>
                <option value="-1"> 2. Remove this page from navigation </option>
     </cfselect>                
<!--- when form is processed, the correct ParentID is populated into tbl_acct_navigation --->
<!--- ParentID value NULL means the record / web page is not included in navigation menu --->




Output from View Source:

<select name="ParentID" id="ParentID"  size="10" >

			    <option value="0"> 1. Add this page to top-level navigation </option>
                <option value="-1"> 2. Remove this page from navigation </option>
         <option value="1">Home</option>
    <option value="2">Media</option>
    <option value="3">Contact Us</option>
    <option value="4">About Us</option>
    <option value="5">Core Goals</option>
    <option value="6">Our Sites</option>
    <option value="7">Our Research Initiatives</option>
</select>

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 35208946
>>>You don't need it.  That's what null="#form.parentID eq -1#" does already :) Just with less code.

I agree. I am following your suggestions.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35209103
<cfselect size="10"

Oh.. I see what's happening. It's *not* a single size select list.  So if nothing is selected, your cfparam will set the value to an empty string.

       <cfparam name="form.ParentID" default="">

If needed make the form field required AND set the default to a numeric value instead of a numeric string.
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 500 total points
ID: 35209127
You can still use val() if you want. But since the ParentID db column is numeric, it doesn't really make sense to use "" for the default.  It should be a number.  
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 35209315
>>>it doesn't really make sense to use "" for the default.  It should be a number.

Agreed.

If I set the default value to "0", then the page becomes part of main navigation.

If I set default value to any value 1 - 7, then the page becomes a dropdown menu item.

If I set the default value to "-1" then the page will be removed from the navigation menu -- unless of course a specific selection is made in the select menu. I think that is what I will do.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 35209571
_agx_,

Making progress. =) I set these parameters:

<cfparam name="form.ParentID" default="-1">
<cfparam name="form.SortOrder" default="0">

That solved the problem of the empty string in the ParentID.

I still get the Empty String error if I do not enter a value in the SortOrder field. I think I see why:

 <cfinput type="text" name="SortOrder" value="#getPageDetails.SortOrder#" size="1" maxlength="2" />

... the default value of the SortOrder field is derived from the output of query getPageDetails.

So I set this parameter: <cfparam name="getPageDetails.SortOrder" default="0">

But still, if I do not enter a value in the SortOrder field, I get the Empty String error.

Curious to know why that is happening.

But I think I might use the val() function in the update query to update column SortOrder, because what if someone enters a nonnumeric character -- I would want that nonnumeric character to convert to "0".

What is your opinion? Thank you again. Hope your day is going well.

Eric
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35209863
For text fields, I would definitely use val(). Because the values aren't restricted like a select list. Liek you said, the value entered could be anything ;-)

But still, if I do not enter a value in the SortOrder field, I get the Empty String error.

cfparam does nothing when it comes to text fields ;-) cfparam only applies if the form field does not exist.  That's different than being empty.  Text fields always exist.  If nothing was entered the value is an empty string. But the field still exists and has a value.   A good rule is if you can see the form field when you dump #FORM# the field exists.
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 500 total points
ID: 35209883
> Liek you said

Good grief...  what on earth is a "Liek"? ;-)  

(I'm doing pretty well. Hope you are too.  But back to the grind ...)
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 35210167
>>>cfparam only applies if the form field does not exist.

Got it. I'm going to use val().

This application is working very well now and, as usual, I have learned a lot from you.
0
 
LVL 3

Author Closing Comment

by:Eric Bourland
ID: 35210305
Thank you very much to _agx_. OP_Zaharin -- thank you for your ideas too; they made sense to me. I really appreciate your input. myselfrandhawa -- thank you too; hope you are great.

Eric B
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now