Solved

typed dataset, possible If-Then in query

Posted on 2007-04-02
7
641 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

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…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

756 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