Use of CASE statement in Select using SqlDataSource

I need help regarding the use of CASE in Select of SQLDataSource. Actually I am using FORMVIEW and in the Grid i have "Consumer Numbers" as link and on click I save the Consumer Number in Session Variable. When user go into Edit and change the Consumer Number after updation the formview was coming blank cause i was using that number for select statement now. Just need to correct the SELECT and use the case properly and want to do is

More Related Resource Links

SELECT CASE OT.ItemTypeID
WHEN 6 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12)
WHEN 7 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15)
WHEN 8 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 8
WHEN 9 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 9
WHEN 10 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 10
WHEN 11 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12)
WHEN 12 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12)
WHEN 14 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15)
WHEN 15 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15)
WHEN 18 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 18
WHEN 19 THEN SELECT SUM(ISNULL(&qu

Hi everybody,
i'm just getting started in web services and i have a problem connecting to a database. All i want is that my web service recieves a string parameter and return a dataset so that in the client aplication i just show the result of the web service in a datagrid.
Here's the code of the web service:
<%@ WebService Language="C#" CodeBehind="Service1.asmx.cs" %>
using System;using System.Collections;using System.ComponentModel;using System.Data;using System.Linq;using System.Web;using System.Web.Services;using System.Web.Services.Protocols;using System.Xml.Linq;using System.EnterpriseServices;using System.Data.SqlClient;namespace Farmacia{ [WebService(Namespace = "http://localhost/MyWebServices")] [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)] [ToolboxItem(false)] public class Farmacia : System.Web.Services.WebService { private SqlDataAdapter da; [WebMethod(TransactionOption=TransactionOption.RequiresNew)] public DataSet Consultas (string sel) { if (sel == "")&

Working
with LINQ I realized that I had to use the simple case statement in my
SQL query. There is no special keyword for this. To create a case
statement like structure you will have to do it in the select section of
the query.
Below
is an example of the usage of the case statement in LINQ.
var t =
from n in idc.categories select
new {
catName =
(n.id==1 ? "Cat1" : n.id==2
? "Cat2" : n.id==3 ?
"Cat3" : "Unknown Category" )
};
Here
in the above code we are using multiple cases for value 1, 2 and 3

Hello
,
I exectuted the code(alter/add with check)
below in SSMS . My_table has 200 millions records .
While running the alter/add with check query
the server crashed and the “SQL DIAGOSITC MANAGER” which run on the top of sql serever alert the DBA that select * from my_table was obtained from the last command issued by the user using a
DBCC command.
Where does the select * from my_table comes from ? I didn’t excuted select * from my_table at all .
After the sql serever re started I was able to run the command below with out any problem. I checked the SQL PROFILER for the trace there is
no “select * from my_table” .
So again where this select * from my_table comes from ?
Here is my initial query
-----------------------------------------------------------------
ALTER
TABLE [Dbo].my_table
WITH CHECK
ADD CONSTRAINT [FK_key1]
FOREIGN KEY([Key1])
REFERENCES [Dbo].[Dim_table1]
([Key1])
GO
ALTER
TABLE [Dbo].my_table
CHECK CONSTRAINT [FK_key1]
GO
ALTER
TABLE [Dbo].my_table
WITH CHECK
ADD CONSTRAINT [FK_key2]
FOREIGN KEY([key2])
REFERENCES [Dbo].[Dim_table2]
([key2])
GO
ALTER
TABLE [Dbo].my_table
CHECK CONSTRAINT [FK_key2]
GO
ALTER
TABLE [Dbo].my_table
WITH CHECK
ADD CONSTRAINT [FK_key3]
FOREIGN KEY([d], [c], [r], [g])
REFERENCES [Dbo].my

Hi,I am fairly new at SQL and I have been struggling for days now trying to find an answer to my problem and i have come to the point where i have run out of ideas and about to give up. I'm hoping someone can put me in the correct path. The problem
I have 3 table
Table 1 Department" has the following columns: REF, NAME
Table 2 "Department_Collection" has the following columns: REF, DEPARTMENT_REF, MANAGER_REF, STORE_REF, ACTIVE
Table 3 Store" has the following columns: REF, NAME, STORE_ID
What i am trying to do is to take all the rows in the Department table and get a matching row (DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF) from the Department_Collection table, if it does not match any then still display DEPARTMENT.NAME but mark DEPARTMENT_COLLECTION.REF as null.
I have tried the following select statement but it seem to remove all null values when supplied with a 'storename'
SELECT DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF FROM DEPARTMENT_COLLECTION
right outer join DEPARTMENT on DEPARTMENT_COLLECTION.DEPARTMENT_REF = DEPARTMENT.REF
left outer join STORE on DEPARTMENT_COLLECTION.STORE_REF = STORE.REF
where STORE.NAME = 'storename'
order by DEPARTMENT.NAME
Any help will be greatly appreciated. Thanks

hi friends,
i have table-value function, but the has the bug in the body and i cannot debug, becuase it does not allow the select statement in it.
is there away to print values to the screen, when debugging?
PS: in not using a SP to debug it
thanks

Hi All,
I am having trouble getting started on writing a select statement to meet the following requirements.
I have a table with Employee Ids, address types, and address information. I would like to obtain one record for each EmplID based upon the Type column; the PRF type
record if it exists, otherwise then the PRI record. (See Table A)
TableA
Rcd #, EmplID, Type, Address Data
1, 000001, PRI, etc
2, 000001, PRF, etc
3, 000002, PRI, etc
4, 000003, PRI, etc
5, 000003, PRF, etc
6, 000003, OTH, etc
So, my result set from the table above would be:
2, 000001, PRF, etc
3, 000002, PRI, etc
5, 000003, PRF, etc
What is the best way to obtain this information via a SELECT statement?
Thanks in advance,
Steve

Is it possible to get the output from stored procedure and assign into select statement.
for example.
Here prc_GetComplete is another storeprocedure to get the value for @Completes
INSERT INTO TABELE1
(ID,
address,
completes
)
SELECT NEWID(),
t2.address,
@Completes = EXEC prc_GetComplete t2.ID , @Completes OUT
FROM TABELE2 t2
Regards,
teesh

Hi, I am connecting to a MAS90 database via DSN and am having trouble with the query string for ODBC. I can collect most all fields if I SELECT them directly, however, I want to create a result column based on which cost is higher.
As far as I know, this select statement in to and ODBC connection needs to comply with MSSQL
I have tried
SELECT IM1_InventoryMasterfile.ItemNumber, IFF(IM1_InventoryMasterfile.LastCost > IM1_InventoryMasterfile.AveCost,IM1_InventoryMasterfile.LastCost,IM1_InventoryMasterfile.AveCost) as 'Cost', IM1_InventoryMasterfile.TotalQtyOnHand
FROM IM1_InventoryMasterfile IM1_InventoryMasterfile
And
SELECT IM1_InventoryMasterfile.ItemNumber, CASE WHEN IM1_InventoryMasterfile.LastCost > IM1_InventoryMasterfile.AveCost THEN IM1_InventoryMasterfile.LastCost ELSE IM1_InventoryMasterfile.AveCost END as 'Cost', IM1_InventoryMasterfile.TotalQtyOnHand
FROM IM1_InventoryMasterfile IM1_InventoryMasterfile
Neither are working? What am I missing here? Can I do this sort of if then statement in MSSQL?
Thanks for your help

Hi All. Greetings. I have 2 tables.Table =Person
ID Person ---------- ----------- 1 ali2 abuTable = DetailID Person_ID fakeID---------- ----------- --------------1 1 442 6 553 2 66select * from person, detail where person.id=detail.person_id and fakeID=55I want it show me record of 55 because of Person_ID 6 is not in Table Person and i am using operator AND,so It is not showing any thing.But i want, it show me record of 55 so what i change in this statement or which opertor i use .... so it show me.select * from person, detail where person.id=detail.person_id and fakeID=55I mean i want if value 6 in the (table Person) it show me the name of Person, but if ID 6 or value 6 not there even it show mebut show me empty place of name. Because 6 i required. ID Person ---------- ----------- 6
Any if there is 6 in the (Table Person) then
ID Person ---------- ----------- 6 Rubi Thanks Advance

Hi,
I'm trying to read Excel file header column name. Then I can query data from excel using SELECT statement. For instance. column name is name,
department and email. Then I want this three header name being detected using code and put in select statement. User no need to care about the
header name and we can use code to read the header name instead of hardcode header name inside SELECT statement.
Please provide the details. Thanks.

Hi All,
I have table something like this:-
SELECT UserName, IsManager FROM Employee
IsManager has only two values either 1 (which means user is a Manager) or 0 (which means user is not a Manager)
Now I want to write below query:-
DECLARE @IsManager INT
SET @IsManager = 2
SELECT
UserName,
IsManager
FROM
Employee
WHERE IsManager =
CASE
WHEN @IsManager = 1 THEN 1
WHEN @IsManager = 0 THEN 0
WHEN @IsManager = 2 THEN 1 | 0 ----- THIS IS NOT WORKING, when I set @IsManager value to 2 then where clause should be like this "IsManager = 1 OR IsManager = 0"
END
Can anybody help me out in modifying about query so that it produces both the results when @IsManager variable value "2".
Thanks
Regards,
Kumar

I have some query logic that needs to return two dynamically created columns based on two conditional tests so that
- If Test1 is True then the values for columns 1 and 2 are always the same regardless of the results of Test2.
- If Test1 is False then Test2 determines the value of Column2
Because the CASE statement only produces one column I am having to repeat Test1 and then nest Test2 in the ELSE portion of the second iteration of Test1. I was hoping that someone might have a different approach that would reduce the amount of testing
being performed in the SQL. I did see the post at
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/a0e45c7d-eedd-41ca-89f9-163c4dadc1a7/ that doesn't offer much hope but I thought I'd ask.
For what it's worth I'm posting the heart of my logic below. The test is performing bitwise comparisons to determine (Test1:) If the currently logged in user has access to a document and, if not (Test2) what they need to do to gain access. @Access
will end up as a parameter in the stored procedure once I finalize this.
DECLARE
@Access tinyint
= 1
SELECT
Holding.HoldingID,
ShortTitle, Access,
CASE
WHEN @Access
&[Access]
= @Access
THEN
(URL
+ [FileName])
ELSE
null
END
AS [HoldingURL],
CASE