asked on

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

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

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eric Bourland

ASKER

Good idea. I'm working on this.

Eric Bourland

ASKER

_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

_agx_

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.

SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

OP_Zaharin

- 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 ......

OP_Zaharin

- i have extra ' there on this line : <cfif form.parentID eq "-1">
- so kindly remove it...

Coast Line

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

Eric Bourland

ASKER

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

OP_Zaharin

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.

Eric Bourland

ASKER

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

Eric Bourland

ASKER

>>>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

SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eric Bourland

ASKER

<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.

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

_agx_

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?

Eric Bourland

ASKER

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

_agx_

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.

Eric Bourland

ASKER

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

Eric Bourland

ASKER

>>>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.

_agx_

<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.

SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eric Bourland

ASKER

>>>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.

Eric Bourland

ASKER

_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

_agx_

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.

SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eric Bourland

ASKER

>>>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.

Eric Bourland

ASKER

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

How can I update column &quot;ParentID&quot; with value &quot;NULL&quot;?

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