Problem with an IIF Statement

In my group footer I build a string based on the number of rows in that
group plus the field the group was based on.
But if the group was based on a an empty string I get an #error in the
textbox.
So I wrapped the code with an IIF statement. If the field is empty do
nothing otherwise build the string.
= IIF(Fields!AdjustmentCode.Value = "","No Adjustment
Code",countrows("grpAdjustmentDesc") & " Records for Adjustment Code
Description: " & Fields!AdjustmentCode.Value.trim & " - " &
Fields!AdjustmentDescription.Value.tolower)FYI this is the error: The value expression for the textbox
'textbox35' contains an error: Object variable or With block
variable not set.|||VB.Net evaluates all parts of the IIF which is why you get the error. It
does not just evaluate the true portion. You could use code behind report to
do this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128709777.373098.129000@.g49g2000cwa.googlegroups.com...
> In my group footer I build a string based on the number of rows in that
> group plus the field the group was based on.
> But if the group was based on a an empty string I get an #error in the
> textbox.
> So I wrapped the code with an IIF statement. If the field is empty do
> nothing otherwise build the string.
> = IIF(Fields!AdjustmentCode.Value = "","No Adjustment
> Code",countrows("grpAdjustmentDesc") & " Records for Adjustment Code
> Description: " & Fields!AdjustmentCode.Value.trim & " - " &
> Fields!AdjustmentDescription.Value.tolower)
>|||Can you point me in some direction for a Code Behind example. I can't
find anything yet.
Thanks.|||Look in books on line for the phrase code block
They have an example with that. I tend to develop my code in vb.net, test it
and then copy it over.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128712212.284978.143180@.z14g2000cwz.googlegroups.com...
> Can you point me in some direction for a Code Behind example. I can't
> find anything yet.
> Thanks.
>|||All i see is XML. This is going to be a lot of work to just handle the
fact the the Rpt can't handle a blank value.
So your saying I can put vb code in this.
<Textbox Name="textbox35">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>25</ZIndex>
<rd:DefaultName>textbox35</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= countrows("grpAdjustmentDesc") &
" Records for Adjustment Code Description: " &
Fields!AdjustmentCode.Value.trim & " - " &
Fields!AdjustmentDescription.Value.tolower</Value>
</Textbox>|||Can you post the hyperlink from the RSBook online address bar to where
the sample is.|||I put this in the code window
<Code>
Public Function test() As String
Dim strTest As String
strTest = "BLAH BLAH BLAH"
Return strTest
End Function
</Code>
I put this in a textbox
= code.test()
d:\phfxclaims\Pending Claims Detail.rdl There is an error on line 0 of
custom code: [BC32035] Attribute specifier is not a complete statement.
Use a line continuation to apply the attribute to the following
statement.
And this is my error|||You should not be having to modify the rdl at all (which is what you are
doing).
Search Books Online for the phrase: writing custom code
Also these two links:
ms-help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_expressions_v1_84f9.htm
ms-help://MS.RSBOL80.1033/RShowto/htm/hrs_designer_v1_1nfp.htm
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128719715.084263.60890@.g43g2000cwa.googlegroups.com...
>I put this in the code window
> <Code>
> Public Function test() As String
> Dim strTest As String
> strTest = "BLAH BLAH BLAH"
> Return strTest
> End Function
> </Code>
>
> I put this in a textbox
> = code.test()
>
> d:\phfxclaims\Pending Claims Detail.rdl There is an error on line 0 of
> custom code: [BC32035] Attribute specifier is not a complete statement.
> Use a line continuation to apply the attribute to the following
> statement.
> And this is my error
>|||Thanks for the links. I finally got it to work
I had cut and pasted the code from this article
http://www.15seconds.com/issue/041110.htm and I had the tags in there
which were getting placed in the XML definition
Thanks for the quick responses

Problem with a query using MAX(ID)

I have a query regarding and I think I need to use max ID to get round it.I am creating a query and I need to get the Home Telephone number (from table 3).The problem is that there may be more than one home telephone nuber so I want to get the lastest one (highest ID).

I need to display all the person id from table along with their home no even if it is null.I’m not sure how to get round it whether I do a subquery joining the tables or whether I do a select case.

Please help

Table 1

Person ID

111

112

113

114

207

Table 2

PersonIdID

1110122

1110123

1120124

1130125

2070126

2070127

Table 3

IDTel_noType

01220125 23223Hone

01230122 43533Home

01240122 444111Mobile

0125077747474Mobile

012601222747474Home

012701232484848Home

Result

Person IDHome No

1110122 43533

112NULL

113NULL

114NULL

20701232 484848

use the following query...

Code Snippet

Create Table #table1 (

[PersonId] Int

);

Insert Into #table1 Values('111');

Insert Into #table1 Values('112');

