Immutability of procedure parameters

So, I was having just the most frustrating bug a few months ago, and my
boss says, "Oh, yeah, you should never assign a new value to an IN
parameter in SQL Server."
He couldn't give me a reference -- I'm happy enough not to do it, and
it solved that problem, but I really can't suggest to _other people
that they copy values and so forth unless I can point to some
documentation, which I've been unable to find. Is it anywhere written
that "assigning new values to IN parameters in SQL Server stored
procedures may product unpredictable results?"
Thanks!
IonIon
Do you mean that you get different data back, or the performance is
different.
Obviously, if you have a procedure that finds customers given their id
number, and you call the procedure with customer ID 112 and then the
procedure changes that value to 220 before searching, you'll get results for
different customer than what you were expecting.
I've also found cases of performance problems because of this, because the
proc is optimized based on the value passed IN, and then if you change it to
another value, it still uses the original plan based on the original value.
You should keep parameters as they were, and if you need to modify them,
assign the new value to a local variable, so it is clear what values are
parameters and which are variables. They are treated very differently.
HTH
Kalen Delaney, SQL Server MVP
<ionFreeman@.gmail.com> wrote in message
news:1150834879.252067.128120@.h76g2000cwa.googlegroups.com...
> So, I was having just the most frustrating bug a few months ago, and my
> boss says, "Oh, yeah, you should never assign a new value to an IN
> parameter in SQL Server."
> He couldn't give me a reference -- I'm happy enough not to do it, and
> it solved that problem, but I really can't suggest to _other people
> that they copy values and so forth unless I can point to some
> documentation, which I've been unable to find. Is it anywhere written
> that "assigning new values to IN parameters in SQL Server stored
> procedures may product unpredictable results?"
> Thanks!
> Ion
>|||Kalen,
Thanks for responding. The issue that I was seeing was that if I
modified a passed date parameter directly, the stored procedure never
came back -- the connection wasn't closed and no data was returned. The
calling application indicates that it wished to cover a maximal date
range by passing identical start and end times, and if I tried to
implement that logic by modifying the passed dates themselves, I lost
contact with the process -- it was really pretty mysterious.
It's possible this plan business is the ticket -- the proc expected
to return no data (based on its infinitesimal date range), but ended up
with all of it.
Thanks a bunch! Poking around in TechNet, I found
http://www.microsoft.com/technet/pr...comp.mspx#EDUAE
which asserts your point.
Ion
Kalen Delaney wrote:
> Ion
> Do you mean that you get different data back, or the performance is
> different.
> Obviously, if you have a procedure that finds customers given their id
> number, and you call the procedure with customer ID 112 and then the
> procedure changes that value to 220 before searching, you'll get results f
or
> different customer than what you were expecting.
> I've also found cases of performance problems because of this, because the
> proc is optimized based on the value passed IN, and then if you change it
to
> another value, it still uses the original plan based on the original value
.
> You should keep parameters as they were, and if you need to modify them,
> assign the new value to a local variable, so it is clear what values are
> parameters and which are variables. They are treated very differently.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <ionFreeman@.gmail.com> wrote in message
> news:1150834879.252067.128120@.h76g2000cwa.googlegroups.com...sql

Images in a table cell, conditional on a data item value

Phew!
I have a table, and in one cell I want to display an image. No big deal. However, I then want another image to be substituted for the original one, if a certain field value is false.

So the logic is,if field value is true, display image 1
if the same field value is false, display image no 2.

The expression I have written so far is:

-iif(Fields!Expr9.Value=False,"image1","image2")

where both image1 and image2 are images embedded into the report.

It shows the first image when the field value is false, but It isn't showing image 2 on lines where the field value is true.

Any takers?

What happens if you invert a condition, i.e.

=iif(Fields!Expr9.Value,"image2","image1")

Image size while export

Hello,

I'm using Reporting Services 2005.

I have image that data value set to take the image from a web server.

I also set the sizing to "AutoSize".

In the report while the image size is more than I set in the design mode the report, the image grow and all other object in the report move.

but while I export to the excel the image size in the excel is the same size I set in the design mode.

While I export to PDF the image grow but all other objects are not moving and the image go up on other object.

can you help me ?

How are you positioning this image? Is this a single image or multiple images in a data table/matrix?|||Single image|||

I'm not sure why this is happening but I suspect it's down to the implementation of each Rendering Extension. You see each output format (HTML, XLS, PDF etc...) is programmed separately and it sounds like they treat image rendering differently.

Considering it's a single image and assuming that this image doesn't change size, the best advice I can give you is to check the dimensions of the image and to set them explicitly within the report instead of using the Auto option. That way you'll also have a better feel for what effect the image has on the rest of the report at design time.

Also try putting the image in the header or the footer to prevent it from affecting other report items in the body of the report.

Sorry I couldn't be of more help.

Image on each pahe header

