ASP.net OleDbCommand Oracle SQL not returning any results with valid query

Question

I am trying to read the results from an SQL query using OleDbCommand in ASP.net with C# codebehind. I am using the following Oracle SQL statement which is valid and works properly within Oracle SQL Developer:

WITH "bis" AS
    (SELECT v.name AS "vendor", 
    SUM(CASE WHEN i.bi_rating=5 THEN 1 ELSE 0 END) AS "bi5", 
    SUM(CASE WHEN i.bi_rating=6 THEN 1 ELSE 0 END) AS "bi6", 
    SUM(CASE WHEN i.bi_rating=7 THEN 1 ELSE 0 END) AS "bi7" 
    FROM incident_tbl i, vendor_tbl v 
    WHERE i.vendor_code=v.vendor_code 
    GROUP BY v.name 
    ORDER BY v.name) 
SELECT DISTINCT 
    "bis"."vendor", 
    "bis"."bi5", 
    "bis"."bi6", 
    "bis"."bi7", 
    ("bis"."bi5"+"bis"."bi6"+"bis"."bi7") AS "total" 
FROM "bis" 
WHERE "vendor"!='[No Vendor]' 
ORDER BY "total" DESC

This produces a correct result of 92 rows. However when I run it on my ASP.net page, it returns zero rows. I even isolated the query into a test method to try and sort it out: (con is an already-existing working connection string)

protected void test()
    {
        OleDbCommand sql = new OleDbCommand("WITH \"bis\" AS (SELECT v.name AS \"vendor\", SUM(CASE WHEN i.bi_rating=5 THEN 1 ELSE 0 END) AS \"bi5\", SUM(CASE WHEN i.bi_rating=6 THEN 1 ELSE 0 END) AS \"bi6\", SUM(CASE WHEN i.bi_rating=7 THEN 1 ELSE 0 END) AS \"bi7\" FROM incident_tbl i, vendor_tbl v WHERE i.vendor_code=v.vendor_code GROUP BY v.name ORDER BY v.name) SELECT DISTINCT \"bis\".\"vendor\", \"bis\".\"bi5\", \"bis\".\"bi6\", \"bis\".\"bi7\", (\"bis\".\"bi5\"+\"bis\".\"bi6\"+\"bis\".\"bi7\") AS \"total\" FROM incident_tbl i, \"bis\" WHERE \"vendor\"!='[No Vendor]' ORDER BY \"total\" DESC", con);
        try
        {
            if (con.State == ConnectionState.Closed)
                con.Open();

            OleDbDataReader reader = sql.ExecuteReader();

            while (reader.Read())
            {
                // Show on a temporary label for testing
                lblDebug.Text += "<br />New row... vendor: " + reader["vendor"].ToString();
            }
        }
        catch (Exception ex)
        {
            lblDebug.Text += "<br />ERROR WITH TEST METHOD: " + ex;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
                con.Close();
        }
    }

The thing is, no exceptions are thrown, there are just zero results. Any ideas?

...unrelated - but why FROM incident_tbl i, "bis"? You are using only "bis", not incident_tbl — MiMo
– MiMo, Commented Sep 19, 2012 at 13:57
@MiMo You're right, it's not needed. It's left over from when I was building the query so I have now removed it. (Still doesn't work, but that was a very long shot ;-) ) — Ben Hill
– Ben Hill, Commented Sep 19, 2012 at 14:06

MiMo · Accepted Answer · 2012-09-19 14:01:55Z

2

Try with

SELECT DISTINCT  
  vendor,  
  bi5,  
  bi6,  
  bi7,  
  (bi5+bi6+bi7) total  
FROM  
  (SELECT v.name vendor,  
   SUM(CASE WHEN i.bi_rating=5 THEN 1 ELSE 0 END) bi5,  
   SUM(CASE WHEN i.bi_rating=6 THEN 1 ELSE 0 END) bi6,  
   SUM(CASE WHEN i.bi_rating=7 THEN 1 ELSE 0 END) bi7  
   FROM incident_tbl i, vendor_tbl v  
   WHERE i.vendor_code=v.vendor_code  
   GROUP BY v.name  
   ORDER BY v.name) bis
WHERE vendor!='[No Vendor]'  
ORDER BY total DESC

answered Sep 19, 2012 at 14:01

MiMo

12k1 gold badge35 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ben Hill Over a year ago

Success! Thanks, this produces the exact same results as my query in the question, but works in the code! I understand the changes in the query, but is there any reasoning as to why it wasn't working before? Or was it just a guess? :-)

MiMo Over a year ago

..a guess. I recently had a different problem in a query with T as . . . executed from Ole DB and I solved it switching to select ...from - I guessed it was a similar problem.

Ben Hill Over a year ago

Weird, maybe ASP.net only likes SELECT statements that start only with SELECT or something... But at least it's solved, thanks again :-)

Collectives™ on Stack Overflow

ASP.net OleDbCommand Oracle SQL not returning any results with valid query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related