subject:"\[ACFUG Discuss\] cfqueryparam and NULL attribute SQL Server 2005"

Re: [ACFUG Discuss] cfqueryparam and NULL attribute SQL Server 2005

2007-03-29 Thread Jeff Howard

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= 

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:

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])))# "  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
> ???
>
>
> 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

Re: [ACFUG Discuss] cfqueryparam and NULL attribute SQL Server 2005

2007-03-29 Thread Teddy Payne


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])))# "  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
???


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 FusionLink <http://www.fusionlink.com>

RE: [ACFUG Discuss] cfqueryparam and NULL attribute SQL Server 2005

2007-03-29 Thread Troy Jones

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])))# "  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 ??? 


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

2007-03-29 Thread Jeff Howard


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])))# "  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
> ???


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




-
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

2007-03-29 Thread Tom McNeer


Jeff,

Bottom line, why is isnerting the string "null" into my db instead of

???



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
-

[ACFUG Discuss] cfqueryparam and NULL attribute SQL Server 2005

2007-03-29 Thread Jeff Howard


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


  
   UPDATE tblNAOB
   SET
 purchasedate= ,
 POnumber= ,
 equiptype= ,
 manufacturer= ,
 brandname= ,
 model= ,
 serialnumber= ,
 assettag= ,
 owner= 
WHERE assetID=#Form.assetgrid.assetID[Row]#
  
 

Bottom line, why is isnerting the string "null" into my db instead of
???

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

Re: [ACFUG Discuss] cfqueryparam and NULL attribute SQL Server 2005

RE: [ACFUG Discuss] cfqueryparam and NULL attribute SQL Server 2005

Re: [ACFUG Discuss] cfqueryparam and NULL attribute SQL Server 2005

Re: [ACFUG Discuss] cfqueryparam and NULL attribute SQL Server 2005

[ACFUG Discuss] cfqueryparam and NULL attribute SQL Server 2005

6 matches

Site Navigation

Mail list logo

Footer information