case statement in where clause

I would like to know that may I use case statement in where clause?
If not, are there any other solution to filter my records depends on my
field.
For example
MyType integer, MyDate datetime
MyType MyDate
1 03/24/2005
1 03/20/2005
2 03/24/2005
I want to show MyType one date before today like record 2,but I want to
everything for MyType 2
Are there any solution for this?
Any information is appreciated,
Souris,Try this
Select * from Tablename where id in (select max(id) from tablename
group by date)
Madhivanan|||You can use a case statement in a where clause, but you don't need to for
this example. If you just want to look for myDate on the previous day and
myType = 2 than you would do this:
select * from myTable
where MyDate = convert(varchar,dateadd(d,-1,getdate()),101)
and MyType = 2
If you are looking for ANY myDate prior to today and myType = 2 than:
select * from myTable
where MyDate < convert(varchar,getdate(),101)
and MyType = 2
"souris" wrote:

> I would like to know that may I use case statement in where clause?
> If not, are there any other solution to filter my records depends on my
> field.
> For example
> MyType integer, MyDate datetime
> MyType MyDate
> 1 03/24/2005
> 1 03/20/2005
> 2 03/24/2005
> I want to show MyType one date before today like record 2,but I want to
> everything for MyType 2
> Are there any solution for this?
> Any information is appreciated,
> Souris,
>
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
<<
No. This is because there is no such statement in SQL. There is a
CASE expression which returns a scalar value.
I want to
everything for MyType 2 <<
SELECT my_type, my_date
FROM Foobar
WHERE my_type = 2
OR my_date < CURRENT_TIMESTAMP;
Rows are not records, columns are not fields. You are still using
terminology from a procedural language with a file system and not
thinking in RDBMS yet.|||Thanks millions,
Souris,
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1111763824.117387.95340@.o13g2000cwo.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are. Sample data is also a good idea, along with clear
> specifications.
>
> <<
> No. This is because there is no such statement in SQL. There is a
> CASE expression which returns a scalar value.
>
> I want to
> everything for MyType 2 <<
> SELECT my_type, my_date
> FROM Foobar
> WHERE my_type = 2
> OR my_date < CURRENT_TIMESTAMP;
> Rows are not records, columns are not fields. You are still using
> terminology from a procedural language with a file system and not
> thinking in RDBMS yet.
>

CASE count wierd join needed maybe? just one more field needed!

Right, I used your handy answers to get a solution that worked.. Your
last method was the one to use but i left it running for 3 hours and
it still hadn't returned any results! (and it's a stupidly high spec
server with pretty much just sql on there)

anyway.. so in SQL Server managment studio i found the "Views" section
(sorta like tempory tables i presume) and popped this into a new one
call kev_bin.

WITH Bin1 AS (SELECT ItemID, MAX(DateTimeCreated) AS MDate
FROM dbo.BinItem AS Bin1
GROUP BY ItemID)
SELECT Bin2.ItemID, Bin2.BinName
FROM dbo.BinItem AS Bin2 INNER JOIN
Bin1 AS Bin1 ON Bin2.ItemID = Bin1.ItemID
AND Bin2.DateTimeCreated = Bin1.MDate

I could then go back to my orignal SQL and use the following to grap
the data from the newly created table kev_bin above:

Select ... .. ...
kbin.BinName as location

Quote:

Originally Posted by

>From ...

...
dbo.kev_bin kbin
...
WHERE ..
item.ItemID = kbin.ItemID
....

and this runs in about 20 seconds :D

thanks again for all your help, i couldn't have done it without!(kevin@.nu-urbanmusic.co.uk) writes:

Quote:

Originally Posted by

anyway.. so in SQL Server managment studio i found the "Views" section
(sorta like tempory tables i presume) and popped this into a new one
call kev_bin.

Views are just a logical concept. Except for indexed view, they are not
materialised, and they are expanded so that the optimizer works with the
expanded query. That is, essentiall a macro.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Nov 6, 10:31 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