Insert Into #table1 Values('113');

Insert Into #table1 Values('114');

Insert Into #table1 Values('207');

Create Table #table2 (

[PersonId] int ,

[ID] int

);

Insert Into #table2 Values('111','0122');

Insert Into #table2 Values('111','0123');

Insert Into #table2 Values('112','0124');

Insert Into #table2 Values('113','0125');

Insert Into #table2 Values('207','0126');

Insert Into #table2 Values('207','0127');

Create Table #table3 (

[ID] int ,

[Tel_no] Varchar(100) ,

[Type] Varchar(100)

);

Insert Into #table3 Values('0122','0125 23223','Hone');

Insert Into #table3 Values('0123','0122 43533','Home');

Insert Into #table3 Values('0124','0122 444111','Mobile');

Insert Into #table3 Values('0125','0777 47474','Mobile');

Insert Into #table3 Values('0126','01222 747474','Home');

Insert Into #table3 Values('0127','01232 484848','Home');

Select

X.PersonId

,Tel_No

,Y.ID

,Type Into #Temp

From

#Table1 X

Left Outer Join #Table2 Y On X.Personid=Y.PersonID

Left Outer Join #Table3 Z on Z.Id=Y.ID

Select

A.PersonId

,B.Tel_No as Home_No

from

(Select PersonId, Max(Case When Type='Home' Then Id Else NULL END) ID

From #Temp Group By PersonId) A

Left Outer Join #Temp B On A.PersonId=B.PersonId And A.id=B.ID

Select

A.PersonId

,B.Tel_No as Mobile_No

from

(Select PersonId, Max(Case When Type='Mobile' Then Id Else NULL END) ID

From #Temp Group By PersonId) A

Left Outer Join #Temp B On A.PersonId=B.PersonId And A.id=B.ID

Select Home.PersonId,Home_No,Mobile_No from

(

Select

A.PersonId

,B.Tel_No as Home_No

from

(Select PersonId, Max(Case When Type='Home' Then Id Else NULL END) ID

From #Temp Group By PersonId) A

Left Outer Join #Temp B On A.PersonId=B.PersonId And A.id=B.ID

) as Home

Join

(

Select

A.PersonId

,B.Tel_No as Mobile_No

from

(Select PersonId, Max(Case When Type='Mobile' Then Id Else NULL END) ID

From #Temp Group By PersonId) A

Left Outer Join #Temp B On A.PersonId=B.PersonId And A.id=B.ID

) as Mobile

On Home.PersonId=Mobile.PersonId

|||Here are two versions, one for SQL Server 2005 and later, and one for SQL Server 2000 and earlier:

-- Requires SQL Server 2005 or later
with TPRanked as (
select
T1.PersonId,
T3.Tel_no,
rank() over (
partition by T1.PersonId
order by T2.ID desc
) as rk
from #table1 as T1 left outer join #table2 as T2
on T2.PersonId = T1.PersonId
left outer join #table3 as T3
on T3.ID = T2.ID
and T3.Type = 'Home'
)
select
PersonId,
Tel_no
from TPRanked
where rk = 1

-- SQL Server 2000 or 7.0
select
T1.PersonId,
T3.Tel_no
from #table1 as T1 left outer join #table2 as T2
on T2.PersonId = T1.PersonId
left outer join #table3 as T3
on T3.ID = T2.ID
and T3.Type = 'Home'
where not exists (
select *
from #table1 as T1a left outer join #table2 as T2a
on T2a.PersonId = T1a.PersonId
left outer join #table3 as T3a
on T3a.ID = T2a.ID
and T3a.Type = 'Home'
where T1a.PersonId = T1.PersonId
and T2a.ID > T2.ID
)

Steve Kass
Drew University
http://www.stevekass.com
|||I have just tried the above query in SQL but the problem is that it will show a NULL value if a mobile no has a higher ID than a Home no.|||

I need to do the query without creating any temporary tables.

|||

Ok.. here it is..

Note: If you use temp table then you can increase the performance

Code Snippet

Create Table #table1 (

[PersonId] Int

);

Insert Into #table1 Values('111');

Insert Into #table1 Values('112');

Insert Into #table1 Values('113');

Insert Into #table1 Values('114');

Insert Into #table1 Values('207');

Create Table #table2 (

[PersonId] int ,

[ID] int

);

Insert Into #table2 Values('111','0122');

Insert Into #table2 Values('111','0123');

Insert Into #table2 Values('112','0124');

Insert Into #table2 Values('113','0125');

Insert Into #table2 Values('207','0126');

Insert Into #table2 Values('207','0127');

Create Table #table3 (

[ID] int ,

[Tel_no] Varchar(100) ,

[Type] Varchar(100)

);

Insert Into #table3 Values('0122','0125 23223','Hone');

Insert Into #table3 Values('0123','0122 43533','Home');

Insert Into #table3 Values('0124','0122 444111','Mobile');

Insert Into #table3 Values('0125','0777 47474','Mobile');

Insert Into #table3 Values('0126','01222 747474','Home');

Insert Into #table3 Values('0127','01232 484848','Home');

Select

A.PersonId

,B.Tel_No as Home_No

from

(Select PersonId, Max(Case When Type='Home' Then Id Else NULL END) ID

From (Select

X.PersonId

,Tel_No

,Y.ID

,Type

From

#Table1 X

Left Outer Join #Table2 Y On X.Personid=Y.PersonID

Left Outer Join #Table3 Z on Z.Id=Y.ID) as D Group By PersonId) A

Left Outer Join (Select

X.PersonId

,Tel_No

,Y.ID

,Type

From

#Table1 X

Left Outer Join #Table2 Y On X.Personid=Y.PersonID

Left Outer Join #Table3 Z on Z.Id=Y.ID) B On A.PersonId=B.PersonId And A.id=B.ID

|||I think I have a corrected query for SQL Server 2005. I changed the ranking criteria so that 'Home' numbers are always ranked highest. I'm working on the 2000 query. Obviously this needs careful testing!

with TPRanked as (
select
T1.PersonId, T2.ID,
T3.Tel_no,
rank() over (
partition by T1.PersonId
order by
case when T3.Type = 'Home' then 0 else 1 end,
T2.ID desc
) as rk
from #table1 as T1 left outer join #table2 as T2
on T2.PersonId = T1.PersonId
left outer join #table3 as T3
on T3.ID = T2.ID
and T3.Type = 'Home'
)
select
PersonId,
Tel_no
from TPRanked
where rk = 1

SK
|||Here's a correction for SQL 2000 along with another query that takes a different approach. The different approach doesn't generalize very well, but it's much more concise.

-- Quick and dirty
select
T1.PersonId,
(
select top (1) Tel_no
from #table2 as T2
join #table3 as T3
on T3.ID = T2.ID
and T3.Type = 'Home'
where T2.PersonId = T1.PersonId
order by T3.ID desc
) as Tel_no
from #table1 as T1

-- Second try, first approach
select
T1.PersonId,
T3.Tel_no
from #table1 as T1 left outer join #table2 as T2
on T2.PersonId = T1.PersonId
left outer join #table3 as T3
on T3.ID = T2.ID
and T3.Type = 'Home'

where not exists (
select *
from #table1 as T1a left outer join #table2 as T2a
on T2a.PersonId = T1a.PersonId
left outer join #table3 as T3a
on T3a.ID = T2a.ID
and T3a.Type = 'Home'
where T1a.PersonId = T1.PersonId
and (
(T3a.Type = 'Home' and (T3.Type is null or T2a.ID > T2.ID)) or
(T3.Type is null and T2a.ID > T2.ID)
)
)

SK

Problem wit sp_execute SQL

I have to make a large number of updates (about 29k) so I generated teh update statements into a table and am trying to sue sp_executesql to run them. Here is my code:

Declare @.SQLState NVARCHAR(500)

Declare Code Cursor
for
select SQLState from updates
open Code
FETCH NEXT FROM Code
into @.SQLState
While @.@.fetch_Status = 0
Begin
Exec sp_executesql @.SQLState

FETCH NEXT FROM Code
END

CLOSE Code
DEALLOCATE Code

IT appears to run succesfully, but the updates never happen - I get the following results for each update line:

UPDATE REEmployeeEvent SET UpdatedByEmployeeID= '00013' Where UpdatedByEmployeeID='00279'

(1 row(s) affected)

(0 row(s) affected)

Any ideas what I am doing wrong?

BTW - If I run the statements manually, they do work.

Thanks for any help!is there any particular reason why you want to use such a non-standard method to execute a script?

Why not execute dump them to a file, and execute the file as a single batch? At least that way you could visually inspect the scripts for correctness.

What you are trying to do here seems risky at best.

Problem when transfering an important number of records.

Hi,
I have MSSQl 2000 , SP3 with WIn2000
when I transfer from one table to another, 115852 records, le server hangs.
I tried with 70000 records and it worked. even with 90000 records.
With more than 90000 records, the server hangs.
Any idea ? It might be a bug in MSSQLsvr ? any known bugs ?
Thanks for your help
OlivierOliver,
We will need more information to help guide you.
How are you transfering the data? Insert, select into, DTS, bcp, etc.
Describe what you mean by hang.
Can you connect?
If you can connect what is showing for the spid when excuting an sp_who2
active?
Is the sqlserver process consuming cpu and disk?
Is there anything in the SQL Error log?
"oLiVieR CheNeSoN" <ocheneson@.hotmail.com> wrote in message
news:ub65iUySFHA.336@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I have MSSQl 2000 , SP3 with WIn2000
> when I transfer from one table to another, 115852 records, le server
> hangs.
> I tried with 70000 records and it worked. even with 90000 records.
> With more than 90000 records, the server hangs.
> Any idea ? It might be a bug in MSSQLsvr ? any known bugs ?
> Thanks for your help
> Olivier
>
>

Problem when transfering an important number of records.

Hi,
I have MSSQl 2000 , SP3 with WIn2000
when I transfer from one table to another, 115852 records, le server hangs.
I tried with 70000 records and it worked. even with 90000 records.
With more than 90000 records, the server hangs.
Any idea ? It might be a bug in MSSQLsvr ? any known bugs ?
Thanks for your help
Olivier
Oliver,
We will need more information to help guide you.
How are you transfering the data? Insert, select into, DTS, bcp, etc.
Describe what you mean by hang.
Can you connect?
If you can connect what is showing for the spid when excuting an sp_who2
active?
Is the sqlserver process consuming cpu and disk?
Is there anything in the SQL Error log?
"oLiVieR CheNeSoN" <ocheneson@.hotmail.com> wrote in message
news:ub65iUySFHA.336@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I have MSSQl 2000 , SP3 with WIn2000
> when I transfer from one table to another, 115852 records, le server
> hangs.
> I tried with 70000 records and it worked. even with 90000 records.
> With more than 90000 records, the server hangs.
> Any idea ? It might be a bug in MSSQLsvr ? any known bugs ?
> Thanks for your help
> Olivier
>
>

problem using LIKE in UNICODE characters...

I have a table with nVarchar column.

If i do a search like this
----------------------
SELECT ID, Book, Chapter, Number, Amharic, English
FROM tbl_test
WHERE (Amharic LIKE '%???%')
----------------------

it doesn't return anything but if i add 'N' after LIKE as
----------------------
SELECT ID, Book, Chapter, Number, Amharic, English
FROM tbl_test
WHERE (Amharic LIKE N'%???%')
----------------------

It returns the whole table without filtering.

Can someone help me with this?

What about this:

WHERE RTRIM(Amharic) LIKE '%???%'

Problem using "AS" to add 2 numbers

i need to add 2 results from a query to get this string: 2.2006
(that represents the number of the w, and the year)
i have a table that holds these 2 columns ("w" , "year") as integers
(i can't change it to string)
i want to return them as one result, but when i do this:
SELECT w + year AS [result w]
the result i get is the w added to the year (since they both are
integers)
how do i get the result as an added string?
thanks!SELECT CONVERT(VARCHAR(2), w) + CONVERT(CHAR(4), year) AS [result w]
<friedman30@.gmail.com> wrote in message
news:1146748724.890049.324700@.y43g2000cwc.googlegroups.com...
>i need to add 2 results from a query to get this string: 2.2006
> (that represents the number of the w, and the year)
> i have a table that holds these 2 columns ("w" , "year") as integers
> (i can't change it to string)
> i want to return them as one result, but when i do this:
> SELECT w + year AS [result w]
> the result i get is the w added to the year (since they both are
> integers)
> how do i get the result as an added string?
> thanks!
>|||declare @.w int
declare @.year int
declare @.string varchar(16)
set @.w = 2
set @.year = 2006
select @.string = cast(@.w as varchar(2)) + '.' + cast (@.year as
char(4) )
print @.string|||thanks!
that really works

Problem updating SQL2005 From MSAccess Form

I have recently migrated from SQL2K to SQL2K5 and have a number of front ends in MSAccess Forms that worked fine with SQL2K but are not working with SQL2K5. I am able to connect and view the data in the SQL table using a file DSN but I am unable to update the records from the MSAccess form in SQL2K5. I have checked all of the permissions and have even given the Login dbo privs. I have also allowed remote access through the Surface Area Configuration Tool. What am I missing?

Thanks in advance for any assistance you can provide.

What are you using as the front end?, Access Project or Access mdb? If it's a Project I don't think they can update in SS2005

I've just completed a big Access/SQL Server 2005 project, leaving the front end as an mdb. There are a few quirks, you can't use Views only SP's and pass through queries, but it still works well.

|||I am using Access mdb. Sql tables are Linked tables in the Access mdb. Thanks for the advice. I'll ensure that there are no views involved.|||You should run one of your queries from the query design grid, try to make a change to the returned result set, if you can make an update , you know it's a forms based problem not permissions.|||Same result using the Design Grid. I am able to display the contents of the table but unable to update or append records from the grid. WHen I try a simple update query I get the error "Operation must use an updatable query".|||Problem solved. For some reason when I originally created the Linked Table I was not prompted to identify a unique key in the table for updating. Recreating the Linked Table with the unique fields identified solved the problem.