I can insert image in header from a database by using :
- in body a textbox (name = textbox33) with value:
=convert.toBase64String(first(fields!Image.value,"IMAGE"))
- in header an image with value:
=convert.fromBase64String(ReportItems!textbox33.value)
Image appears on first page only, after just a red X appears!!
Any ideas or a solution... Thanks.that's because textbox33 appears on 1st page only.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ginnie" <Ginnie@.discussions.microsoft.com> wrote in message
news:38ABA450-786E-4A86-936A-F04C6EA6483A@.microsoft.com...
> I can insert image in header from a database by using :
> - in body a textbox (name = textbox33) with value:
> =convert.toBase64String(first(fields!Image.value,"IMAGE"))
> - in header an image with value:
> =convert.fromBase64String(ReportItems!textbox33.value)
> Image appears on first page only, after just a red X appears!!
> Any ideas or a solution... Thanks.
>
>|||my textbox33 is directly on body.
how can I make textbox33 appearing on each page?
Thanks
"Lev Semenets [MSFT]" wrote:
> that's because textbox33 appears on 1st page only.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Ginnie" <Ginnie@.discussions.microsoft.com> wrote in message
> news:38ABA450-786E-4A86-936A-F04C6EA6483A@.microsoft.com...
> > I can insert image in header from a database by using :
> > - in body a textbox (name = textbox33) with value:
> > =convert.toBase64String(first(fields!Image.value,"IMAGE"))
> > - in header an image with value:
> > =convert.fromBase64String(ReportItems!textbox33.value)
> >
> > Image appears on first page only, after just a red X appears!!
> >
> > Any ideas or a solution... Thanks.
> >
> >
> >
> >
>
>|||excuse me but how can i recup your sample report. I don't see it
"Lev Semenets [MSFT]" wrote:
> There is another approach: using parameter and calculated field.
> I'm attaching sample report.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Ginnie" <Ginnie@.discussions.microsoft.com> wrote in message
> news:BCCB848A-CFFD-47E7-A181-4E7BDC8A81CD@.microsoft.com...
> > my textbox33 is directly on body.
> > how can I make textbox33 appearing on each page?
> > Thanks
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> > > that's because textbox33 appears on 1st page only.
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "Ginnie" <Ginnie@.discussions.microsoft.com> wrote in message
> > > news:38ABA450-786E-4A86-936A-F04C6EA6483A@.microsoft.com...
> > > > I can insert image in header from a database by using :
> > > > - in body a textbox (name = textbox33) with value:
> > > > =convert.toBase64String(first(fields!Image.value,"IMAGE"))
> > > > - in header an image with value:
> > > > =convert.fromBase64String(ReportItems!textbox33.value)
> > > >
> > > > Image appears on first page only, after just a red X appears!!
> > > >
> > > > Any ideas or a solution... Thanks.
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>|||I'm sorry but how can i recup sample report? I don't see it !!
"Lev Semenets [MSFT]" wrote:
> There is another approach: using parameter and calculated field.
> I'm attaching sample report.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Ginnie" <Ginnie@.discussions.microsoft.com> wrote in message
> news:BCCB848A-CFFD-47E7-A181-4E7BDC8A81CD@.microsoft.com...
> > my textbox33 is directly on body.
> > how can I make textbox33 appearing on each page?
> > Thanks
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> > > that's because textbox33 appears on 1st page only.
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "Ginnie" <Ginnie@.discussions.microsoft.com> wrote in message
> > > news:38ABA450-786E-4A86-936A-F04C6EA6483A@.microsoft.com...
> > > > I can insert image in header from a database by using :
> > > > - in body a textbox (name = textbox33) with value:
> > > > =convert.toBase64String(first(fields!Image.value,"IMAGE"))
> > > > - in header an image with value:
> > > > =convert.fromBase64String(ReportItems!textbox33.value)
> > > >
> > > > Image appears on first page only, after just a red X appears!!
> > > >
> > > > Any ideas or a solution... Thanks.
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>|||send me e-mail and i'll send it to you.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ginnie" <Ginnie@.discussions.microsoft.com> wrote in message
news:9B2FBA35-568F-4520-902E-964BF04D0CB2@.microsoft.com...
> I'm sorry but how can i recup sample report? I don't see it !!
> "Lev Semenets [MSFT]" wrote:
> > There is another approach: using parameter and calculated field.
> > I'm attaching sample report.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Ginnie" <Ginnie@.discussions.microsoft.com> wrote in message
> > news:BCCB848A-CFFD-47E7-A181-4E7BDC8A81CD@.microsoft.com...
> > > my textbox33 is directly on body.
> > > how can I make textbox33 appearing on each page?
> > > Thanks
> > >
> > > "Lev Semenets [MSFT]" wrote:
> > >
> > > > that's because textbox33 appears on 1st page only.
> > > >
> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > >
> > > > "Ginnie" <Ginnie@.discussions.microsoft.com> wrote in message
> > > > news:38ABA450-786E-4A86-936A-F04C6EA6483A@.microsoft.com...
> > > > > I can insert image in header from a database by using :
> > > > > - in body a textbox (name = textbox33) with value:
> > > > > =convert.toBase64String(first(fields!Image.value,"IMAGE"))
> > > > > - in header an image with value:
> > > > > =convert.fromBase64String(ReportItems!textbox33.value)
> > > > >
> > > > > Image appears on first page only, after just a red X appears!!
> > > > >
> > > > > Any ideas or a solution... Thanks.
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Image not displayed in report

