DangerMode
asked on
In LINQ How can I select all with a where parameter
B"H
Say I have a LINQ
<asp:LinqDataSource ID="LinqDataSource4" runat="server"
ContextTypeName="DataClass esDataCont ext" OrderBy="Serial"
Select="new (Serial, ID)" TableName="Items"
Where="Location == @Location">
<WhereParameters>
<asp:ControlParameter ControlID="ddlLocation" DefaultValue="1" Name="Location"
PropertyName="SelectedValu e" Type="Int32" />
</WhereParameters>
This works
Now the user wants to select all locations
ddLocation is All which is value of 1
then I want the where statement to be
IF(condition_expr, true_expr, false_expr)
Where="IF (@Location=1,Location<>-1, Location == @Location)"
True_expr would mean return all rows because none of the locations are -1
False_expr would mean use whatever Location is and return only those rows
However I cant seem to get the syntax to work
No applicable method 'IF' exists in type 'Item'
Also, is there a better way to do this?
Say I have a LINQ
<asp:LinqDataSource ID="LinqDataSource4" runat="server"
ContextTypeName="DataClass
Select="new (Serial, ID)" TableName="Items"
Where="Location == @Location">
<WhereParameters>
<asp:ControlParameter ControlID="ddlLocation" DefaultValue="1" Name="Location"
PropertyName="SelectedValu
</WhereParameters>
This works
Now the user wants to select all locations
ddLocation is All which is value of 1
then I want the where statement to be
IF(condition_expr, true_expr, false_expr)
Where="IF (@Location=1,Location<>-1,
True_expr would mean return all rows because none of the locations are -1
False_expr would mean use whatever Location is and return only those rows
However I cant seem to get the syntax to work
No applicable method 'IF' exists in type 'Item'
Also, is there a better way to do this?
Oh, and this one too : following on from the above... http://msdn.microsoft.com/en-us/vbasic/bb688084.aspx
ASKER
OK but I am trying to change the operation
not the value of location
How can I change the operation that gets performed?
if Location =1 select from where location<>-1
else select from where location=@location
Thanks in advance
not the value of location
How can I change the operation that gets performed?
if Location =1 select from where location<>-1
else select from where location=@location
Thanks in advance
ASKER
am I going to have to do this a stored procedure?
ASKER
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ItemsSelect]
@Location int
AS
if (@Location <> 1)
Begin
select * from Items where Location=@Location
END
else
BEGIN
select * from Items where Location<>-1
end
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ItemsSelect]
@Location int
AS
if (@Location <> 1)
Begin
select * from Items where Location=@Location
END
else
BEGIN
select * from Items where Location<>-1
end
shouldn't have to after all all you are saying is an "or" type statement....
where (location = @location or @location = 1)
where (location = @location or @location = 1)
ASKER
not exactly
I am saying if the Location=1 then select everything
otherwise only select Location
thats why the second select
else
BEGIN
select * from Items where Location <> -1
end
that selects everything
I am saying if the Location=1 then select everything
otherwise only select Location
thats why the second select
else
BEGIN
select * from Items where Location <> -1
end
that selects everything
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much, sometimes I am too close to the problem
you rock
thanks again
you rock
thanks again
It is so much easier to see the wood from the trees when you do not hold the axe - just made that up - might check it to see it I did, or if it is a memory.
And all that I am really saying is, we all get so involved and it is often the case that a fresh set of eyes can see what you have quite possibly always known, but couldn't quite recognise.
Pleased I could be of assistance..
And all that I am really saying is, we all get so involved and it is often the case that a fresh set of eyes can see what you have quite possibly always known, but couldn't quite recognise.
Pleased I could be of assistance..
or,
have a look at : http://lancefisher.net/blog/archive/2008/05/07/linq-to-sql---case-statements.aspx
and generate an quivelent "case" for @location instead of the IF....