(ke...@.nu-urbanmusic.co.uk) writes:

Quote:

Originally Posted by

anyway.. so in SQL Server managment studio i found the "Views" section
(sorta like tempory tables i presume) and popped this into a new one
call kev_bin.

>
Views are just a logical concept. Except for indexed view, they are not
materialised, and they are expanded so that the optimizer works with the
expanded query. That is, essentiall a macro.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Hi Kev,

Glad I could help :)

Jsql

CASE - How to show groups with count(*) = 0 (zero) ?

I would like to show even the rows where number of records are zero (in the Case statement ).

I couldn't find the solution either in the microsoft newsgroup, dbforums or Google. I fear I am overlooking something very very basic!
Thanks in advance.
:)

SELECT case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end,
Count(*) 'Number of Invoices'

FROM AP

GROUP BY case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end
ORDER BY Min(Amount)
----------
Suppose these are the Amounts (99,50,75,201,230)
The CURRENT OUTPUT is
<= 100 3
>200 2

The REQUIRED OUTPUT is
<= 100 3
<= 200 0
> 200 2select 'Unknown', count(*)
from AP
where Amount IS NULL
UNION ALL
select '<= 100 ', count(*)
from AP
where Amount <= 100
UNION ALL
select '<=200', count(*)
from AP
where Amount <= 100 AND Amount > 100
UNION ALL
select '>200', count(*)
from AP
where Amount > 200|||correction:
where Amount <= 200 AND Amount > 100|||Thanks HanafiH.

Using union will cause a pass through the table each time. That will generate a performance overhead which would increase as the number of divisions increase. I am generating this CASE statement using dynamic SQL. The application can provide any value to the number of divisions.

I want to take advantage of the performance benefit by using CASE.

Is there a solution using CASE itself?

Thanks again in advance.
:)|||declare @.GroupingTable table
(GroupingValue varchar(10),
SortOrder int)

insert into @.GroupingTable (GroupingValue, SortOrder) values('Unknown', 1)
insert into @.GroupingTable (GroupingValue, SortOrder) values('<= 100 ', 2)
insert into @.GroupingTable (GroupingValue, SortOrder) values('<= 118 ', 3)
insert into @.GroupingTable (GroupingValue, SortOrder) values('> 200', 4)

select GroupingTable.GroupingValue
from
@.GroupingTable GroupingTable
left outer join
(SELECT case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end GroupingValue
Count(*) 'Number of Invoices'
FROM AP
GROUP BY case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end) SummaryData
on GroupingTable.GroupingValue = SummaryData.GroupingValue
order by GroupingTable.SortOrder

blindman|||Originally posted by HornOkPlease
Thanks HanafiH.

Using union will cause a pass through the table each time. That will generate a performance overhead which would increase as the number of divisions increase. I am generating this CASE statement using dynamic SQL. The application can provide any value to the number of divisions.

I want to take advantage of the performance benefit by using CASE.

Is there a solution using CASE itself?

Thanks again in advance.
:)

The CASE may well be efficient, but the GROUP BY and ORDER BY necessary to make use of it is not. UNION *ALL* is very efficient. On my system your original case query has a 0.0604 total query cost, my query runs at 0.0192 total query cost, which is roughly three times faster than the CASE case (as it were).

I shudder to think about what insertion into a temp will cost.|||try this:select count(*) as TotalNumber
, sum(case when amount is null
then 1 else 0 end) as Unknown
, sum(case when amount <= 100
then 1 else 0 end) as "<= 100"
, sum(case when amount <= 200
then 1 else 0 end) as "<= 200"
, sum(case when amount > 200
then 1 else 0 end) as "> 200"
from ap
you guys with the cpu timers, please let me know how this compares to the other solutions

rudy
http://r937.com/|||r937, I think you hit the nail on the head.

blindman|||Hats off to you blindman!
Thanks, HanafiH, i've taken note of your message too.