I am creating a very simple report in VS 2005. I dragged an image
control to the report surface and set it's value property to the
virtual path of my image (like "~/SubPage/Trademark.jpg"). However,
the image is never displayed - either at design time, when the report
viewer runs, or when the report is exported to PDF. I just get the
standard "No image" (red X) icon.
I have read other posts about this but most of them were trying to do
something more complicated than me. At this point, I am not trying to
do any logic for the visibility or the value it is trying to display.
I read about a bug that might be related here:
http://support.microsoft.com/kb/842537/
...but I am not sure if this pertains to me (as I am not pointing to a
URL, but rather a virtual path). I am leery of applying this SP2
patch, as there is no way to uninstall it, and I am not sure if it
will "break" anything that is currently working.
I would appreciate any suggestions that anyone could give me.
Thank you!Kirk,
Try embedding the image into the report instead of using the URL.
MM
"Kirk" wrote:
> I am creating a very simple report in VS 2005. I dragged an image
> control to the report surface and set it's value property to the
> virtual path of my image (like "~/SubPage/Trademark.jpg"). However,
> the image is never displayed - either at design time, when the report
> viewer runs, or when the report is exported to PDF. I just get the
> standard "No image" (red X) icon.
> I have read other posts about this but most of them were trying to do
> something more complicated than me. At this point, I am not trying to
> do any logic for the visibility or the value it is trying to display.
> I read about a bug that might be related here:
> http://support.microsoft.com/kb/842537/
> ...but I am not sure if this pertains to me (as I am not pointing to a
> URL, but rather a virtual path). I am leery of applying this SP2
> patch, as there is no way to uninstall it, and I am not sure if it
> will "break" anything that is currently working.
> I would appreciate any suggestions that anyone could give me.
> Thank you!
>|||I´ve also tested what you say and it does not work with relative path.
To make it work, do what Marie says or use absolute URL
(http://www.yourpage.com/Subpage/Trademark.jpg)
"Kirk" <loki70@.hotmail.com> escribió en el mensaje
news:1193753651.198915.166370@.k79g2000hse.googlegroups.com...
>I am creating a very simple report in VS 2005. I dragged an image
> control to the report surface and set it's value property to the
> virtual path of my image (like "~/SubPage/Trademark.jpg"). However,
> the image is never displayed - either at design time, when the report
> viewer runs, or when the report is exported to PDF. I just get the
> standard "No image" (red X) icon.
> I have read other posts about this but most of them were trying to do
> something more complicated than me. At this point, I am not trying to
> do any logic for the visibility or the value it is trying to display.
> I read about a bug that might be related here:
> http://support.microsoft.com/kb/842537/
> ...but I am not sure if this pertains to me (as I am not pointing to a
> URL, but rather a virtual path). I am leery of applying this SP2
> patch, as there is no way to uninstall it, and I am not sure if it
> will "break" anything that is currently working.
> I would appreciate any suggestions that anyone could give me.
> Thank you!
>|||On Oct 31, 12:21 pm, "M=F3nica" <monica.d...@.augure.com> wrote:
> I=B4ve also tested what you say and it does not work with relative path.
> To make it work, do what Marie says or use absolute URL
> (http://www.yourpage.com/Subpage/Trademark.jpg)
> "Kirk" <lok...@.hotmail.com> escribi=F3 en el mensajenews:1193753651.19891=5=2E166370@.k79g2000hse.googlegroups.com...
>
> >I am creating a very simple report in VS 2005. I dragged an image
> > control to the report surface and set it's value property to the
> > virtual path of my image (like "~/SubPage/Trademark.jpg"). However,
> > the image is never displayed - either at design time, when the report
> > viewer runs, or when the report is exported to PDF. I just get the
> > standard "No image" (red X) icon.
> > I have read other posts about this but most of them were trying to do
> > something more complicated than me. At this point, I am not trying to
> > do any logic for the visibility or the value it is trying to display.
> > I read about a bug that might be related here:
> >http://support.microsoft.com/kb/842537/
> > ...but I am not sure if this pertains to me (as I am not pointing to a
> > URL, but rather a virtual path). I am leery of applying this SP2
> > patch, as there is no way to uninstall it, and I am not sure if it
> > will "break" anything that is currently working.
> > I would appreciate any suggestions that anyone could give me.
> > Thank you!- Hide quoted text -
> - Show quoted text -
Thanks for the replies. I used the absolute URL and that worked.
It's unfortunate that this does not work with a relative path.
Obviously, thing change (and move around on servers) and having to
update the application just to update absolute URL's is goofy. I
know, I know, you can add this as a configuration setting so that you
don't have to rebuild the site - I was just whining.
Thanks again!

Image DataType Default Value

Hi ,

Iam using sqlserver 2005. what default value i can set to image data type.

Thanks

vijay

The Image Datatype is actualy a Binary datatype.

But you can try setting it to 0x00. That does work for VarBinary(max)

Why are you wanting to set a default value?

Image And Expression

hi there,
i using an expression at the data value with the IIF statement to call the
image which i have embeded 2 images and wanna to display them on the table
textbox when they they satify the IIF statement. and i encounter this error;
The value expression for the image â'image6â' contains an error: [BC30451] Name
'check' is not declared.
how can i resolve this problem or is there any other alternative for mi to
this issue.because wat i want is that within the IIF statement i wan to
display the different image.
thanks in advance
Angelacan you post the expression
"Angela" wrote:
> hi there,
> i using an expression at the data value with the IIF statement to call the
> image which i have embeded 2 images and wanna to display them on the table
> textbox when they they satify the IIF statement. and i encounter this error;
> The value expression for the image â'image6â' contains an error: [BC30451] Name
> 'check' is not declared.
> how can i resolve this problem or is there any other alternative for mi to
> this issue.because wat i want is that within the IIF statement i wan to
> display the different image.
> thanks in advance
> Angela
>|||=iif( First(Fields!allergyRoute_CODE.Value, "DataSet1") = Fields!Code.Value ,
check , checked )
(the image name is check and checked)
"Antoon" wrote:
> can you post the expression
> "Angela" wrote:
> > hi there,
> > i using an expression at the data value with the IIF statement to call the
> > image which i have embeded 2 images and wanna to display them on the table
> > textbox when they they satify the IIF statement. and i encounter this error;
> > The value expression for the image â'image6â' contains an error: [BC30451] Name
> > 'check' is not declared.
> > how can i resolve this problem or is there any other alternative for mi to
> > this issue.because wat i want is that within the IIF statement i wan to
> > display the different image.
> >
> > thanks in advance
> > Angela
> >|||ok,
- The name of the image has to be between quotes.
- You can not put this in a textbox, you can put the expression in an
image-box. So first put an image-box on you report (or in a table cell) and
than change the value of the image to the expression.
- In the expression you've given the Code-value of each row of the table is
compared to the "First" allergyRout_Code-value. This migth be correct, but if
you want to check against every value of allergyRout_Code, you should ommit
the "First"- function (if they are in the same dataset)
=iif( Fields!allergyRoute_CODE.Value = Fields!Code.Value ,
"check" , "checked" )
"Angela" wrote:
> =iif( First(Fields!allergyRoute_CODE.Value, "DataSet1") = Fields!Code.Value ,
> check , checked )
> (the image name is check and checked)
> "Antoon" wrote:
> > can you post the expression
> >
> > "Angela" wrote:
> >
> > > hi there,
> > > i using an expression at the data value with the IIF statement to call the
> > > image which i have embeded 2 images and wanna to display them on the table
> > > textbox when they they satify the IIF statement. and i encounter this error;
> > > The value expression for the image â'image6â' contains an error: [BC30451] Name
> > > 'check' is not declared.
> > > how can i resolve this problem or is there any other alternative for mi to
> > > this issue.because wat i want is that within the IIF statement i wan to
> > > display the different image.
> > >
> > > thanks in advance
> > > Angela
> > >|||hi Antoon,
for the value to compare, they are from 2 different dataset. bcause i want
to compare the actual value with the master values. so if the answer matches
then i will display the correct image. And is there another other way apart
from making them to be the same dataset?
ur regards
Angela
"Antoon" wrote:
> ok,
> - The name of the image has to be between quotes.
> - You can not put this in a textbox, you can put the expression in an
> image-box. So first put an image-box on you report (or in a table cell) and
> than change the value of the image to the expression.
> - In the expression you've given the Code-value of each row of the table is
> compared to the "First" allergyRout_Code-value. This migth be correct, but if
> you want to check against every value of allergyRout_Code, you should ommit
> the "First"- function (if they are in the same dataset)
> =iif( Fields!allergyRoute_CODE.Value = Fields!Code.Value ,
> "check" , "checked" )
>
> "Angela" wrote:
> > =iif( First(Fields!allergyRoute_CODE.Value, "DataSet1") = Fields!Code.Value ,
> > check , checked )
> >
> > (the image name is check and checked)
> >
> > "Antoon" wrote:
> >
> > > can you post the expression
> > >
> > > "Angela" wrote:
> > >
> > > > hi there,
> > > > i using an expression at the data value with the IIF statement to call the
> > > > image which i have embeded 2 images and wanna to display them on the table
> > > > textbox when they they satify the IIF statement. and i encounter this error;
> > > > The value expression for the image â'image6â' contains an error: [BC30451] Name
> > > > 'check' is not declared.
> > > > how can i resolve this problem or is there any other alternative for mi to
> > > > this issue.because wat i want is that within the IIF statement i wan to
> > > > display the different image.
> > > >
> > > > thanks in advance
> > > > Angela
> > > >|||Yes there are, but if you really wan't to compare
First(Fields!allergyRoute_CODE.Value, "DataSet1") = Fields!Code.Value
then
=iif( First(Fields!allergyRoute_CODE.Value, "DataSet1") = Fields!Code.Value ,
"check" , "checked" )
should work fine (in an image-box-in-a-table-field)
"Angela" wrote:
> hi Antoon,
> for the value to compare, they are from 2 different dataset. bcause i want
> to compare the actual value with the master values. so if the answer matches
> then i will display the correct image. And is there another other way apart
> from making them to be the same dataset?
> ur regards
> Angela
> "Antoon" wrote:
> > ok,
> > - The name of the image has to be between quotes.
> > - You can not put this in a textbox, you can put the expression in an
> > image-box. So first put an image-box on you report (or in a table cell) and
> > than change the value of the image to the expression.
> > - In the expression you've given the Code-value of each row of the table is
> > compared to the "First" allergyRout_Code-value. This migth be correct, but if
> > you want to check against every value of allergyRout_Code, you should ommit
> > the "First"- function (if they are in the same dataset)
> > =iif( Fields!allergyRoute_CODE.Value = Fields!Code.Value ,
> > "check" , "checked" )
> >
> >
> > "Angela" wrote:
> >
> > > =iif( First(Fields!allergyRoute_CODE.Value, "DataSet1") = Fields!Code.Value ,
> > > check , checked )
> > >
> > > (the image name is check and checked)
> > >
> > > "Antoon" wrote:
> > >
> > > > can you post the expression
> > > >
> > > > "Angela" wrote:
> > > >
> > > > > hi there,
> > > > > i using an expression at the data value with the IIF statement to call the
> > > > > image which i have embeded 2 images and wanna to display them on the table
> > > > > textbox when they they satify the IIF statement. and i encounter this error;
> > > > > The value expression for the image â'image6â' contains an error: [BC30451] Name
> > > > > 'check' is not declared.
> > > > > how can i resolve this problem or is there any other alternative for mi to
> > > > > this issue.because wat i want is that within the IIF statement i wan to
> > > > > display the different image.
> > > > >
> > > > > thanks in advance
> > > > > Angela
> > > > >

Image , text , ntext..

Is there any way I can comapre the new value of text, ntext and image with
the existing text, ntext and image column value. I have to do validation if
that text is not exist then it should inserted otherwise not..
Anyway I can do this validation?
Thanks in advance.
Hello,
You may check for NULL but cannot compare the values.
Example:
SELECT * FROM SomeTable WHERE TextColumn IS NULL
Hope that helps.
Amit Basu
"Rogers" wrote:

> Is there any way I can comapre the new value of text, ntext and image with
> the existing text, ntext and image column value. I have to do validation if
> that text is not exist then it should inserted otherwise not..
> Anyway I can do this validation?
> Thanks in advance.
>
>
|||Hello,
You may check for NULL but cannot compare the values.
Example:
SELECT * FROM SomeTable WHERE TextColumn IS NULL
Hope that helps.
Amit Basu
"Rogers" wrote:

> Is there any way I can comapre the new value of text, ntext and image with
> the existing text, ntext and image column value. I have to do validation if
> that text is not exist then it should inserted otherwise not..
> Anyway I can do this validation?
> Thanks in advance.
>
>
|||Hello,
You can check for existence of data only and not compare values like:
select * from SomeTable where TextColumn is null
Hope that helps.
Amit Basu
"Rogers" wrote:

> Is there any way I can comapre the new value of text, ntext and image with
> the existing text, ntext and image column value. I have to do validation if
> that text is not exist then it should inserted otherwise not..
> Anyway I can do this validation?
> Thanks in advance.
>
>

Im getting a subquery returned more than 1 value error. Please Help!

The error is occurring in the where clause that I spaced out down below, Its dealing with the State_No I know for a fact. I can't figure out how to run it in the Query anzlyzer to relate to match up with another table

PLEASE HELP!!!!!!

This is the error I'm getting in the DTS package i ran and query analyzer:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Update HBC_Boiler_Inspection
set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "

strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = " & rs1 ("State_No") & ") "try

Boiler_ID IN (Select ID from ...

caution: this will update all the boilers selected in the subquery

rudy

I'm confused about "Null" fields and Default values

I have defined a table with a "DateCreated" field. I put "(getdate())"
(w/o quotes) in the Default Value field when I defined the table (Allow
Nulls). First I tried adding records (via a VB.NET app) and the records
added fine, but no dates. So I turned off "Allow Nulls". Now, I am getting
an error if I don't fill in the date field from the app..."Null not
allowed." I thought (wrongly, obviously) that the point of "Default Value"
was to furnish a "Default Value."
Please straighten me out...
TIA,
Larry WoodsNULL != empty string! If your column doesn't allow NULLs, and you want to
supply a default value, you should not specify that column at all in your
insert statement.
"Larry Woods" <larry@.lwoods.com> wrote in message
news:e7xPbEZoDHA.488@.tk2msftngp13.phx.gbl...
> I have defined a table with a "DateCreated" field. I put "(getdate())"
> (w/o quotes) in the Default Value field when I defined the table (Allow
> Nulls). First I tried adding records (via a VB.NET app) and the records
> added fine, but no dates. So I turned off "Allow Nulls". Now, I am
getting
> an error if I don't fill in the date field from the app..."Null not
> allowed." I thought (wrongly, obviously) that the point of "Default
Value"
> was to furnish a "Default Value."
> Please straighten me out...
> TIA,
> Larry Woods
>|||I had "hoped" that I could either (1) insert the date myself or, if not,
then SQL would insert the default date (getdate()) that I had specified.
Guess not, huh?
Larry
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23fgAdJZoDHA.424@.TK2MSFTNGP10.phx.gbl...
> NULL != empty string! If your column doesn't allow NULLs, and you want to
> supply a default value, you should not specify that column at all in your
> insert statement.
>
>
> "Larry Woods" <larry@.lwoods.com> wrote in message
> news:e7xPbEZoDHA.488@.tk2msftngp13.phx.gbl...
> > I have defined a table with a "DateCreated" field. I put "(getdate())"
> > (w/o quotes) in the Default Value field when I defined the table (Allow
> > Nulls). First I tried adding records (via a VB.NET app) and the records
> > added fine, but no dates. So I turned off "Allow Nulls". Now, I am
> getting
> > an error if I don't fill in the date field from the app..."Null not
> > allowed." I thought (wrongly, obviously) that the point of "Default
> Value"
> > was to furnish a "Default Value."
> >
> > Please straighten me out...
> >
> > TIA,
> >
> > Larry Woods
> >
> >
>|||> I had "hoped" that I could either (1) insert the date myself or, if not,
> then SQL would insert the default date (getdate()) that I had specified.
> Guess not, huh?
Yes, you can! You need to make sure you understand what "it not" means.
Leave the column out of your INSERT statement, rather than setting it (or
not setting it).
CREATE TABLE blat
(
id INT,
dateColumn DATETIME DEFAULT GETDATE()
)
-- now compare:
INSERT blat(id) VALUES(1)
INSERT blat(id, dateColumn) VALUES(2, '2003-10-31')
INSERT blat(id, dateColumn) VALUES(3, NULL)
INSERT blat(id, dateColumn) VALUES(4, '')
SELECT * FROM blat
DROP TABLE blat|||Aaron is it possible to do somthing like this, I'm sure I've read it
somewhere:
INSERT blat(id, dateColumn) VALUES(4, DEFAULT)
Al.
On Sun, 2 Nov 2003 18:32:31 -0500, "Aaron Bertrand [MVP]"
<aaron@.TRASHaspfaq.com> wrote:
>> I had "hoped" that I could either (1) insert the date myself or, if not,
>> then SQL would insert the default date (getdate()) that I had specified.
>> Guess not, huh?
>Yes, you can! You need to make sure you understand what "it not" means.
>Leave the column out of your INSERT statement, rather than setting it (or
>not setting it).
>CREATE TABLE blat
>(
> id INT,
> dateColumn DATETIME DEFAULT GETDATE()
>)
>-- now compare:
>INSERT blat(id) VALUES(1)
>INSERT blat(id, dateColumn) VALUES(2, '2003-10-31')
>INSERT blat(id, dateColumn) VALUES(3, NULL)
>INSERT blat(id, dateColumn) VALUES(4, '')
>SELECT * FROM blat
>DROP TABLE blat
>|||> Aaron is it possible to do somthing like this, I'm sure I've read it
> somewhere:
> INSERT blat(id, dateColumn) VALUES(4, DEFAULT)
Sure. You can use the DEFAULT keyword to let SQL Server apply a
timestamp/rowversion, value generated by a default constraint or a NULL. Not
an identity, though.
--
Tibor Karaszi
"Harag" <harag@.softGETRIDOFCAPLETTERShome.net> wrote in message
news:ro2cqv86knq0mjl89av3enng7nv3s4gemd@.4ax.com...
> Aaron is it possible to do somthing like this, I'm sure I've read it
> somewhere:
> INSERT blat(id, dateColumn) VALUES(4, DEFAULT)
> Al.
> On Sun, 2 Nov 2003 18:32:31 -0500, "Aaron Bertrand [MVP]"
> <aaron@.TRASHaspfaq.com> wrote:
> >> I had "hoped" that I could either (1) insert the date myself or, if
not,
> >> then SQL would insert the default date (getdate()) that I had
specified.
> >> Guess not, huh?
> >
> >Yes, you can! You need to make sure you understand what "it not" means.
> >Leave the column out of your INSERT statement, rather than setting it (or
> >not setting it).
> >
> >CREATE TABLE blat
> >(
> > id INT,
> > dateColumn DATETIME DEFAULT GETDATE()
> >)
> >
> >-- now compare:
> >INSERT blat(id) VALUES(1)
> >INSERT blat(id, dateColumn) VALUES(2, '2003-10-31')
> >INSERT blat(id, dateColumn) VALUES(3, NULL)
> >INSERT blat(id, dateColumn) VALUES(4, '')
> >
> >SELECT * FROM blat
> >
> >DROP TABLE blat
> >
> >
>|||To All:
Thanks for the advice. The problem comes down to this: I am using ADO.NET
and a Data Adapter. The Data Adapter gens the INSERT and obviously doesn't
handle the DEFAULT... The INSERT has a placeholder defined for the date
field, and seemingly no logic to handle the test for a default value. Guess
I will have to plug in the date myself.
My problem is that I am coming from the "kiddie" world of Access, and it
handled this situation just fine; i.e., if you didn't enter a value it
plugged in the default.
Oh, well.
Thanks, again.
Larry
"Larry Woods" <larry@.lwoods.com> wrote in message
news:%23VyH%23hZoDHA.2272@.tk2msftngp13.phx.gbl...
> I had "hoped" that I could either (1) insert the date myself or, if not,
> then SQL would insert the default date (getdate()) that I had specified.
> Guess not, huh?
> Larry
> "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> news:%23fgAdJZoDHA.424@.TK2MSFTNGP10.phx.gbl...
> > NULL != empty string! If your column doesn't allow NULLs, and you want
to
> > supply a default value, you should not specify that column at all in
your
> > insert statement.
> >
> >
> >
> >
> >
> > "Larry Woods" <larry@.lwoods.com> wrote in message
> > news:e7xPbEZoDHA.488@.tk2msftngp13.phx.gbl...
> > > I have defined a table with a "DateCreated" field. I put
"(getdate())"
> > > (w/o quotes) in the Default Value field when I defined the table
(Allow
> > > Nulls). First I tried adding records (via a VB.NET app) and the
records
> > > added fine, but no dates. So I turned off "Allow Nulls". Now, I am
> > getting
> > > an error if I don't fill in the date field from the app..."Null not
> > > allowed." I thought (wrongly, obviously) that the point of "Default
> > Value"
> > > was to furnish a "Default Value."
> > >
> > > Please straighten me out...
> > >
> > > TIA,
> > >
> > > Larry Woods
> > >
> > >
> >
> >
>|||Hi Larry,
Thanks for your feedback. I think this article will help you a lot.
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=gZPKVHzg
DHA.2624%40cpmsftngxa06.phx.gbl&rnum=1&prev=/groups%3Fq%3Dv-kevy%2Bdefault%2
Bvalue%2Bsql%2Bdataset%2Btyped%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%2
6selm%3DgZPKVHzgDHA.2624%2540cpmsftngxa06.phx.gbl%26rnum%3D1
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||I found the Google response. It says to put the default value in the xsd
property for the field. If I want either "now()" or "date()" as the
default, how do I specify that? I tried both and got errors both times. By
looking at the XML I can see why, I just don't know enough about the format
of the XSD to know how to specify "code" in the XSD.
Please advise...
TIA,
Larry
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:RTDs5$toDHA.2700@.cpmsftngxa06.phx.gbl...
> Hi Larry,
> Thanks for your feedback. I think this article will help you a lot.
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=gZPKVHzg
>
DHA.2624%40cpmsftngxa06.phx.gbl&rnum=1&prev=/groups%3Fq%3Dv-kevy%2Bdefault%2
>
Bvalue%2Bsql%2Bdataset%2Btyped%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%2
> 6selm%3DgZPKVHzgDHA.2624%2540cpmsftngxa06.phx.gbl%26rnum%3D1
> Please feel free to post in the group if this solves your problem or if
you
> would like further assistance.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Hi Larry,
Thanks for your feedback. As I understand, you are using ADO.NET with
Dataadapter to connect SQL Server. You want to find a way to set the
default value of the column before you update the dataset. If I have
misunderstood, please feel free to let me know.
Based on my research, I would like you to try to add the following
statements in the codes to see if they solve your problem.
this.dataset11.Tables["<TableName>"].Columns["<ColumnName>"].DefaultValue =DateTime.Now;
dataset11 is the name of the Dataset object.
For additional information regarding this issue, please refer to the
following article.
DataColumn.DefaultValue Property
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfSystemDataDataColumnClassDefaultValueTopic.asp
Also, it seems that current problem in this issue is related to ADO.NET
programming. I think the current news group is not the best one for this
problem. To resolve this problem, you may need to program the code with
DefaultValue property of the DataColumn. Therefore, I suggest that you post
this question in the microsoft.public.dotnet.framework.adonet newsgroup,
which is primarily for issues involving ADO.NET programming.
The reason why we recommend posting appropriately is you will get the most
qualified pool of respondents, and other partners who read the newsgroups
regularly can either share their knowledge or learn from your interaction
with us. I hope the problem can be resolved quickly.
Thank you for using our Newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Iif use

I am using this code, trying to set the bit value of @.temp1 using the Iif statement:

declare @.temp1 bit,
@.var1 varchar,
@.var2 varchar,
@.var3 varchar

select @.var1='testing', @.var2='testing2'

Select @.temp1 = Iif((@.var1 = @.var2), 1, 0)

select @.temp1

I get this error:
Line 9: Incorrect syntax near '='.

Any suggestions?
ThanksI belive yu are using IIF in the wrong context try:

case when @.var1 = @.var2 then 1 else 0 end|||declare @.temp1 bit,
@.var1 varchar,
@.var2 varchar,
@.var3 varchar

select @.var1='testing', @.var2='testing2', @.temp1=0

Select @.temp1 = case when @.var1 = @.var2 then 1 else 0 end

select @.temp1 as result

OK, I changed it to the codeabove. But now result comes back as 1, how is that possible since the 2 variables arent equal?|||try:

declare @.temp1 bit,
@.var1 varchar(10),
@.var2 varchar(10),
@.var3 varchar(10)
select @.var1='testing'
, @.var2='testing2'
, @.temp1=0
Select @.temp1 = case when @.var1 = @.var2 then 1 else 0 end
select @.temp1 as result|||That worked. Wonder why the first one didnt. Thanks!

Originally posted by Paul Young
try:

declare @.temp1 bit,
@.var1 varchar(10),
@.var2 varchar(10),
@.var3 varchar(10)
select @.var1='testing'
, @.var2='testing2'
, @.temp1=0
Select @.temp1 = case when @.var1 = @.var2 then 1 else 0 end
select @.temp1 as result|||By default Varchar (or Char, NChar, NVarchar, etc.) is a 1 character string. Adding the (10) makes it a 10 byte string.

IIF String Contains a value?

I want to create an IIF expression that changes the color of a field based on if a string value contains a 4 or 5 Any ideas on how to accomplish this?

Hi,

You can write an iif() expression in the background color property of that field.

The following expression will help:

iif(instr("string5",5) or instr("string4",4),"Gray","White")

You can change the color by choosing what u require from the constants provided else custon color.

Somiya

|||Thanks, but I think I need a little more help. The value I need to look for will be in a string like "3,4,6,8" so I think I need the Like conparison function, but I'm not sure of the syntax

The psudo code is this:

If instrI(String) contains a 4 or instrI(String) contains 5 display in Red else Black

Do you know what the syntax should be for IIF with a like conparison?|||

i think the same expression should work in a way similar to Like.

An iff() expression with Like operator would be in this case:

iif(("3,4,6,8" Like "*4*") or ("3,4,6,8" Like "*5*") ,"Red",Black")

* is for any 4 preceeding and followed with any number of characters

Somiya

IIF Statements

Hi All,
The below IIF statement is not working for me.
=IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
"Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
& " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
<> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
"Address")
Could you please let me know where I am going wrong. I tried several other
options such as writing custom code, switch, choose statements..I am
migrating the report from crystal reports to Reporting services. Instead of
the format in the above expression it was AddressLine1 in Crystal reports
that had worked fine. Also does anybody know of an alternative for the
NameFlip function of crystal rpts to use in Reporting svcs'
Thanks in advance,
RSAfter seeing the full syntax I think you have to use some more "iif's " in
between before "Address:" , if you can explain in plain language what exactly
you are trying to display. ie something like if the first conditions is true
then what and if false then what...
Amarnath
"RSub" wrote:
> Hi All,
> The below IIF statement is not working for me.
> =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> "Address")
> Could you please let me know where I am going wrong. I tried several other
> options such as writing custom code, switch, choose statements..I am
> migrating the report from crystal reports to Reporting services. Instead of
> the format in the above expression it was AddressLine1 in Crystal reports
> that had worked fine. Also does anybody know of an alternative for the
> NameFlip function of crystal rpts to use in Reporting svcs'
> Thanks in advance,
> RS|||Hi Amarnath,
My report uses a SQL query which is very complex and it has joins from
several diff tables. I was trying to add a calculated field(embedded) to the
data source and I need that to display the address which is addressline1, 2,
city state, zip etc based on some criteria which is the first part of the IIF
statement. I removed the variable Address and the OR and it is working fine.
The latter false part of the statement needs to remove US if it finds it in
the address and not display in the report. I'm trying to make that work now.
Thanks,
Roopa
"Amarnath" wrote:
> After seeing the full syntax I think you have to use some more "iif's " in
> between before "Address:" , if you can explain in plain language what exactly
> you are trying to display. ie something like if the first conditions is true
> then what and if false then what...
> Amarnath
>
> "RSub" wrote:
> > Hi All,
> > The below IIF statement is not working for me.
> > =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> > "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> > Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> > Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> > Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> > & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> > <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> > "Address")
> >
> > Could you please let me know where I am going wrong. I tried several other
> > options such as writing custom code, switch, choose statements..I am
> > migrating the report from crystal reports to Reporting services. Instead of
> > the format in the above expression it was AddressLine1 in Crystal reports
> > that had worked fine. Also does anybody know of an alternative for the
> > NameFlip function of crystal rpts to use in Reporting svcs'
> >
> > Thanks in advance,
> > RS|||ok, so infact you can nest the iif as well, to get the desired results.
Amarnath
"RSub" wrote:
> Hi Amarnath,
> My report uses a SQL query which is very complex and it has joins from
> several diff tables. I was trying to add a calculated field(embedded) to the
> data source and I need that to display the address which is addressline1, 2,
> city state, zip etc based on some criteria which is the first part of the IIF
> statement. I removed the variable Address and the OR and it is working fine.
> The latter false part of the statement needs to remove US if it finds it in
> the address and not display in the report. I'm trying to make that work now.
> Thanks,
> Roopa
>
> "Amarnath" wrote:
> > After seeing the full syntax I think you have to use some more "iif's " in
> > between before "Address:" , if you can explain in plain language what exactly
> > you are trying to display. ie something like if the first conditions is true
> > then what and if false then what...
> >
> > Amarnath
> >
> >
> > "RSub" wrote:
> >
> > > Hi All,
> > > The below IIF statement is not working for me.
> > > =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> > > "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> > > Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> > > Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> > > Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> > > & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> > > <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> > > "Address")
> > >
> > > Could you please let me know where I am going wrong. I tried several other
> > > options such as writing custom code, switch, choose statements..I am
> > > migrating the report from crystal reports to Reporting services. Instead of
> > > the format in the above expression it was AddressLine1 in Crystal reports
> > > that had worked fine. Also does anybody know of an alternative for the
> > > NameFlip function of crystal rpts to use in Reporting svcs'
> > >
> > > Thanks in advance,
> > > RS|||Your first problem is that the IIf currently contains four parameters:
1: Trim(Fields!BillType.Value) = "IN" AND (Fields!User9.Value)= 1
2: "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value &
" " & Fields!City.Value & " " & Fields!State.Value & " " & Fields!
Zip.Value & " " & Fields!Country.Value) OR Format(Fields!Addr1_2.Value
& " " & Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " &
Fields!State_2.Value & " " & Fields!Zip_2.Value & " " & Fields!
Country_2.Value)
3: Trim("Address") <> "US" and Trim("Address") <> "USA" and
Trim("Address") <> "United States"
4: "Address"
Second, parameter 2 is altogether meaningless for several reasons:
* "Address:" = Format(... is testing if the result of your format
statement matches the string "Address:", which it almost certainly
won't.
* Format() takes two parameters, the object and the format type, and
you only pass one parameter each time.
* Format() is generally used to convert numbers, dates, etc to a
string: for example, Format(1.5, "C") returns $1.50 in the US. You
probably don't even need it for the addresses you're putting together.
* OR operates on two boolean values. Though you have one boolean value
from the "Address:" = Format(... comparison (by accident, I suspect),
I don't see what you're hoping to accomplish with the statement.
And third, parameter 3 will always return true - Trim("Address") will
always return "Address" which will never match the variations on "US".
On Apr 23, 5:42 pm, RSub <R...@.discussions.microsoft.com> wrote:
> Hi All,
> The below IIF statement is not working for me.
> =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> "Address")
> Could you please let me know where I am going wrong. I tried several other
> options such as writing custom code, switch, choose statements..I am
> migrating the report from crystal reports to Reporting services. Instead of
> the format in the above expression it was AddressLine1 in Crystal reports
> that had worked fine. Also does anybody know of an alternative for the
> NameFlip function of crystal rpts to use in Reporting svcs'
> Thanks in advance,
> RS

IIF Statements

Hi All,

I have a normal IIF statement that controls what text i see in a text box depending on the returned value from the database. This is fine and all is working well.

My question is: Is it possible to have say the first line of the text in bold and a different size to that of the first bit of data?

=IIF(Fields!Code1.Value = "Developed","Developed: " & First(Fields!DevelopedText.Value, "ResourceTexts"),Fields!Code1.Value)

My example above shows this but what i want is the word Developed: to be bold and a different sizer to what follows. is this possible? I'm thinking maybe i have to insert a bold tag maybe?

Any help would be greatly appreciated.

Ta

Dave

No, different formats in the control is not supported. RTF support will be eventually added in later versions.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||http://www.sqlservercentral.com/columnists/bknight/reportingservicesconditionalformatting.asp

IIF Statement with Date checking

Hello, I am using the following to try to find date ranges and do some work.
Here is the statement
=iif(fields!mbr_join_date.value = "7/01/2003", mbt_code, "Not Grandfathered")
I have looked at the query result and I see alot of dates that are this date
but it's not putting the MBT_Code in for the value. Anyone have any
suggestions. Should I convert the Date in the Query to a different format
currently it shows as yyyy-mm-dd 00:00:00 should I trim off the time or
convert it to a string value?
Thank you in advanceScrocker,
I'm not 100% sure if your doing this in the SQL or in a cell on the
report. If its in the report you need to write it like:
iif(fields!mbr_join_date.value = "7/01/2003", FIELDS!mbt_cod.VALUE, "Not
Grandfathered")
You can't reference the field name in a control without usin gthe FIELDS!
collection.
If its in your SQL, well, IIF's dont' work, so I'm thinking your not doing
it, but if you are then use a CASE statement
CASE WHEN mbr_join_date = '7/01/2003' THEN mbr_code ELSE 'Not Grandfathered'
END as mbr_code
Michael C
"scrocker" wrote:
> Hello, I am using the following to try to find date ranges and do some work.
> Here is the statement
> =iif(fields!mbr_join_date.value = "7/01/2003", mbt_code, "Not Grandfathered")
> I have looked at the query result and I see alot of dates that are this date
> but it's not putting the MBT_Code in for the value. Anyone have any
> suggestions. Should I convert the Date in the Query to a different format
> currently it shows as yyyy-mm-dd 00:00:00 should I trim off the time or
> convert it to a string value?
> Thank you in advance
>|||Thanks Michael, I will give that a shot, Yes it's in the report not in SQL
Michael C wrote:
>Scrocker,
> I'm not 100% sure if your doing this in the SQL or in a cell on the
>report. If its in the report you need to write it like:
>iif(fields!mbr_join_date.value = "7/01/2003", FIELDS!mbt_cod.VALUE, "Not
>Grandfathered")
>You can't reference the field name in a control without usin gthe FIELDS!
>collection.
>If its in your SQL, well, IIF's dont' work, so I'm thinking your not doing
>it, but if you are then use a CASE statement
>CASE WHEN mbr_join_date = '7/01/2003' THEN mbr_code ELSE 'Not Grandfathered'
>END as mbr_code
>Michael C
>> Hello, I am using the following to try to find date ranges and do some work.
>> Here is the statement
>[quoted text clipped - 7 lines]
>> convert it to a string value?
>> Thank you in advance|||You might try:
=iif(fields!mbr_join_date.value < "7/02/2003", mbt_code, "Not
Grandfathered")
Adding one day to the date value - you might have to specify the date
as
"2003-07-02 00:00:00" (yyyy-mm-dd 00:00:00 format)
to ensure you are setting the date/time to the very start of the
following day.

IIF statement issue

Here is the iif statement I have in the report:
=iif ((Sum(Fields!EXTENDED_MARGIN.Value) = 0) or
(SUM(Fields!TY_QTY.Value)=0), 0,
SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value))
If I put in just the basic iif statment evaluation returning a 1 for true
and a 2 for false, it works fine. If I put in the expression I have for
false it will work fine unless one of the fields is a 0 and the other has a
number, then I get an error (which is understandable). I put them together
and I get the same thing as if I just put the false expression in, an error
if one number is a 0 and the other is an actual number.
Can anyone shed some light on this please?Not sure if there is another work around, however, I have run into this
problem many times and have solved it in several different ways. The probelm
that I see is that the IIF statement is calculating both the true and false
parts of the statement. Thus, you have a divide bt zero whenever
Fields!TY_Qty.Value = 0 even though you are trying to trap this condition in
the first part of the expression. Note that testing for
Fields!Extended_margin.value = 0 does not need to be done.
The first way to applies if you are using a stored proc to return the
dataset. If this is the case then you can in many cases return a field that
is already populated with
SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value);.taking care to
handle the division by zero potential in your TSQL code.
The other way that I have handled the problem is by writing a procedure that
takes the two numbers and returns zero if SUM(Fields!TY_QTY.Value) = 0 or
else SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value). Then instead
of using the IIF statement in an expression you call you procedure like this
=Code.MyProcedure( SUM(Fields!EXTENDED_MARGIN.Value),
SUM(Fields!TY_QTY.Value)).
Like I said, there may be other solutions but perhaps this will help.
"TBraun" wrote:
> Here is the iif statement I have in the report:
> =iif ((Sum(Fields!EXTENDED_MARGIN.Value) = 0) or
> (SUM(Fields!TY_QTY.Value)=0), 0,
> SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value))
> If I put in just the basic iif statment evaluation returning a 1 for true
> and a 2 for false, it works fine. If I put in the expression I have for
> false it will work fine unless one of the fields is a 0 and the other has a
> number, then I get an error (which is understandable). I put them together
> and I get the same thing as if I just put the false expression in, an error
> if one number is a 0 and the other is an actual number.
> Can anyone shed some light on this please?|||Mark,
Thanks. I used your second suggestion and wrote a custom function and that
worked just fine. Appreciate the help!
"B. Mark McKinney" wrote:
> Not sure if there is another work around, however, I have run into this
> problem many times and have solved it in several different ways. The probelm
> that I see is that the IIF statement is calculating both the true and false
> parts of the statement. Thus, you have a divide bt zero whenever
> Fields!TY_Qty.Value = 0 even though you are trying to trap this condition in
> the first part of the expression. Note that testing for
> Fields!Extended_margin.value = 0 does not need to be done.
> The first way to applies if you are using a stored proc to return the
> dataset. If this is the case then you can in many cases return a field that
> is already populated with
> SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value);.taking care to
> handle the division by zero potential in your TSQL code.
> The other way that I have handled the problem is by writing a procedure that
> takes the two numbers and returns zero if SUM(Fields!TY_QTY.Value) = 0 or
> else SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value). Then instead
> of using the IIF statement in an expression you call you procedure like this
> =Code.MyProcedure( SUM(Fields!EXTENDED_MARGIN.Value),
> SUM(Fields!TY_QTY.Value)).
> Like I said, there may be other solutions but perhaps this will help.
> "TBraun" wrote:
> > Here is the iif statement I have in the report:
> >
> > =iif ((Sum(Fields!EXTENDED_MARGIN.Value) = 0) or
> > (SUM(Fields!TY_QTY.Value)=0), 0,
> > SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value))
> >
> > If I put in just the basic iif statment evaluation returning a 1 for true
> > and a 2 for false, it works fine. If I put in the expression I have for
> > false it will work fine unless one of the fields is a 0 and the other has a
> > number, then I get an error (which is understandable). I put them together
> > and I get the same thing as if I just put the false expression in, an error
> > if one number is a 0 and the other is an actual number.
> >
> > Can anyone shed some light on this please?

