Solved

typed dataset, possible If-Then in query

Posted on 2007-04-02
7
609 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

AJAX ModalPopupExtender has a required property "TargetControlID" which may seem to be very confusing to new users. It means the server control that will be extended by the ModalPopup, for instance, if when you click a button, a ModalPopup displays,…
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now