Regards,
HornOkPlease|||Originally posted by r937
try this:select count(*) as TotalNumber
, sum(case when amount is null
then 1 else 0 end) as Unknown
, sum(case when amount <= 100
then 1 else 0 end) as "<= 100"
, sum(case when amount <= 200
then 1 else 0 end) as "<= 200"
, sum(case when amount > 200
then 1 else 0 end) as "> 200"
from ap
you guys with the cpu timers, please let me know how this compares to the other solutions

rudy
http://r937.com/

0.0376|||Originally posted by blindman
declare @.GroupingTable table
(GroupingValue varchar(10),
SortOrder int)

insert into @.GroupingTable (GroupingValue, SortOrder) values('Unknown', 1)
insert into @.GroupingTable (GroupingValue, SortOrder) values('<= 100 ', 2)
insert into @.GroupingTable (GroupingValue, SortOrder) values('<= 118 ', 3)
insert into @.GroupingTable (GroupingValue, SortOrder) values('> 200', 4)

select GroupingTable.GroupingValue
from
@.GroupingTable GroupingTable
left outer join
(SELECT case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end GroupingValue
Count(*) 'Number of Invoices'
FROM AP
GROUP BY case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end) SummaryData
on GroupingTable.GroupingValue = SummaryData.GroupingValue
order by GroupingTable.SortOrder

blindman

0.1158 and the slowest solution proposed.|||I think r937 was being modest. You don't need to time these solutions to see that his is better. It's the solution I was trying to get to, but I guess I was one or two cups of coffee short this morning.

blindman

Cascading Parameters in Report Builder Reports!

Is it possible to create cascading parameters with in Report Builder? I have done this with Report Designer but i need a solution with Report Builder.

(I need the structure: First the user selects the state, afer the postpack the cirties of the state are listed in another dropdownlist for selection).

Thanks in advance

I need to know if it is possible. I have checked Report Builder and didn't find a way for it.

I am curios if i have missed something or if Report Builder doesn't support cascadign parameters.

cascading parameter help - solution

I a report that will have two parameters (Year and Account name), I'm having trouble setting up the second parameter to be filtered based on the value of the first parameter.

here is the second parameter with a value hard coded.(see bold text) this works fine when I run the report.

WITH MEMBER [Measures].[ParameterCaption] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,FILTER([CUSTOMER JOB].[ACCOUNT NAME].ALLMEMBERS,([Job Complete Date].[Year].&[2006],[Measures].[Billed Sales Amount])) ON ROWS FROM [DW PREP ARCHIVE]

When I try the following code where I have added in the equal sign and double quotes I get an error message (see below)

= "WITH MEMBER [Measures].[ParameterCaption] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,FILTER([CUSTOMER JOB].[ACCOUNT NAME].ALLMEMBERS,(" & Parameter!JobCompleteDateYear.value & ",[Measures].[Billed Sales Amount])) ON ROWS FROM [DW PREP ARCHIVE]"

error message

TITLE: Microsoft Visual Studio

The query cannot be retrieved from the query builder.
Check the query for syntax errors.
Reporting Services will continue to use the most recent valid query.

ADDITIONAL INFORMATION:

Query preparation failed. (Microsoft.AnalysisServices.Controls)

Query (1, 1) Parser: The syntax for '=' is incorrect. (msmgdsrv)

BUTTONS:

OK

Any suggestions ?

thanks

I found my solution. I needed to use the Strtomember function and inserted the parameter name as @.parametername

here is the final code

before

WITH MEMBER [Measures].[ParameterCaption] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,FILTER([CUSTOMER JOB].[ACCOUNT NAME].ALLMEMBERS,([Job Complete Date].[Year].&[2006],[Measures].[Billed Sales Amount])) ON ROWS FROM [DW PREP ARCHIVE]

after

WITH MEMBER [Measures].[ParameterCaption] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,FILTER([CUSTOMER JOB].[ACCOUNT NAME].ALLMEMBERS,(Strtomember(@.JobCompleteDateYear),[Measures].[Billed Sales Amount])) ON ROWS FROM [DW PREP ARCHIVE]

