Last post Oct 23, 2013 12:24 PM by katiep23
Oct 22, 2013 06:37 PM|katiep23|LINK
Trying to set a datasource's SelectCommand equal to a SQL query that starts with "WITH" CTEs.
WITH a1 AS (SELECT colA FROM table A),
a2 AS (SELECT colA FROM table B)
SELECT * FROM a1, a2
The SQL itself is fine. I have verified that it works in TOAD. However, is this even supported? I don't get an error message; I just get nothing in return. And when I changed the code & just assigned it a simple SELECT query instead, it returned results
so it's definitely the SQL I'm passing.
Any thoughts? Thanks!
Oct 23, 2013 01:54 AM|Mikesdotnetting|LINK
There's no reason that I can think of why this shouldn't be supported. The SqlDataSource doesn't care what kind of SQL you give it. It doesn't validate it or anything like that. It just attempts to get it executed by SQL Server. If SQL Server generates a
resultset, the SqlDataSource will stuff that into a DataTable (by default).
Oct 23, 2013 03:03 AM|smirnov|LINK
Just run this
<%@ Page %>
<form id="form1" runat="server">
ConnectionString="Data Source=Test;Initial Catalog=Test;Integrated Security=True;"
SelectCommand="WITH a1 AS (SELECT colA FROM table A),
a2 AS (SELECT colA FROM table B)
SELECT * FROM a1, a2">
As Mike already said the SqlDataSource doesn't care what kind of SQL you give it.
Oct 23, 2013 10:41 AM|katiep23|LINK
Tried putting it directly in the sqldatasource like above & it still returned no results. No error or anything, just no results.
But when I remove the CTE's from the query, I have no problem. Ugh. Here is the exact query I am running (it's built dynamically). Can you see anything that .NET would not like for some reason?
WITH COM AS (SELECT DISTINCT multiplier AS "COM" FROM price_sheet_header_pricing WHERE header_id = 6 AND pricing_cat = 'COM' AND pricing_type = 'Default'),
SPC AS (SELECT DISTINCT multiplier AS "SPC" FROM price_sheet_header_pricing WHERE header_id = 6 AND pricing_cat = 'SPC' AND pricing_type = 'Default')
SELECT * FROM COM, SPC
Oct 23, 2013 11:00 AM|katiep23|LINK
Well, it looks like my OleDbCommand/DataReader doesn't like the SQL either, so it's not the SQLDataSource. I probably need to take this topic to another area.
Oct 23, 2013 11:34 AM|smirnov|LINK
I'm not sure how you used SQLDataSource with OleDbCommand and why, but either move to SqlCommand or put your query into the stored procedure and call it from your controls.
Oct 23, 2013 11:42 AM|PatriceSc|LINK
Could it be caused by " you are using inside an attribute which is itself enclosed within " ?
What is trying to use AS [COM] and AS [SPC] instead...
According to your last post it seems you have an error. It's always better to tell which error you have as usually it greatly narrow down the issue.
Oct 23, 2013 11:58 AM|katiep23|LINK
I didn't, but the sqldatasource is using an oledb provider connection. I just used an OleDBCommand separately to test the query string.
Oct 23, 2013 12:04 PM|katiep23|LINK
I don't get an error; I just get 0 rows returned.
It looks like there may be an issue with the OleDB Provider supporting CTE's I think.
Oct 23, 2013 12:24 PM|katiep23|LINK
Switched connection string from "Provider=MSDAORA" to "Provider=OraOLEDB.Oracle" and now it works like a charm! Sorry this ended up in the wrong forum, but hopefully it may end up helping someone else down the road!