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

E

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
 
G

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
 
E

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
 
G

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
 
E

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,733
Messages
2,569,440
Members
44,831
Latest member
HealthSmartketoReviews

Latest Threads

Top