[ACFUG Discuss] cfqueryparam and NULL attribute SQL Server 2005
Hey all, I'm working on an asset tracking system and once again I've found myself using the CFGRID to be able to edit serveral items at once and then save. In the past week we've migrated to SQL Server 2005. I am having an issue with saving NULL values to the db. I just read Charlie's blog from 3/5/2007 where he discussed the NULL attribute for CFQUERYPARAM. Before this, I was using if else logic to enter NULL values into the db but was having an issue with them being written to the db as null (as in the string). Obviously this is less than ideal. Any ideas what I'm doing wrong? Here is a snippet of the code (the actual query covers about 25 different fields): CFCASE value=U CFQUERY name=update datasource=intranet UPDATE tblNAOB SET purchasedate= CFQUERYPARAM cfsqltype=CF_SQL_TIMESTAMP value=#Form.assetgrid.purchasedate[Row]# NULL=#yesNoFormat(NOT len(trim( Form.assetgrid.purchasedate[Row])))#, POnumber= CFQUERYPARAM cfsqltype=CF_SQL_VARCHAR value=#Form.assetgrid.POnumber[Row]# NULL=#yesNoFormat(NOT len(trim( Form.assetgrid.POnumber[Row])))#, equiptype= CFQUERYPARAM cfsqltype=CF_SQL_VARCHAR value=#Form.assetgrid.equiptype[Row]# NULL=#yesNoFormat(NOT len(trim( Form.assetgrid.equiptype[Row])))#, manufacturer= CFQUERYPARAM cfsqltype=CF_SQL_VARCHAR value=#Form.assetgrid.manufacturer[Row]# NULL=#yesNoFormat(NOT len(trim( Form.assetgrid.manufacturer[Row])))#, brandname= CFQUERYPARAM cfsqltype=CF_SQL_VARCHAR value=#Form.assetgrid.brandname[Row]# NULL=#yesNoFormat(NOT len(trim( Form.assetgrid.brandname[Row])))#, model= CFQUERYPARAM cfsqltype=CF_SQL_VARCHAR value=#Form.assetgrid.model[Row]# NULL=#yesNoFormat(NOT len(trim( Form.assetgrid.model[Row])))#, serialnumber= CFQUERYPARAM cfsqltype=CF_SQL_VARCHAR value=#Form.assetgrid.serialnumber[Row]# NULL=#yesNoFormat(NOT len(trim( Form.assetgrid.serialnumber[Row])))#, assettag= CFQUERYPARAM cfsqltype=CF_SQL_VARCHAR value=#Form.assetgrid.assettag[Row]# NULL=#yesNoFormat(NOT len(trim( Form.assetgrid.assettag[Row])))#, owner= CFQUERYPARAM cfsqltype=CF_SQL_VARCHAR value=#Form.assetgrid.owner[Row]# NULL=#yesNoFormat(NOT len(trim( Form.assetgrid.owner[Row])))# WHERE assetID=#Form.assetgrid.assetID[Row]# /CFQUERY /CFCASE Bottom line, why is isnerting the string null into my db instead of NULL??? Is this a SQL Server 2005 issue? - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
Re: [ACFUG Discuss] cfqueryparam and NULL attribute SQL Server 2005
Jeff, Bottom line, why is isnerting the string null into my db instead of NULL??? There's nothing in the code you showed that would insert that value. Just a guess, but -- is your old code in place, the code that tried to create null in a string value? You're experiencing the same behavior you describe when using your old code. And the only way that a string value of null would be inserted is if the variable held that value before you ran the INSERT. In that case, your yesNoFormat function would return false, because the form variable would have a length. And the string null would be inserted. -- Thanks, Tom Tom McNeer MediumCool http://www.mediumcool.com 1735 Johnson Road NE Atlanta, GA 30306 404.589.0560 - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
Re: [ACFUG Discuss] cfqueryparam and NULL attribute SQL Server 2005
Tom, Syntax error on my part. It is an UPDATE that I'm doing and not an INSERT. The code I provided is the update statement. As far as the old code, it exists in a backup file but is not present at all in the live code. The behavior that the app is displaying is as follows: 1. If I click into the formfield and DELETE anything in the field and hit submit it passes an empty string to the action page 2. On the action page I have a CFDUMP and I output this here-- #len(trim(Form.assetgrid.equiptype[Row]))# --#yesNoFormat(NOT len(trim(Form.assetgrid.equiptype[Row])))# BR and this is displayed: here-- 0 --Yes 3. Using Management Studio I look into the DB and see *NULL* displayed there in the appropriate column and row. 4. I revisit my CFGRID/CFFORM and the field is blank (the behavior I am expecting) 5. I submit the form again and this time in the CFDUMP I see the word/string null 6. This time for the output of this code: here-- #len(trim( Form.assetgrid.equiptype[Row]))# --#yesNoFormat(NOT len(trim( Form.assetgrid.equiptype[Row])))# I get this: here-- 4 --No 7. When I then look at the DB in Management Studio I see the word/string null in the appropriate column and row. If I run a query against it looking for all with a value of NULL the record is NOT returned. 8. I then load the form again and in the formfield the word/string null now appears. Any ideas what I'm doing wrong or what I'm over looking? thanks in advance, Jeff On 3/29/07, Tom McNeer [EMAIL PROTECTED] wrote: Jeff, Bottom line, why is isnerting the string null into my db instead of NULL??? There's nothing in the code you showed that would insert that value. Just a guess, but -- is your old code in place, the code that tried to create null in a string value? You're experiencing the same behavior you describe when using your old code. And the only way that a string value of null would be inserted is if the variable held that value before you ran the INSERT. In that case, your yesNoFormat function would return false, because the form variable would have a length. And the string null would be inserted. -- Thanks, Tom Tom McNeer MediumCool http://www.mediumcool.com 1735 Johnson Road NE Atlanta, GA 30306 404.589.0560 - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserformhttp://www.acfug.org/?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com/ - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
RE: [ACFUG Discuss] cfqueryparam and NULL attribute SQL Server 2005
While I am not terribly versed in MSSQL, it sounds like it may be treating the word NULL as a varchar or text value? Troy Jones Dynapp Support Team 678-528-2952 [EMAIL PROTECTED] [EMAIL PROTECTED] From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeff Howard Sent: Thursday, March 29, 2007 1:26 PM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] cfqueryparam and NULL attribute SQL Server 2005 Tom, Syntax error on my part. It is an UPDATE that I'm doing and not an INSERT. The code I provided is the update statement. As far as the old code, it exists in a backup file but is not present at all in the live code. The behavior that the app is displaying is as follows: 1. If I click into the formfield and DELETE anything in the field and hit submit it passes an empty string to the action page 2. On the action page I have a CFDUMP and I output this here-- #len(trim(Form.assetgrid.equiptype[Row]))# --#yesNoFormat(NOT len(trim(Form.assetgrid.equiptype[Row])))# BR and this is displayed: here-- 0 --Yes 3. Using Management Studio I look into the DB and see NULL displayed there in the appropriate column and row. 4. I revisit my CFGRID/CFFORM and the field is blank (the behavior I am expecting) 5. I submit the form again and this time in the CFDUMP I see the word/string null 6. This time for the output of this code: here-- #len(trim(Form.assetgrid.equiptype[Row]))# --#yesNoFormat(NOT len(trim(Form.assetgrid.equiptype[Row])))# I get this: here-- 4 --No 7. When I then look at the DB in Management Studio I see the word/string null in the appropriate column and row. If I run a query against it looking for all with a value of NULL the record is NOT returned. 8. I then load the form again and in the formfield the word/string null now appears. Any ideas what I'm doing wrong or what I'm over looking? thanks in advance, Jeff On 3/29/07, Tom McNeer [EMAIL PROTECTED] wrote: Jeff, Bottom line, why is isnerting the string null into my db instead of NULL??? There's nothing in the code you showed that would insert that value. Just a guess, but -- is your old code in place, the code that tried to create null in a string value? You're experiencing the same behavior you describe when using your old code. And the only way that a string value of null would be inserted is if the variable held that value before you ran the INSERT. In that case, your yesNoFormat function would return false, because the form variable would have a length. And the string null would be inserted. -- Thanks, Tom Tom McNeer MediumCool http://www.mediumcool.com/ http://www.mediumcool.com 1735 Johnson Road NE Atlanta, GA 30306 404.589.0560 - To unsubscribe from this list, manage your profile @ http://www.acfug.org/?fa=login.edituserform http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com/ - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
Re: [ACFUG Discuss] cfqueryparam and NULL attribute SQL Server 2005
I am not sure why the behavior of the cfgrid is getting the value when it queries the data source. Did you check to make sure that the specified column does not have a default value of some type? If 2005 is actually able to send the string null to the cfgrid, then that is indeed a behavior that is odd. When you submit or read data through whichever process, can you perform a quick check for the word null prior to committing and querying to cleanse the data? This may be a band-aid, but would alleviate some frustration to ensure the correct behavior. On 3/29/07, Troy Jones [EMAIL PROTECTED] wrote: While I am not terribly versed in MSSQL, it sounds like it may be treating the word NULL as a varchar or text value? Troy Jones Dynapp Support Team 678-528-2952 [EMAIL PROTECTED] [EMAIL PROTECTED] -- *From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] *On Behalf Of *Jeff Howard *Sent:* Thursday, March 29, 2007 1:26 PM *To:* discussion@acfug.org *Subject:* Re: [ACFUG Discuss] cfqueryparam and NULL attribute SQL Server 2005 Tom, Syntax error on my part. It is an UPDATE that I'm doing and not an INSERT. The code I provided is the update statement. As far as the old code, it exists in a backup file but is not present at all in the live code. The behavior that the app is displaying is as follows: 1. If I click into the formfield and DELETE anything in the field and hit submit it passes an empty string to the action page 2. On the action page I have a CFDUMP and I output this here-- #len(trim(Form.assetgrid.equiptype[Row]))# --#yesNoFormat(NOT len(trim(Form.assetgrid.equiptype[Row])))# BR and this is displayed: here-- 0 --Yes 3. Using Management Studio I look into the DB and see *NULL*displayed there in the appropriate column and row. 4. I revisit my CFGRID/CFFORM and the field is blank (the behavior I am expecting) 5. I submit the form again and this time in the CFDUMP I see the word/string null 6. This time for the output of this code: here-- #len(trim( Form.assetgrid.equiptype[Row]))# --#yesNoFormat(NOT len(trim( Form.assetgrid.equiptype[Row])))# I get this: here-- 4 --No 7. When I then look at the DB in Management Studio I see the word/string null in the appropriate column and row. If I run a query against it looking for all with a value of NULL the record is NOT returned. 8. I then load the form again and in the formfield the word/string null now appears. Any ideas what I'm doing wrong or what I'm over looking? thanks in advance, Jeff On 3/29/07, *Tom McNeer* [EMAIL PROTECTED] wrote: Jeff, Bottom line, why is isnerting the string null into my db instead of NULL??? There's nothing in the code you showed that would insert that value. Just a guess, but -- is your old code in place, the code that tried to create null in a string value? You're experiencing the same behavior you describe when using your old code. And the only way that a string value of null would be inserted is if the variable held that value *before* you ran the INSERT. In that case, your yesNoFormat function would return false, because the form variable would have a length. And the string null would be inserted. -- Thanks, Tom Tom McNeer MediumCool http://www.mediumcool.com/http://www.mediumcool.com 1735 Johnson Road NE Atlanta, GA 30306 404.589.0560 - To unsubscribe from this list, manage your profile @ http://www.acfug.org/?fa=login.edituserform http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglistshttp://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com/ - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com - -- Teddy R. Payne Adobe Certified ColdFusion MX 7 Developer Google Talk - [EMAIL PROTECTED] Atlanta ColdFusion User Group - http://www.acfug.org Atlanta Flash Flex User Group - http://www.affug.org
Re: [ACFUG Discuss] cfqueryparam and NULL attribute SQL Server 2005
Right, there is definitely a band-aid approach. I was hoping to discover or
be shown exactly why this is happening. I'm going to use the band-aid
approach for now using this:
equiptype= CFQUERYPARAM cfsqltype=CF_SQL_VARCHAR
value=#Form.assetgrid.equiptype[Row]# NULL=#yesNoFormat(NOT
len(trim(Replace(Form.assetgrid.equiptype[Row],null,#
That's only midly ugly. I guess it might be better to use and CFIF
statement instead in the case that there is some piece of equipment or
person's name out there that contains null in it.
After reading this email I went back into my code and CFDUMPed my query
immediately before my CFFORM/CFGRID and the value comes in from the query as
[empty string]. I then change a different field on my form and hit submit
and look at the CFDUMP for the form on my action page and there it has
null.
Now what makes this even more odd, is that this doesn't hold true for every
field. I thought maybe it was the ones where I have *type=string_nocase
*but that wasn't it. I took the type attribute out of my CFGRIDCOLUMN tag
and got the same result. It does seem to only be happening to my
*string_nocase
*fields and the datetime and currency work fine.
It seems that there has to be something that I'm over looking. Surely CFML
doesn't decide to replace [empty string] with the string null when I
submit a form from a CFGRID.
It may be worth noting that I have a databind with this grid to a cfinput
type text:
CFINPUT type=text
name=equiptype
width=220
label=Equipment Type:
bind={assetgrid.dataProvider[assetgrid.selectedIndex]['equiptype']}
onChange=assetgrid.dataProvider.editField(assetgrid.selectedIndex
,'equiptype',equiptype.text);
Thanks again for any help/insight.
On 3/29/07, Teddy Payne [EMAIL PROTECTED] wrote:
I am not sure why the behavior of the cfgrid is getting the value when it
queries the data source. Did you check to make sure that the specified
column does not have a default value of some type? If 2005 is actually able
to send the string null to the cfgrid, then that is indeed a behavior that
is odd.
When you submit or read data through whichever process, can you perform a
quick check for the word null prior to committing and querying to cleanse
the data? This may be a band-aid, but would alleviate some frustration to
ensure the correct behavior.
On 3/29/07, Troy Jones [EMAIL PROTECTED] wrote:
While I am not terribly versed in MSSQL, it sounds like it may be
treating the word NULL as a varchar or text value?
Troy Jones
Dynapp Support Team
678-528-2952
[EMAIL PROTECTED]
[EMAIL PROTECTED]
--
*From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] *On Behalf Of *Jeff
Howard
*Sent:* Thursday, March 29, 2007 1:26 PM
*To:* discussion@acfug.org
*Subject:* Re: [ACFUG Discuss] cfqueryparam and NULL attribute SQL
Server 2005
Tom,
Syntax error on my part. It is an UPDATE that I'm doing and not an
INSERT. The code I provided is the update statement. As far as the old
code, it exists in a backup file but is not present at all in the live code.
The behavior that the app is displaying is as follows:
1. If I click into the formfield and DELETE anything in the field
and hit submit it passes an empty string to the action page
2. On the action page I have a CFDUMP and I output this here--
#len(trim(Form.assetgrid.equiptype[Row]))# --#yesNoFormat(NOT
len(trim(Form.assetgrid.equiptype[Row])))# BR and this is
displayed: here-- 0 --Yes
3. Using Management Studio I look into the DB and see *NULL*displayed
there in the appropriate column and row.
4. I revisit my CFGRID/CFFORM and the field is blank (the behavior
I am expecting)
5. I submit the form again and this time in the CFDUMP I see the
word/string null
6. This time for the output of this code: here-- #len(trim(
Form.assetgrid.equiptype[Row]))# --#yesNoFormat(NOT len(trim(
Form.assetgrid.equiptype[Row])))# I get this: here-- 4 --No
7. When I then look at the DB in Management Studio I see the
word/string null in the appropriate column and row. If I run a query
against it looking for all with a value of NULL the record is NOT returned.
8. I then load the form again and in the formfield the word/string
null now appears.
Any ideas what I'm doing wrong or what I'm over looking?
thanks in advance,
Jeff
On 3/29/07, *Tom McNeer* [EMAIL PROTECTED] wrote:
Jeff,
Bottom line, why is isnerting the string null into my db instead of
NULL???
There's nothing in the code you showed that would insert that value.
Just a guess, but -- is your old code in place, the code that tried to
create null in a string value? You're experiencing the same behavior you
describe when using your old code. And the only way that a string value of
null would be inserted is if the variable held that value *before* you
ran the INSERT. In that case, your yesNoFormat
