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

Discussion in 'ASP .Net' started by Eric Nelson, Jan 29, 2009.

  1. Eric Nelson

    Eric Nelson 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
     
    Eric Nelson, Jan 29, 2009
    #1
    1. Advertising

  2. On Jan 29, 1:03 am, Eric Nelson <> wrote:
    > 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
     
    Alexey Smirnov, Jan 29, 2009
    #2
    1. Advertising

  3. Eric Nelson

    Eric Nelson Guest

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

    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



    "Alexey Smirnov" wrote:

    > On Jan 29, 1:03 am, Eric Nelson <> wrote:
    > > 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, Feb 2, 2009
    #3
  4. Re: ASP.NET won't retrieve query results that depend on union quer

    On Feb 2, 10:19 pm, Eric Nelson <> wrote:
    > 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
    >
    > "Alexey Smirnov" wrote:
    > > On Jan 29, 1:03 am, Eric Nelson <> wrote:
    > > > 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


    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
     
    Alexey Smirnov, Feb 2, 2009
    #4
  5. Eric Nelson

    Eric Nelson Guest

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

    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


    "Alexey Smirnov" wrote:

    > On Feb 2, 10:19 pm, Eric Nelson <> wrote:
    > > 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
    > >
    > > "Alexey Smirnov" wrote:
    > > > On Jan 29, 1:03 am, Eric Nelson <> wrote:
    > > > > 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

    >
    > 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, Feb 4, 2009
    #5
  6. Re: ASP.NET won't retrieve query results that depend on union quer

    On Feb 4, 7:02 pm, Eric Nelson <> wrote:
    > 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
    >
    > "Alexey Smirnov" wrote:
    > > On Feb 2, 10:19 pm, Eric Nelson <> wrote:
    > > > 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

    >
    > > > "Alexey Smirnov" wrote:
    > > > > On Jan 29, 1:03 am, Eric Nelson <> wrote:
    > > > > > 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

    >
    > > 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


    Great, good to know, thanks
     
    Alexey Smirnov, Feb 4, 2009
    #6
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. COHENMARVIN
    Replies:
    2
    Views:
    649
    Lucas Tam
    Sep 12, 2005
  2. Martin Johansen

    Font size depend on table height

    Martin Johansen, Sep 20, 2004, in forum: HTML
    Replies:
    7
    Views:
    732
    Martin Johansen
    Sep 22, 2004
  3. Matt Garman
    Replies:
    1
    Views:
    682
    Matt Garman
    Apr 25, 2004
  4. Peter Dunker

    union in struct without union name

    Peter Dunker, Apr 26, 2004, in forum: C Programming
    Replies:
    2
    Views:
    912
    Chris Torek
    Apr 26, 2004
  5. angel
    Replies:
    4
    Views:
    455
    Ype Kingma
    Jul 30, 2004
Loading...

Share This Page