ASP.NET won't retrieve query results that depend on union query

[Eric Nelson]

I have a query in an MS Access DB, which selects from the results of a union
query. If I open the query in MS Access I see results. If I open the query in
VS2008 Server Explorer within an ASP.NET project, I get nothing back.

Moreover, Server Explorer shows the union query as a Stored Procedure?

What's up and how can I fix this?

Thanks
Eric

 

[Guest]

I have a query in an MS Access DB, which selects from the results of a union
query. If I open the query in MS Access I see results. If I open the query in
VS2008 Server Explorer within an ASP.NET project, I get nothing back.

Moreover, Server Explorer shows the union query as a Stored Procedure?

What's up and how can I fix this?

Thanks
Eric

I think this is not because of union but because of OLE DB. Try to
write custom query in VS.NET using "select * from" and name of your MS
Access Query and see if this works. For example, if your query named
"Query1" and you see it under "Stored Procedures" in Server Explorer,
click "New Query" and type

SELECT * FROM Query1

Hope this helps

 

[Eric Nelson]

Hi Alexey,
Thanks for your response. I have done some experiments

I wrote a program to open an OleDbConnection to an MSAccess db, create an
OleDbCommand and execute a reader, connecting the reader to a GridView.

if the query is "select * from UnionQuery" it works.

However, if the query is "select * from NormalQueryThatUsesUnionQuery" it
returns no rows (reader.HasRows = false).

any ideas?

Thanks,
Eric

 

[Guest]

Hi Alexey,
Thanks for your response. I have done some experiments

I wrote a program to open an OleDbConnection to an MSAccess db, create an
OleDbCommand and execute a reader, connecting the reader to a GridView.

if the query is "select * from UnionQuery" it works.

However, if the query is "select * from NormalQueryThatUsesUnionQuery" it
returns no rows (reader.HasRows = false).

any ideas?

Thanks,
Eric

Hi Eric,

this is exactly what I tried to do, "select * from UnionQuery" works,
but using "UNION" clause in the plain text query does nothing and
returns no result. I didn't find any information regarding this, in
the same time I see that people asking about the same problem, so
perhaps it's a bug in OLE DB, or I don't know.

Alexey

 

[Eric Nelson]

Hi Alexey,

Here's what I've learned:

Referring to TableAdapter.SelectCommand using the QueryBuilder to create

1. The query in QueryBuilder can be a union query itself. I finally ended up
with a union of a couple dozen joins over tables.

2. The query can reference a Union Query directly, as in "select a,b,c from
UnionQuery". The Query Builder does not show union queries in its list of
views, but you can type in the SQL command, naming the columns.

3. If the query references a query that itself uses a union query you get no
results. This must be a bug in OleDb.

Best,
Eric

 

[Guest]

Hi Alexey,

Here's what I've learned:

Referring to TableAdapter.SelectCommand using the QueryBuilder to create

1. The query in QueryBuilder can be a union query itself. I finally ended up
with a union of a couple dozen joins over tables.

2. The query can reference a Union Query directly, as in "select a,b,c from
UnionQuery". The Query Builder does not show union queries in its list of
views, but you can type in the SQL command, naming the columns.

3. If the query references a query that itself uses a union query you get no
results. This must be a bug in OleDb.

Best,
Eric

Great, good to know, thanks