Carriage Return in Crystal Syntax

I am sure there is a solution already out there, but I couldn't find it. I am trying to insert a carriage return in some Crystal Syntax I am writing. I am doing the following

cstr(currentfieldvalue) + chr(13) + "FG"

and instead of displaying

"$300.00
FG"

it displays

"$300.00 FG"

Can someone help?Try sending a linefeed only
cstr(currentfieldvalue) + chr(10) + "FG"

or

sending a cr + linefeed
cstr(currentfieldvalue) + chr(13) + chr(10) + "FG"|||Try sending a linefeed only
cstr(currentfieldvalue) + chr(10) + "FG"

or

sending a cr + linefeed
cstr(currentfieldvalue) + chr(13) + chr(10) + "FG"

I tried both of those and it does not work. I have attached a screen shot of the coding area, maybe you can see what I am missing?

Thanks,
Michael|||Forgot to tell you that you have to check the Can Grow property
You can find it in FormatField / Common / CanGrow
Sorry!

Capture Referrer URL within a report.

I have a situation where my best solution is to capture the full Referrer URL in my SSRS 2005 Report to facilitate "Back" links within my reports. I have looked but don't see a way to capture this information natively in a report.

Any help is appreciated.

We don't expose the referrer URL to the processing engine, so there is no way to embed it into the report. In many cases, there is no referrer URL (consider all of the cases where you are not rendering to HTML).

What is wrong with the browser's back button?

|||

John Gallardo - MSFT wrote:

What is wrong with the browser's back button?

That is the same question I posed, but we are using a series of drill through reports and some reports have many pages. When clicking through from one report to another and they click the Back button, it returns them to the first page of the previous report. This can get tedious if they are looking at the detail on several rows from the first report.

Therefore, if I could capture the full URL from the previous report, I would be able to return them to the previous report with a link and they would be on the same page.

Otherwise, it will require trapping the full URL in the code environment which will not be easy to deal with on an infinity basis. I want to have unlimited drill throughs and unlimited Back links, maintining state.

If you have any ideas, I appreciate it.

|||How are you displaying the report? If you are using the HTML viewer control then it should be doing this already.|||

It would make my life easier if I had an ASP.NET solution using report viewer but that is not my situation. My reports are being viewed through JSP pages where the code is posting the request URL string to report server and integrating the returning HTML into the web page. It has posed many challenges, but this is the first one we haven't found a solution for.

If it can't be done, just let me know and I'll deliver the news. I know the JSP code environment should be able to capture this info, and if nothing native will work, I'll probalby setup a named variable placedholder in the Report Server reports that the JSP code will replace with the URL. That might work, but I'd rather keep it in the reports so it works when we internally browse to it directly through the report server web as well.

|||Yes, since you are not directly using the viewer control you will probably have to roll your own solution into your application.|||Thanks for your help John. For the benefit of anyone else in the same boat, the reason the back button doesn't work for me is that it is running under an SSL so the pages aren't cached locally. It works fine if I try it from a standard HTTP insecure address. For those browsing on the company intranet it should be ok.|||Hey,

Can you please share the information on how you are trying to integrate JSP and SSRS 2005. That will be helpful.

Regards,

Raj

Capture Referrer URL within a report.

I have a situation where my best solution is to capture the full Referrer URL in my SSRS 2005 Report to facilitate "Back" links within my reports. I have looked but don't see a way to capture this information natively in a report.

Any help is appreciated.

We don't expose the referrer URL to the processing engine, so there is no way to embed it into the report. In many cases, there is no referrer URL (consider all of the cases where you are not rendering to HTML).

What is wrong with the browser's back button?

|||

John Gallardo - MSFT wrote:

What is wrong with the browser's back button?

That is the same question I posed, but we are using a series of drill through reports and some reports have many pages. When clicking through from one report to another and they click the Back button, it returns them to the first page of the previous report. This can get tedious if they are looking at the detail on several rows from the first report.

