Solved

typed dataset, possible If-Then in query

Posted on 2007-04-02
7
626 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 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