Char and Varchar: mime

Thursday, March 22, 2012

Check if fieldname exists?

This is a multi-part message in MIME format.
--=_NextPart_000_001F_01C4A64F.273B6E50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Can I do a check if a field name exists like in an expression:
iif(Fields!Time_Month.Value,,)
iif(Exists(Fields!Time_Month.Value,,))
--=_NextPart_000_001F_01C4A64F.273B6E50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Can I do a check if a field name exists = like in an expression:

iif(Fields!Time_Month.Value,,)
iif(Exists(Fields!Time_Month.Value,,))

--=_NextPart_000_001F_01C4A64F.273B6E50--Only field names defined in the RDL file will be accessible in the Fields
collection. If a Field defined in the RDL is not returned by the data source
query, the IsMissing property of this field will be true:
=iif(Fields!xxx.IsMissing, "Field is not returned by query", ...)
More information about the Fields collection can be found in BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_7ilv.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:%230Ue0nopEHA.3396@.tk2msftngp13.phx.gbl...
Can I do a check if a field name exists like in an expression:
iif(Fields!Time_Month.Value,,)
iif(Exists(Fields!Time_Month.Value,,))|||This is a multi-part message in MIME format.
--=_NextPart_000_0044_01C4A6D7.AE802670
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Ok, I see that, but the problem comes when I try to use that field
=3Diif(Fields!xxx.IsMissing, "Field is not returned by query", "Returned =field is"& Fields!xxx.value)
If the field is there, I get
Returned field is (value)
If the field isn't there I don't get anything back.
I just get a field is missing error
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message =news:uv2elNppEHA.1992@.TK2MSFTNGP09.phx.gbl...
> Only field names defined in the RDL file will be accessible in the =Fields
> collection. If a Field defined in the RDL is not returned by the data =source
> query, the IsMissing property of this field will be true:
> =3Diif(Fields!xxx.IsMissing, "Field is not returned by query", ...)
> > More information about the Fields collection can be found in BOL:
> =http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/rscrea=
te/htm/rcr_creating_expressions_v1_7ilv.asp
> > --
> This posting is provided "AS IS" with no warranties, and confers no =rights.
> > > > "Cindy Lee" <cindylee@.hotmail.com> wrote in message
> news:%230Ue0nopEHA.3396@.tk2msftngp13.phx.gbl...
> Can I do a check if a field name exists like in an expression:
> > iif(Fields!Time_Month.Value,,)
> iif(Exists(Fields!Time_Month.Value,,))
> >
--=_NextPart_000_0044_01C4A6D7.AE802670
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Ok, I see that, but the problem comes =when I try to use that field
=3Diif(Fields!xxx.IsMissing, "Field is =not returned by query", "Returned field is"& Fields!xxx.value)
If the field is there, I get
Returned field is (value)
If the field isn't there I don't get =anything back.
I just get a field is missing =error
"Robert Bruckner [MSFT]" wrote in message news:uv2elNppEHA.1992@.TK2MSFTNGP09.phx.gbl...> =Only field names defined in the RDL file will be accessible in the Fields> =collection. If a Field defined in the RDL is not returned by the data source> =query, the IsMissing property of this field will be true:> =3Diif(Fields!xxx.IsMissing, "Field is not returned by query", =...)> > More information about the Fields collection can be found in BOL:>http://msdn.microsoft.com/library/default.asp?url=3D/library/en-=us/rscreate/htm/rcr_creating_expressions_v1_7ilv.asp> > --> This posting is provided ="AS IS" with no warranties, and confers no rights.> > > > ="Cindy Lee" =wrote in message> news:%230Ue0nopEHA.3396@.tk2msftngp13.phx.gbl...> Can I do a check if a field name exists like in an expression:> > iif(Fields!Time_Month.Value,,)> iif(Exists(Fields!Time_Month.Value,,))> > =

--=_NextPart_000_0044_01C4A6D7.AE802670--

Sunday, March 11, 2012

Check Contraint question

This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C67383.F9AD44F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) + = ']%'),[LicensePlateNumber]),0) =3D 0)
which works fine, throwing an error if those characters are entered. Is = there a way to have it not throw an error, but rather just remove the = offending characters if entered? Thanks
--=_NextPart_000_0008_01C67383.F9AD44F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I have the following check constraint

(isnull(patindex(('%[' + ' ' + = char(9) + char(10) + char(13) + ']%'),[LicensePlateNumber]),0) =3D 0)

which works fine, throwing an = error if those characters are entered. Is there a way to have it not throw = an error, but rather just remove the offending characters if entered? = Thanks
--=_NextPart_000_0008_01C67383.F9AD44F0--No, that's not what a constraint does.
You can perhaps use an instead-of trigger to achieve this functionality.
Conor
"Burma Jones" <somebody@.somedomain.not> wrote in message
news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
']%'),[LicensePlateNumber]),0) = 0)
which works fine, throwing an error if those characters are entered. Is
there a way to have it not throw an error, but rather just remove the
offending characters if entered? Thanks|||No. Constraints are declarative and do not perform actions. I would
do this kind of thing inthe front end or in the inpout procedure.
Triggers will fire any time the table is touched and work on all rows,
so they can be a bit costly.|||Since this is only a few thousand records, I'm not too worried about the
cost of using a trigger. Can you share an example, even pseudocode, showing
how to create a trigger which will remove those characters? Thanks
"Conor Cunningham [MS]" <conorc_removeme@.online.microsoft.com> wrote in
message news:eu$9uj9cGHA.4932@.TK2MSFTNGP03.phx.gbl...
> No, that's not what a constraint does.
> You can perhaps use an instead-of trigger to achieve this functionality.
> Conor
> "Burma Jones" <somebody@.somedomain.not> wrote in message
> news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
> I have the following check constraint
> (isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
> ']%'),[LicensePlateNumber]),0) = 0)
> which works fine, throwing an error if those characters are entered. Is
> there a way to have it not throw an error, but rather just remove the
> offending characters if entered? Thanks
>|||On Wed, 10 May 2006 08:26:16 -0700, Burma Jones wrote:
>Since this is only a few thousand records, I'm not too worried about the
>cost of using a trigger. Can you share an example, even pseudocode, showing
>how to create a trigger which will remove those characters? Thanks
Hi Burma,
Here's a sample trigger that will remove the offending characters
silently:
CREATE TRIGGER YourTrigger
ON YourTable INSTEAD OF INSERT
AS
INSERT INTO YourTable (OtherColumns, LicensePlate)
SELECT OtherColumns,
REPLACE(REPLACE(REPLACE(REPLACE(LicensePlate, ' ', ''), CHAR(9),
''), CHAR(10), ''), CHAR(13), ''), OtherColumns
FROM inserted
go
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP

Thursday, March 22, 2012

Check if fieldname exists?

Sunday, March 11, 2012

Check Contraint question

Char and Varchar

Blog Archive

About Me