Therefore, if I could capture the full URL from the previous report, I would be able to return them to the previous report with a link and they would be on the same page.

Otherwise, it will require trapping the full URL in the code environment which will not be easy to deal with on an infinity basis. I want to have unlimited drill throughs and unlimited Back links, maintining state.

If you have any ideas, I appreciate it.

|||How are you displaying the report? If you are using the HTML viewer control then it should be doing this already.|||

It would make my life easier if I had an ASP.NET solution using report viewer but that is not my situation. My reports are being viewed through JSP pages where the code is posting the request URL string to report server and integrating the returning HTML into the web page. It has posed many challenges, but this is the first one we haven't found a solution for.

If it can't be done, just let me know and I'll deliver the news. I know the JSP code environment should be able to capture this info, and if nothing native will work, I'll probalby setup a named variable placedholder in the Report Server reports that the JSP code will replace with the URL. That might work, but I'd rather keep it in the reports so it works when we internally browse to it directly through the report server web as well.

|||Yes, since you are not directly using the viewer control you will probably have to roll your own solution into your application.|||Thanks for your help John. For the benefit of anyone else in the same boat, the reason the back button doesn't work for me is that it is running under an SSL so the pages aren't cached locally. It works fine if I try it from a standard HTTP insecure address. For those browsing on the company intranet it should be ok.|||Hey,

Can you please share the information on how you are trying to integrate JSP and SSRS 2005. That will be helpful.

Regards,

Raj

Can't use VS 2003 for reports now that VS 2005 and SQL 2005 are installed?