iif problem brings back #Error

I'm pulling a "Number (8)" data type field for a date (formatted YYYYMMDD) from a Oracle 9i database. When the value is inserted into the database it is set a zero (A non-null database for the most part), otherwise it is for example 20061224. If the date field has a value the date is displayed correctly, but if it is zero then I get the "#Error" message.

Here is one of many iif expressions I've tried.

=iif(Len(CStr(Fields!DTE_MAILED.Value)) = 8,

((CStr(Fields!DTE_MAILED.Value)).Substring(4,2) + "/" + Right(CStr(Fields!DTE_MAILED.Value), 2) + "/" + Left(CStr(Fields!DTE_MAILED.Value), 4))

,

Nothing)

Now I've returned the value with out any formatting done to the string, and it will return "0"(zero) or a number. I've returned the lengths of the returning value and it comes back "1" or "8". I read a lot of previous posts and I thought at first that it was because I was trying to do a substring function on the zero value getting a index error. So I've changed the iif test condition many different ways with no prevail. I read a previous post where someone ended up doing his work in his SQL, but I would like to find out how to do this in the report. This is a simple expression, so I feel like there is something obvious I don't know maybe something with the format mask.

All help will be appreciated!

hi nwyork,

i don't have Oracle db so i can't try.

but as far as the error that i've encountered,

