Solved

typed dataset, possible If-Then in query

Posted on 2007-04-02
7
649 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

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…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

617 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