I've been using Visual Studio 2003 successfully for several months to
develop a web-based reporting solution. I've also had SQL Server 2000
installed, including the evaluation version of Reporting Services. I
just recently installed Visual Studio 2005 (but left Visual Studio 2003
installed as well) and updated to SQL Server 2005, including the new
version of Reporting Services. I made a copy of one of the report
projects from my VS 2003 solution and I can successfully open it in VS
2005 and convert the reports to the new RS 2005 format. However, when
I try to open my original solution (not the converted copy) in VS 2003,
the non-report projects open fine, but I get the follow message as it
tries to open each of the report projects: "The application for
C:\MyReportProject.rptproj is not installed. Make sure the application
for the project type (.rptproj) is installed." After that, the word
"unavailable" appears next to each of the report projects in Solution
Explorer, but the non-report projects are fine.
Any ideas?I am having the same issue.
--
John
"Lee" wrote:
> I've been using Visual Studio 2003 successfully for several months to
> develop a web-based reporting solution. I've also had SQL Server 2000
> installed, including the evaluation version of Reporting Services. I
> just recently installed Visual Studio 2005 (but left Visual Studio 2003
> installed as well) and updated to SQL Server 2005, including the new
> version of Reporting Services. I made a copy of one of the report
> projects from my VS 2003 solution and I can successfully open it in VS
> 2005 and convert the reports to the new RS 2005 format. However, when
> I try to open my original solution (not the converted copy) in VS 2003,
> the non-report projects open fine, but I get the follow message as it
> tries to open each of the report projects: "The application for
> C:\MyReportProject.rptproj is not installed. Make sure the application
> for the project type (.rptproj) is installed." After that, the word
> "unavailable" appears next to each of the report projects in Solution
> Explorer, but the non-report projects are fine.
> Any ideas?
>|||Dear All,
solution :
--
install the Client Components from SQL SERVER 2005.
SQL SERVER 2005 -> Client Components -> Business Intelligence
with regards,
Murali
"John K" wrote:
> I am having the same issue.
> --
> John
>
> "Lee" wrote:
> > I've been using Visual Studio 2003 successfully for several months to
> > develop a web-based reporting solution. I've also had SQL Server 2000
> > installed, including the evaluation version of Reporting Services. I
> > just recently installed Visual Studio 2005 (but left Visual Studio 2003
> > installed as well) and updated to SQL Server 2005, including the new
> > version of Reporting Services. I made a copy of one of the report
> > projects from my VS 2003 solution and I can successfully open it in VS
> > 2005 and convert the reports to the new RS 2005 format. However, when
> > I try to open my original solution (not the converted copy) in VS 2003,
> > the non-report projects open fine, but I get the follow message as it
> > tries to open each of the report projects: "The application for
> > C:\MyReportProject.rptproj is not installed. Make sure the application
> > for the project type (.rptproj) is installed." After that, the word
> > "unavailable" appears next to each of the report projects in Solution
> > Explorer, but the non-report projects are fine.
> >
> > Any ideas?
> >
> >|||That will install 2005 components...
What I need back is the 2003 projects templates that work will SQL 2000 !
Has anybody managed to fix this issue ?
> install the Client Components from SQL SERVER 2005.
> SQL SERVER 2005 -> Client Components -> Business Intelligence
> with regards,
> Murali
>
> "John K" wrote:
> > I am having the same issue.
> >
> > --
> > John
> >
> >
> > "Lee" wrote:
> >
> > > I've been using Visual Studio 2003 successfully for several months to
> > > develop a web-based reporting solution. I've also had SQL Server 2000
> > > installed, including the evaluation version of Reporting Services. I
> > > just recently installed Visual Studio 2005 (but left Visual Studio 2003
> > > installed as well) and updated to SQL Server 2005, including the new
> > > version of Reporting Services. I made a copy of one of the report
> > > projects from my VS 2003 solution and I can successfully open it in VS
> > > 2005 and convert the reports to the new RS 2005 format. However, when
> > > I try to open my original solution (not the converted copy) in VS 2003,
> > > the non-report projects open fine, but I get the follow message as it
> > > tries to open each of the report projects: "The application for
> > > C:\MyReportProject.rptproj is not installed. Make sure the application
> > > for the project type (.rptproj) is installed." After that, the word
> > > "unavailable" appears next to each of the report projects in Solution
> > > Explorer, but the non-report projects are fine.
> > >
> > > Any ideas?
> > >
> > >|||Did you try re-installing the RS 2000 report designer?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mikey" <mikey@.discussions.microsoft.com> wrote in message
news:C4164015-D773-40D7-BABB-4E0AE343CC1E@.microsoft.com...
> That will install 2005 components...
> What I need back is the 2003 projects templates that work will SQL 2000 !
> Has anybody managed to fix this issue ?
>
>> install the Client Components from SQL SERVER 2005.
>> SQL SERVER 2005 -> Client Components -> Business Intelligence
>> with regards,
>> Murali
>>
>> "John K" wrote:
>> > I am having the same issue.
>> >
>> > --
>> > John
>> >
>> >
>> > "Lee" wrote:
>> >
>> > > I've been using Visual Studio 2003 successfully for several months to
>> > > develop a web-based reporting solution. I've also had SQL Server
>> > > 2000
>> > > installed, including the evaluation version of Reporting Services. I
>> > > just recently installed Visual Studio 2005 (but left Visual Studio
>> > > 2003
>> > > installed as well) and updated to SQL Server 2005, including the new
>> > > version of Reporting Services. I made a copy of one of the report
>> > > projects from my VS 2003 solution and I can successfully open it in
>> > > VS
>> > > 2005 and convert the reports to the new RS 2005 format. However,
>> > > when
>> > > I try to open my original solution (not the converted copy) in VS
>> > > 2003,
>> > > the non-report projects open fine, but I get the follow message as it
>> > > tries to open each of the report projects: "The application for
>> > > C:\MyReportProject.rptproj is not installed. Make sure the
>> > > application
>> > > for the project type (.rptproj) is installed." After that, the word
>> > > "unavailable" appears next to each of the report projects in Solution
>> > > Explorer, but the non-report projects are fine.
>> > >
>> > > Any ideas?
>> > >
>> > >|||Hi Bruce,
I did this morning.. and it has worked by installing the client designer of
SQL 2000 reporting back onto my machine.
Thanks for the reply. Just hadn't had chance to post my solution back.