e.g. iif (condition, true exp, false exp)

you might want to put your true exp and false exp returning the same data types...

if your true exp has CStr then the false exp should put CStr

|||

Thanks for replying,

I thought that might be it so I tried returning (""), and after reading your post I tried using CStr function in the false part but I still get the error. I ran out of time so I used SubStr, Decode, and Length functions in the SQL.

|||

Hi nwyork

The first problem im seeing is in the boolean check.
You are using 2 expressions on the field.....(Cstr and Len)
If the field containes a null value there will be problems as
Len(Null) = #error

Why not try restructuring your iif statement as follows:

=iif( Fields!DTE_MAILED.Value = Nothing
,0
,((CStr(Fields!DTE_MAILED.Value)).Substring(4,2) + "/" + Right(CStr(Fields!DTE_MAILED.Value), 2) + "/" + Left(CStr(Fields!DTE_MAILED.Value), 4)))

I'm not sure if this is specific to your datasource as i've never used Oracle before but it should still do
the trick. You can even add a second iif in the false part of the expression to make sure that the length is 8
,because as soon as the expression hits false part you know that there is no
null's and you should not get an error when trying to use expressions on the the field value.

If I was unclear in any way, be sure to point it out.

It may be 2 late now but it may help when a similar problem pops up in the future ;P

