Solved

typed dataset, possible If-Then in query

Posted on 2007-04-02
7
633 Views
Last Modified: 2010-05-18
Hi,
In typed dataset - tableadapter query I would like to use an if-then sql query statement but I can't seem to get it to work.   Not even sure if it's poss at all.
Anyway, my straight sql code sofar isn't accepted by the query builder:

IF (@prg_id = 2)
BEGIN
SELECT CAST(grp_no AS varchar) + '.0' + CAST(itm_no AS varchar) + '  ' + CAST(grp_type AS varchar) + '  ' + CAST(itm_desc AS varchar) AS Combi, itm_id
FROM v_Items_Groups WHERE (itm_end > GETDATE()) AND (prg_id = @prg_id )
END
ELSE
SELECT CAST(grp_no AS varchar) + '.' + CAST(itm_no AS varchar) + '  ' + CAST(grp_type AS varchar) + '  ' + CAST(itm_desc AS varchar) AS Combi, itm_id
FROM v_Items_Groups WHERE (itm_end > GETDATE()) AND (prg_id = @prg_id)

error :
The Compound statement SQL construct or statement is not supported.

Is this solvable ?

I know I can create more query's and do a Select Case in the VB code of the originating page but this would be just a bit more flex.
0
Comment
Question by:RonaldZaal
  • 4
  • 3
7 Comments
 
LVL 7

Expert Comment

by:whityum
ID: 18836575
why not put that in a SQL view or stored procedure and bind to the view/sproc?

also, I'd add the BEGIN/END to the else statement.
0
 
LVL 5

Author Comment

by:RonaldZaal
ID: 18836858
Hi whityum,
I have this datatable- tableadapter config and use it widely in the app allready and would like to solve it with an If - Then in the query.
Other solutions I know are poss but I'm wondering if the above would go.
- add the BEGIN/END to the else statement, indeed, but doesn't change the error  :-)
0
 
LVL 7

Expert Comment

by:whityum
ID: 18836940
what does the rest of the query look like?  is that a subselect or what?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 5

Author Comment

by:RonaldZaal
ID: 18836963
uhh, that's the whole query.  Does it look so bad ?  
0
 
LVL 7

Accepted Solution

by:
whityum earned 500 total points
ID: 18837605
i just thought there was more.   i wonder if the table adapter won't support it because it's updateable and you couldn't update based on that query

have you tried using a sqldataadapter or IDataReader instead of the table adapter to bind it to?
0
 
LVL 5

Author Comment

by:RonaldZaal
ID: 18838038
that's possible but when you create a new query for your table adapter you may specify if it's a Select, an Update etc.  As this is only a select it should be possible to use some more complex SQL statement in it.
But then again, maybe not....  
0
 
LVL 5

Author Comment

by:RonaldZaal
ID: 18900067
Decided a while ago to do a workaround.
Still value your input whityum.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question