This is a multi-part message in MIME format.
------=_NextPart_000_02A0_01BFF8B9.C9830B70
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Passing NULL integers to a custom tagHave you tried passing NULL to the =
stored procedure? There is no such thing as a NULL integer. It is =
either an integer or it is NULL. NULL, particularly in RDBMSs, has a =
very particular meaning, which is "the absence of a value".
If the value passed to your custom tag is an empty string, that implies =
NULL, but is not really NULL. You have to specify NULL as the value for =
the field. Simply substituting a 0 for NULL may not be the best =
approach, particularly if decisions are made based on the value of the =
field being one thing or another. =20
DC
----- Original Message -----=20
From: Gina Shillitani=20
To: [EMAIL PROTECTED]=20
Sent: Friday, July 28, 2000 13:46
Subject: Passing NULL integers to a custom tag
Apparently something strange happens to INT fields when you try to =
pass them=20
to a custom tag=20
as NULL values. The stored proc snippet below was originally just a =
query in=20
a simple custom tag, and I kept=20
getting the error "cannot convert datatype to int... use convert =
function"=20
when this particular INT value was NULL. I had to use "convert(int,=20
'#attributes.lid#')" and that worked fine. Then I converted the query =
into a=20
stored proc.=20
When the stored proc started failing ([Microsoft][ODBC SQL Server=20
Driver]Invalid character value for cast=20
specification), I suspected this might be the problem again, so I =
added the=20
IF statement=20
below, which seems to have fixed the problem. The table does accept =
NULL=20
values for that field, and if I run the stored proc in iSQL the NULL =
values=20
go through just fine. If I use the stored proc and that INT field has =
a=20
value, it also runs fine, it only fails when that INT is NULL. So, now =
I=20
know what the problem is and how to work around it, but I need a =
better=20
solution than passing a 0 (zero). Has anyone else had this very =
confusing=20
problem?=20
Original param that failed when passed a NULL:=20
<cfprocparam type=3D"In" dbvarname=3D"lid" value=3D#attributes.lid#=20
cfsqltype=3D"CF_SQL_INTEGER">=20
This is how I fixed it:=20
<cfif #attributes.lid# IS NOT ''>=20
<cfprocparam type=3D"In" dbvarname=3Dlid value=3D#attributes.lid#=20
cfsqltype=3D"CF_SQL_INTEGER">=20
<cfelse>=20
<cfprocparam type=3D"In" dbvarname=3D"lid" value=3D0 =
cfsqltype=3D"CF_SQL_INTEGER">=20
</cfif>=20
Gina Shillitani=20
[EMAIL PROTECTED]=20
=
-------------------------------------------------------------------------=
-----=20
Archives: http://www.mail-archive.com/[email protected]/=20
To Unsubscribe visit =
http://www.houseoffusion.com/index.cfm?sidebar=3Dlists&body=3Dlists/cf_ta=
lk or send a message to [EMAIL PROTECTED] with =
'unsubscribe' in the body.
------=_NextPart_000_02A0_01BFF8B9.C9830B70
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Passing NULL integers to a custom tag</TITLE>
<META content=3D"text/html; charset=3Dwindows-1252" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.3018.900" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Have you tried passing NULL to the =
stored=20
procedure? There is no such thing as a NULL integer. It is =
either an=20
integer or it is NULL. NULL, particularly in RDBMSs, has a very =
particular=20
meaning, which is "the absence of a value".</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>If the value passed to your custom tag =
is an empty=20
string, that implies NULL, but is not really NULL. You have to =
specify=20
NULL as the value for the field. Simply substituting a 0 for NULL =
may not=20
be the best approach, particularly if decisions are made based on the =
value of=20
the field being one thing or another. </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>DC</FONT></DIV>
<DIV> </DIV>
<BLOCKQUOTE=20
style=3D"BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: =
0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">
<DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV=20
style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: =
black"><B>From:</B>=20
<A href=3D"mailto:[EMAIL PROTECTED]" =
[EMAIL PROTECTED]>Gina=20
Shillitani</A> </DIV>
<DIV style=3D"FONT: 10pt arial"><B>To:</B> <A=20
href=3D"mailto:[EMAIL PROTECTED]"=20
[EMAIL PROTECTED]>[EMAIL PROTECTED]</A> </DIV>
<DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Friday, July 28, 2000 =
13:46</DIV>
<DIV style=3D"FONT: 10pt arial"><B>Subject:</B> Passing NULL integers =
to a=20
custom tag</DIV>
<DIV><BR></DIV>
<P><FONT size=3D2>Apparently something strange happens to INT fields =
when you=20
try to pass them</FONT> <BR><FONT size=3D2>to a custom tag</FONT> =
<BR><FONT=20
size=3D2>as NULL values. The stored proc snippet below was originally =
just a=20
query in</FONT> <BR><FONT size=3D2>a simple custom tag, and I =
kept</FONT>=20
<BR><FONT size=3D2>getting the error "cannot convert datatype to =
int... use=20
convert function"</FONT> <BR><FONT size=3D2>when this particular INT =
value was=20
NULL. I had to use "convert(int,</FONT> <BR><FONT =
size=3D2>'#attributes.lid#')"=20
and that worked fine. Then I converted the query into a</FONT> =
<BR><FONT=20
size=3D2>stored proc.</FONT> <BR><FONT size=3D2>When the stored proc =
started=20
failing ([Microsoft][ODBC SQL Server</FONT> <BR><FONT =
size=3D2>Driver]Invalid=20
character value for cast</FONT> <BR><FONT size=3D2>specification), I =
suspected=20
this might be the problem again, so I added the</FONT> <BR><FONT =
size=3D2>IF=20
statement</FONT> <BR><FONT size=3D2>below, which seems to have fixed =
the=20
problem. The table does accept NULL</FONT> <BR><FONT size=3D2>values =
for that=20
field, and if I run the stored proc in iSQL the NULL values</FONT> =
<BR><FONT=20
size=3D2>go through just fine. If I use the stored proc and that INT =
field has=20
a</FONT> <BR><FONT size=3D2>value, it also runs fine, it only fails =
when that=20
INT is NULL. So, now I</FONT> <BR><FONT size=3D2>know what the problem =
is and=20
how to work around it, but I need a better</FONT> <BR><FONT =
size=3D2>solution=20
than passing a 0 (zero). Has anyone else had this very =
confusing</FONT>=20
<BR><FONT size=3D2>problem?</FONT> </P>
<P><FONT size=3D2>Original param that failed when passed a =
NULL:</FONT> </P>
<P><FONT size=3D2><cfprocparam type=3D"In" dbvarname=3D"lid"=20
value=3D#attributes.lid#</FONT> <BR><FONT=20
size=3D2>cfsqltype=3D"CF_SQL_INTEGER"></FONT> </P>
<P><FONT size=3D2>This is how I fixed it:</FONT> </P>
<P><FONT size=3D2><cfif #attributes.lid# IS NOT ''></FONT> =
<BR><FONT=20
size=3D2><cfprocparam type=3D"In" dbvarname=3Dlid =
value=3D#attributes.lid#</FONT>=20
<BR><FONT size=3D2>cfsqltype=3D"CF_SQL_INTEGER"></FONT> <BR><FONT=20
size=3D2><cfelse></FONT> <BR><FONT size=3D2><cfprocparam =
type=3D"In"=20
dbvarname=3D"lid" value=3D0 cfsqltype=3D"CF_SQL_INTEGER"></FONT> =
<BR><FONT=20
size=3D2></cfif></FONT> </P>
<P><FONT size=3D2>Gina Shillitani</FONT> <BR><FONT=20
size=3D2>[EMAIL PROTECTED]</FONT> </P>
<P><FONT=20
=
size=3D2>----------------------------------------------------------------=
--------------</FONT>=20
<BR><FONT size=3D2>Archives: <A=20
href=3D"http://www.mail-archive.com/[email protected]/"=20
=
target=3D_blank>http://www.mail-archive.com/[email protected]/</A=
></FONT>=20
<BR><FONT size=3D2>To Unsubscribe visit <A=20
=
href=3D"http://www.houseoffusion.com/index.cfm?sidebar=3Dlists&body=3D=
lists/cf_talk"=20
=
target=3D_blank>http://www.houseoffusion.com/index.cfm?sidebar=3Dlists&am=
p;body=3Dlists/cf_talk</A>=20
or send a message to [EMAIL PROTECTED] with =
'unsubscribe' in=20
the body.</FONT></P></BLOCKQUOTE></BODY></HTML>
------=_NextPart_000_02A0_01BFF8B9.C9830B70--
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