G

IIF Issues

I am trying to format dates that are in string format. When I use:
=IIF(Fields!MyDate.Value = "","EMPTY","STRING")
I get EMPTY and STRING right where they should appear. When I use:
=IIF(Fields!MyDate.Value = "","",Format((Convert.ToDateTime(Fields!MyDate.Value)), "MMM. d, yyyy"))
I get #Error for all of the empty strings and correctly formatted dates for
everything else... what am I doing wrong?Try inserting spaces( i used 5 spaces)...I had the same problem, it does not
seem to like "".
~aparna
"MER78" wrote:
> I am trying to format dates that are in string format. When I use:
> =IIF(Fields!MyDate.Value = "","EMPTY","STRING")
> I get EMPTY and STRING right where they should appear. When I use:
> =IIF(Fields!MyDate.Value => "","",Format((Convert.ToDateTime(Fields!MyDate.Value)), "MMM. d, yyyy"))
> I get #Error for all of the empty strings and correctly formatted dates for
> everything else... what am I doing wrong?|||IIF always evaluates two sides of an expression, which is where the error
occurs. You can create a custom code function that has a regular If
statement, and call it like so:
=code.MyFunctionName(Fields!MyDate.Value)
Right-click on the report background and click Properties to get to the code
area.
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"MER78" <MER78@.discussions.microsoft.com> wrote in message
news:02AA454F-2678-4E0E-A910-F639933B6788@.microsoft.com...
>I am trying to format dates that are in string format. When I use:
> =IIF(Fields!MyDate.Value = "","EMPTY","STRING")
> I get EMPTY and STRING right where they should appear. When I use:
> =IIF(Fields!MyDate.Value => "","",Format((Convert.ToDateTime(Fields!MyDate.Value)), "MMM. d, yyyy"))
> I get #Error for all of the empty strings and correctly formatted dates
> for
> everything else... what am I doing wrong?|||YUCK... that's so much more of a hassle... what's the point of creating
branching code if it executes all cases?
"Jeff A. Stucker" wrote:
> IIF always evaluates two sides of an expression, which is where the error
> occurs. You can create a custom code function that has a regular If
> statement, and call it like so:
> =code.MyFunctionName(Fields!MyDate.Value)
> Right-click on the report background and click Properties to get to the code
> area.
> Cheers,
> '(' Jeff A. Stucker|||I've had success using = nothing
Hope this helps.
"MER78" wrote:
> YUCK... that's so much more of a hassle... what's the point of creating
> branching code if it executes all cases?
> "Jeff A. Stucker" wrote:
> > IIF always evaluates two sides of an expression, which is where the error
> > occurs. You can create a custom code function that has a regular If
> > statement, and call it like so:
> >
> > =code.MyFunctionName(Fields!MyDate.Value)
> >
> > Right-click on the report background and click Properties to get to the code
> > area.
> >
> > Cheers,
> >
> > '(' Jeff A. Stucker|||I've had success using = nothing
hth
"MER78" wrote:
> YUCK... that's so much more of a hassle... what's the point of creating
> branching code if it executes all cases?
> "Jeff A. Stucker" wrote:
> > IIF always evaluates two sides of an expression, which is where the error
> > occurs. You can create a custom code function that has a regular If
> > statement, and call it like so:
> >
> > =code.MyFunctionName(Fields!MyDate.Value)
> >
> > Right-click on the report background and click Properties to get to the code
> > area.
> >
> > Cheers,
> >
> > '(' Jeff A. Stucker|||I tend to agree with you. But that's how IIF was designed eons ago for
VB/VBScript, not really for branching, but inline evaluation and population
of data.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"MER78" <MER78@.discussions.microsoft.com> wrote in message
news:9925D007-E1CD-47C6-8A48-FE06F826CDD1@.microsoft.com...
> YUCK... that's so much more of a hassle... what's the point of creating
> branching code if it executes all cases?
> "Jeff A. Stucker" wrote:
>> IIF always evaluates two sides of an expression, which is where the error
>> occurs. You can create a custom code function that has a regular If
>> statement, and call it like so:
>> =code.MyFunctionName(Fields!MyDate.Value)
>> Right-click on the report background and click Properties to get to the
>> code
>> area.
>> Cheers,
>> '(' Jeff A. Stucker