Cant update values

Hi Guys

I already searched for one day and didn't find any solution. Unfortunately I can't update my dataset. It works when I remove the string "WHERE ID = ?" in the UpdateCommand but then it updates all the records. I think it must be something obvious which I just don't see:(
There are two tables; tblArtists and tblCountries which are connected together with a Inner Join. Now, I need the ID from the table Artists to update the dataset. How can I get it?

Here the code:
...
SelectCommand="SELECT tblArtists.ID AS IDArtists, tblArtists.ArtistName, tblArtists.FirstName, tblArtists.LastName, tblArtists.Address, tblArtists.PLZ, tblArtists.City, tblArtists.Region, tblArtists.Country_ID, tblArtists.MusicStyles, tblArtists.HomeDJ, tblArtists.Active, tblArtists.CDate, tblArtists.Description, tblCountries.ID AS IDCountries, tblCountries.Country, tblArtists.Producer, tblArtists.Picture, tblArtists.Homepage, tblArtists.Phonenumber, tblArtists.eMail FROM (tblCountries INNER JOIN tblArtists ON tblCountries.ID = tblArtists.Country_ID) WHERE (tblArtists.Active = ?) AND (tblArtists.HomeDJ = ?) AND (tblArtists.Producer = ?)"
UpdateCommand="UPDATE tblArtists SET ArtistName = ?, FirstName = ?, LastName = ?, Address = ?, PLZ = ?, City = ?, Region = ?, Country_ID = ?, MusicStyles = ?, HomeDJ = ?, Active = ?, Description = ?, Producer = ?, Picture = ?, Homepage = ?, Phonenumber = ?, eMail = ? WHERE ID = ?">
<SelectParameters>
<asp:ControlParameter ControlID="activeRadioButton" Name="Active" PropertyName="Checked" Type="Boolean"/>
<asp:ControlParameter ControlID="homedjRadioButton" Name="HomeDJ" PropertyName="Checked" Type="Boolean"/>
<asp:ControlParameter ControlID="producerRadioButton" Name="Producer" PropertyName="Checked" Type="Boolean"/>
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="ArtistName" Type="String" Size="254" />
<asp:Parameter Name="FirstName" Type="String" Size="254" />
<asp:Parameter Name="LastName" Type="String" Size="254" />
<asp:Parameter Name="Address" Type="String" Size="254" />
<asp:Parameter Name="PLZ" Type="String" Size="254" />
<asp:Parameter Name="City" Type="String" Size="254" />
<asp:Parameter Name="Region" Type="String" Size="254" />
<asp:Parameter Name="Country_ID" />
<asp:Parameter Name="MusicStyles" Type="String" Size="254" />
<asp:Parameter Name="HomeDJ" Type="Boolean" />
<asp:Parameter Name="Active" Type="Boolean" />
<asp:Parameter Name="Description" Type="String" />
<asp:Parameter Name="Producer" Type="Boolean" />
<asp:Parameter Name="Picture" Type="String" Size="254" />
<asp:Parameter Name="Homepage" Type="String" Size="254" />
<asp:Parameter Name="Phonenumber" Type="String" Size="50" />
<asp:Parameter Name="eMail" Type="String" Size="50" />
<asp:Parameter Name="original_IDArtists" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>

Greetings
Pat

Hi ya,

is it used in the context of GridVIew? If so did u set the DataKeyNames of the GridView ?

<asp:gridview runat="server" id="GridView1" datasourceid="ArtistsSource"
 AutoGenerateColumns="false"
 DataKeyNames="original_IDArtists" AutoGenerateEditButton="true">
 <columns>
 <asp:boundfield datafield="ArtistName" headertext="Artist Name" />
 <asp:boundfield datafield="FirstName" headertext="First Name" />

.......................
 </columns>
</asp:gridview

Hope this will help,

Yani