iif help please...

I want textbox2 value to NOT show if textbox1 has the word "***mail***"
<--just like that between the quotes.
Any help is appreciated.
Thanks,
TrintTrint,
Right click on textbox2, go to select properties, click the advanced button,
then select the visibility tab. click the 'Expression' radio button, then
put this text in the text box for the expression:
=IIF(Fields!FieldForTextBox1.Value.ToString() = "***mail***", False, True)
Fields!FieldForTextBox1 is whatever field from your datasource is populating
textbox1.
That sound do it for you.
"trint" wrote:
> I want textbox2 value to NOT show if textbox1 has the word "***mail***"
> <--just like that between the quotes.
> Any help is appreciated.
> Thanks,
> Trint
>|||That is it Jeff!
Thanks!
Trint|||no problem.
"trint" wrote:
> That is it Jeff!
> Thanks!
> Trint
>

Iif Functions

In a table im populating I have to leave a data cell blank depending on the
value retrieved.
eg. if the breed type is deer leave the cell blank.
Do i use an Iif function for this or not. And if so how do I write it.
Total sqlnewbie here.
I have this so far....Iif(Fields!Breed.Value = Deer......... not sure
what should come next.Put it in the Visibility/hidden property: ....Iif(Fields!Breed.Value ="Deer", True, False)
"Nat Johnson" wrote:
> In a table im populating I have to leave a data cell blank depending on the
> value retrieved.
> eg. if the breed type is deer leave the cell blank.
> Do i use an Iif function for this or not. And if so how do I write it.
> Total sqlnewbie here.
> I have this so far....Iif(Fields!Breed.Value = Deer......... not sure
> what should come next.