Select Qry: Differing results via Access & ADO (& SQL Server)

Discussion in 'ASP General' started by CJM, Feb 25, 2004.

  1. CJM

    CJM Guest

    I have a query which produces different results in the Access query builder
    and in an ASP page (via ADO)

    An example of the query is:
    ----------------------------------------------------------
    Select 'Ranked' as Source, H.HotelName, H.TelNo, H.URL, H.Location,
    H.HotelID, Rank
    from (Hotels H Inner Join PrefHotels P on H.HotelID = P.HotelID)
    Inner Join Locations L on P.LocID = L.LocID
    where Rank is not null and Rank > 0 and L.LocID=2

    Union

    Select 'Unranked' as Source, H.HotelName, H.TelNo, H.URL, H.Location,
    H.HotelID, Rank
    from (Hotels H Inner Join PrefHotels P on H.HotelID = P.HotelID)
    Inner Join Locations L on P.LocID = L.LocID
    where (Rank is Null or Rank = 0) and L.LocID=2

    Order By Source, Rank
    ----------------------------------------------------------

    In Access, I get the following results

    Ranked, Hotel1, [other fields], 1
    Ranked, Hotel2, [other fields], 2
    Ranked, Hotel3, [other fields], 3
    Unranked, Hotel4, [other fields], 0
    Unranked, Hotel4, [other fields], 0
    Unranked, Hotel4, [other fields], 0
    etc...

    In my ASP Page I get these results:

    Unranked, Hotel1, [other fields],
    Unranked, Hotel2, [other fields],
    Unranked, Hotel3, [other fields],
    Unranked, Hotel4, [other fields],
    Unranked, Hotel4, [other fields],
    Unranked, Hotel4, [other fields],
    etc...

    I've imported the Access DB to SQL Server, and in Query Analyser and in the
    ASP page I get the right result:
    Ranked, Hotel1, [other fields], 1
    Ranked, Hotel2, [other fields], 2
    Ranked, Hotel3, [other fields], 3
    Unranked, Hotel4, [other fields], 0
    Unranked, Hotel4, [other fields], 0
    Unranked, Hotel4, [other fields], 0
    etc...

    (ie Same as in Access)

    I'm assuming that if Access itself doesnt object to the SQL, it must be
    right. Therefore, the problem lies with ADO and the Access OLEDB drivers...

    Any ideas?

    Chris
     
    CJM, Feb 25, 2004
    #1
    1. Advertising

  2. CJM

    Ray at Guest

    So you aren't seeing [Rank] from your query in your ASP pages? What code
    are you using to display the recordset values?

    --

    Ray at home
    Microsoft ASP MVP


    "CJM" <> wrote in message
    news:%234$MKx5%...
    > I have a query which produces different results in the Access query

    builder
    > and in an ASP page (via ADO)
    >
    > An example of the query is:
    > ----------------------------------------------------------
    > Select 'Ranked' as Source, H.HotelName, H.TelNo, H.URL, H.Location,
    > H.HotelID, Rank
    > from (Hotels H Inner Join PrefHotels P on H.HotelID = P.HotelID)
    > Inner Join Locations L on P.LocID = L.LocID
    > where Rank is not null and Rank > 0 and L.LocID=2
    >
    > Union
    >
    > Select 'Unranked' as Source, H.HotelName, H.TelNo, H.URL, H.Location,
    > H.HotelID, Rank
    > from (Hotels H Inner Join PrefHotels P on H.HotelID = P.HotelID)
    > Inner Join Locations L on P.LocID = L.LocID
    > where (Rank is Null or Rank = 0) and L.LocID=2
    >
    > Order By Source, Rank
    > ----------------------------------------------------------
    >
    > In Access, I get the following results
    >
    > Ranked, Hotel1, [other fields], 1
    > Ranked, Hotel2, [other fields], 2
    > Ranked, Hotel3, [other fields], 3
    > Unranked, Hotel4, [other fields], 0
    > Unranked, Hotel4, [other fields], 0
    > Unranked, Hotel4, [other fields], 0
    > etc...
    >
    > In my ASP Page I get these results:
    >
    > Unranked, Hotel1, [other fields],
    > Unranked, Hotel2, [other fields],
    > Unranked, Hotel3, [other fields],
    > Unranked, Hotel4, [other fields],
    > Unranked, Hotel4, [other fields],
    > Unranked, Hotel4, [other fields],
    > etc...
    >
    > I've imported the Access DB to SQL Server, and in Query Analyser and in

    the
    > ASP page I get the right result:
    > Ranked, Hotel1, [other fields], 1
    > Ranked, Hotel2, [other fields], 2
    > Ranked, Hotel3, [other fields], 3
    > Unranked, Hotel4, [other fields], 0
    > Unranked, Hotel4, [other fields], 0
    > Unranked, Hotel4, [other fields], 0
    > etc...
    >
    > (ie Same as in Access)
    >
    > I'm assuming that if Access itself doesnt object to the SQL, it must be
    > right. Therefore, the problem lies with ADO and the Access OLEDB

    drivers...
    >
    > Any ideas?
    >
    > Chris
    >
    >
     
    Ray at, Feb 25, 2004
    #2
    1. Advertising

  3. CJM

    CJM Guest

    Ray,

    I'll spare you the full table structure, but in the debugging process, I
    added a line like this straight after the oConn.Execute:

    With rs
    Response.Write .fields(0) & "," & .fields(1) & "," & ....... &
    ..fields(7)
    End With

    And obviously it came out as:

    Unranked, Hotelname, ...other details... , <= No Rank
    value

    Chris



    "Ray at <%=sLocation%> [MVP]" <Too many private support requests - Ask for
    it if needed> wrote in message
    news:%23y3Vn55%...
    > So you aren't seeing [Rank] from your query in your ASP pages? What code
    > are you using to display the recordset values?
    >
    > --
    >
    > Ray at home
    > Microsoft ASP MVP
    >
    >
     
    CJM, Feb 25, 2004
    #3
  4. CJM

    Bob Barrows Guest

    Could you show us what you get with

    response.write rs.GetString(2,," | ", "<BR>")

    Bob Barrows
    CJM wrote:
    > Ray,
    >
    > I'll spare you the full table structure, but in the debugging
    > process, I added a line like this straight after the oConn.Execute:
    >
    > With rs
    > Response.Write .fields(0) & "," & .fields(1) & "," & ....... &
    > .fields(7)
    > End With
    >
    > And obviously it came out as:
    >
    > Unranked, Hotelname, ...other details... , <= No
    > Rank value
    >
    > Chris
    >
    >
    >
    > "Ray at <%=sLocation%> [MVP]" <Too many private support requests -
    > Ask for it if needed> wrote in message
    > news:%23y3Vn55%...
    >> So you aren't seeing [Rank] from your query in your ASP pages? What
    >> code are you using to display the recordset values?
    >>
    >> --
    >>
    >> Ray at home
    >> Microsoft ASP MVP




    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Feb 25, 2004
    #4
  5. CJM

    Ray at Guest

    If you rearrange the order in which you select the columns, does it display
    differently? Eg.

    Select 'Ranked' as Source,Rank, H.HotelName, H.TelNo, H.URL, H.Location,
    H.HotelID
    from (Hotels H Inner Join PrefHotels P on H.HotelID = P.HotelID)
    Inner Join Locations L on P.LocID = L.LocID
    where Rank is not null and Rank > 0 and L.LocID=2

    Union

    Select 'Unranked' as Source,Rank, H.HotelName, H.TelNo, H.URL, H.Location,
    H.HotelID
    from (Hotels H Inner Join PrefHotels P on H.HotelID = P.HotelID)
    Inner Join Locations L on P.LocID = L.LocID
    where (Rank is Null or Rank = 0) and L.LocID=2

    Order By Source, Rank

    Ray at work

    "CJM" <> wrote in message
    news:ORl5PF6%...
    > Ray,
    >
    > I'll spare you the full table structure, but in the debugging process, I
    > added a line like this straight after the oConn.Execute:
    >
    > With rs
    > Response.Write .fields(0) & "," & .fields(1) & "," & ....... &
    > .fields(7)
    > End With
    >
    > And obviously it came out as:
    >
    > Unranked, Hotelname, ...other details... , <= No Rank
    > value
    >
    > Chris
    >
    >
    >
    > "Ray at <%=sLocation%> [MVP]" <Too many private support requests - Ask for
    > it if needed> wrote in message
    > news:%23y3Vn55%...
    > > So you aren't seeing [Rank] from your query in your ASP pages? What

    code
    > > are you using to display the recordset values?
    > >
    > > --
    > >
    > > Ray at home
    > > Microsoft ASP MVP
    > >
    > >

    >
    >
     
    Ray at, Feb 25, 2004
    #5
  6. CJM

    CJM Guest

    Well you are onto something here Bob...

    I seem to be getting the right response:

    Ranked | Hotelname | TelNo | | Location | 4 | 1
    etc...

    So by this we can determine the recordset is correct... I'm really confused
    now!

    Here's the next code snippet:
    ------------------------------------------------------
    Set rsInfo = oConn.Execute (sSQL, iAffected, adCmdText)

    With rsInfo

    %>
    <thead>
    <tr class="Hotel">
    <th>Rank</th>
    <th>Hotel</th>
    <th>Location</th>
    <th>Tel No.</th>
    <th>&nbsp;</th>
    <th>&nbsp;</th>
    </tr>
    </thead>
    <%
    Do while not .EOF
    sURL =.Fields("URL")
    If Left(sURL,7)<>"http://" Then
    sURL = "http://" & sURL
    End if

    Response.Write "<tr><td>"
    If .fields("Source") = "Ranked" Then
    Response.Write .Fields("Rank") & "</td>"
    Else
    Response.Write "&nbsp;</td>"
    End If
    %>
    <td><%=.Fields("HotelName")%>&nbsp;</td>
    <td><%=.Fields("Location")%>&nbsp;</td>
    <td><%=.Fields("Telno")%>&nbsp;</td>
    <%
    If sURL <>"" Then
    Response.Write "<td><a href=" & sURL & "
    target='_blank'>Website</a></td>"
    Else
    Response.Write "<td>&nbsp;</td>"
    End If
    %>
    <td><a
    href="hotels.asp?id=<%=.Fields("HotelID")%>">Details</a>&nbsp;</td>
    </tr>
    <%
    .movenext
    Loop
    %>

    etc....
    -------------------------------------------------------

    There doesnt seem to be anything to contentious in it.. but clearly there
    must be something...

    Chris

    "Bob Barrows" <> wrote in message
    news:Ov$0YU6%...
    > Could you show us what you get with
    >
    > response.write rs.GetString(2,," | ", "<BR>")
    >
    > Bob Barrows
     
    CJM, Feb 25, 2004
    #6
  7. CJM

    CJM Guest

    Sorry... I'm being a numpty again... I was still using the SQL Server DB!

    Bob, your code tallies with my previous output:

    Unranked | Hotelname | TelNo | | Location | 6 | <= 'Unranked'
    plus no Rank value
    etc...
     
    CJM, Feb 25, 2004
    #7
  8. CJM

    CJM Guest

    Ray,

    I tried the example you gave... no - it didnt work either.

    Chris

    "Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
    message news:%23IHdFe6%...
    > If you rearrange the order in which you select the columns, does it

    display
    > differently? Eg.
    >

    [snip]
     
    CJM, Feb 25, 2004
    #8
  9. CJM

    Bob Barrows Guest

    CJM wrote:
    > Sorry... I'm being a numpty again... I was still using the SQL Server
    > DB!
    >
    > Bob, your code tallies with my previous output:
    >
    > Unranked | Hotelname | TelNo | | Location | 6 | <=
    > 'Unranked' plus no Rank value
    > etc...


    Are you getting any Ranked records?

    You are using the same query in both databases?

    So I don't have to recreate your database to test your code, do this:

    rsInfo.Save <filename>, 1

    and send me the file at

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Feb 25, 2004
    #9
  10. CJM

    Ray at Guest

    And you're sure it's not an HTML issue, in that there's an unclosed tag
    somewhere or something? Like, do you values show up in a view source? What
    happens if you just select the rank from a table? Do you have the latest
    version of MDAC? I remember reading things before about column values not
    showing up or generating errors, but this is when the columns are "memo"
    type in Access, and perhaps some large text types in SQL, but never with a
    numeric column.

    Ray at work

    "CJM" <> wrote in message
    news:euPk5m6%...
    > Ray,
    >
    > I tried the example you gave... no - it didnt work either.
    >
    > Chris
    >
    > "Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
    > message news:%23IHdFe6%...
    > > If you rearrange the order in which you select the columns, does it

    > display
    > > differently? Eg.
    > >

    > [snip]
    >
    >
     
    Ray at, Feb 25, 2004
    #10
  11. CJM

    CJM Guest

    "Bob Barrows" <> wrote in message
    news:Offf9q6%...
    > Are you getting any Ranked records?
    >

    No.

    > You are using the same query in both databases?
    >


    Exactly the same.

    > So I don't have to recreate your database to test your code, do this:
    >
    > rsInfo.Save <filename>, 1
    >
    > and send me the file at
    >


    Done.


    Just out of interest, here are my two connection strings:
    'Application("Connection") = "Provider=Microsoft.Jet.OLEDB.4.0; Data
    Source=travel.mdb"
    'Application("Connection") = "PROVIDER=SQLOLEDB;DATA
    SOURCE=(local);UID=Username;PWD=password;DATABASE=Travel"

    Cheers

    Chris
     
    CJM, Feb 25, 2004
    #11
  12. CJM

    CJM Guest

    Ray,

    I'm reluctant to say anything is for certain(!), but I would say that it
    isnt an ASP/HTML issue, since the SQL Server version works. [I'm assuming
    you are also following Bob's line of enquiry as well]. The data is coming
    out wrong... I think.

    I'm on MDAC 2.8.

    Chris

    "Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
    message news:u1RLzw6%...
    > And you're sure it's not an HTML issue, in that there's an unclosed tag
    > somewhere or something? Like, do you values show up in a view source?

    What
    > happens if you just select the rank from a table? Do you have the latest
    > version of MDAC? I remember reading things before about column values not
    > showing up or generating errors, but this is when the columns are "memo"
    > type in Access, and perhaps some large text types in SQL, but never with a
    > numeric column.
    >
    > Ray at work
    >
     
    CJM, Feb 25, 2004
    #12
  13. CJM

    Bob Barrows Guest

    CJM wrote:
    > "Bob Barrows" <> wrote in message
    > news:Offf9q6%...
    >> Are you getting any Ranked records?
    >>

    > No.
    >
    >> You are using the same query in both databases?
    >>

    >
    > Exactly the same.
    >
    >> So I don't have to recreate your database to test your code, do this:
    >>
    >> rsInfo.Save <filename>, 1
    >>
    >> and send me the file at
    >>

    >
    > Done.
    >
    >
    > Just out of interest, here are my two connection strings:
    > 'Application("Connection") = "Provider=Microsoft.Jet.OLEDB.4.0; Data
    > Source=travel.mdb"
    > 'Application("Connection") = "PROVIDER=SQLOLEDB;DATA
    > SOURCE=(local);UID=Username;PWD=password;DATABASE=Travel"
    >
    > Cheers
    >
    > Chris


    This is strange. I see zeroes in the unranked records. You've put zeroes in
    some of them?
    Looking at your query, I think I see a problem:

    where Rank is not null and Rank > 0 and L.LocID=2

    This should be

    where Rank > 0 and L.LocID=2

    If Rank is greater than 0, it is by definition, not null. I'm not sure this
    is causing your problem but... It looks like the ranked records are
    returning Nulls in the Rank column ... I'm not sure why - if the column
    contains Null in those records, it should not return those records at all.
    Try my suggested change and if it makes no difference, I guess I need to see
    the data in your database. Can you zip it up and send it to me? If possible,
    if you could send me a copy containing only the relevant tables and data,
    that would be great.

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Feb 25, 2004
    #13
  14. CJM

    Bob Barrows Guest


    > This is strange. I see zeroes in the unranked records. You've put
    > zeroes in some of them?
    > Looking at your query, I think I see a problem:
    >
    > where Rank is not null and Rank > 0 and L.LocID=2
    >
    > This should be
    >
    > where Rank > 0 and L.LocID=2
    >
    > If Rank is greater than 0, it is by definition, not null. I'm not
    > sure this is causing your problem but... It looks like the ranked
    > records are returning Nulls in the Rank column ... I'm not sure why -
    > if the column contains Null in those records, it should not return
    > those records at all. Try my suggested change and if it makes no
    > difference, I guess I need to see the data in your database. Can you
    > zip it up and send it to me? If possible, if you could send me a copy
    > containing only the relevant tables and data, that would be great.
    >


    Sorry, but I cannot reproduce your problem. This code produces the correct
    results as far as I can see:

    cn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & server.mappath("travel.mdb")
    ' cn.open "Provider=sqloledb;" & _
    ' "Data Source=xxxxxxx;" & _
    ' "Initial Catalog=test;" & _
    ' "User ID = xxxxx;" & _
    ' "Password = xxxxxx"
    ' set rs=server.createobject("adodb.recordset")
    ' cn.qryHotels rs
    sSQL="Select 'Ranked' as Source, H.HotelName, H.TelNo, " & _
    "H.URL,H.Location,H.HotelID, Rank from (Hotels H " & _
    "Inner Join PrefHotels P on H.HotelID = P.HotelID) " & _
    "Inner Join Locations L on P.LocID = L.LocID" & _
    " where Rank > 0 and L.LocID=2 " & _
    " UNION ALL Select 'Unranked' as Source, H.HotelName, " & _
    "H.TelNo,H.URL, H.Location, H.HotelID, Rank from (Hotels H " & _
    "Inner Join PrefHotels P on H.HotelID = P.HotelID) " & _
    "Inner Join Locations L on P.LocID = L.LocID " & _
    "where (Rank is Null or Rank = 0) and L.LocID=2 " & _
    "ORDER BY Source, Rank;"
    set rs=cn.Execute(sSQL,,1)
    if rs.eof then
    response.write "No records were returned"
    else
    Response.Write "<table border=1><tr><td>"
    sHTML=rs.GetString(2,,"</td><td>", _
    "</td></tr><tr><td>","NA")
    sHTML = Left(sHTML, len(sHTML) - 8)
    response.Write sHTML
    response.write "</table>"
    end if
    rs.close: set rs=nothing
    cn.close: set cn=nothing

    I even imported the tables into my own sql server and verified that I got
    the same results from both sources. I tried it both with the dynamic SQL
    and using the corrected qryHotels saved query (creating a stored procedure
    in SQL Server so I could test from both sources). I'm not sure why you want
    to use dynamic SQL, but ...

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Feb 25, 2004
    #14
  15. Hello Chris,

    I noticed that the problem is also posted in asp.db group. I replied you
    there. If you have any more concerns on it, please feel free to reply in
    that thread. We will follow up it there.

    Thanks very much.

    Best regards,
    Yanhong Huang
    Microsoft Community Support

    Get Secure! ┬ĘC www.microsoft.com/security
    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Yan-Hong Huang[MSFT], Feb 26, 2004
    #15
    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. Bob Green via .NET 247

    qry returns nothing (no data matched qry)

    Bob Green via .NET 247, Apr 16, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    393
    Sebastian
    Apr 16, 2004
  2. Qry strlen ?

    , Oct 9, 2006, in forum: C Programming
    Replies:
    9
    Views:
    456
    Peter Nilsson
    Oct 10, 2006
  3. Sheth Raxit

    Qry : Behaviour of fgets -- ?

    Sheth Raxit, Sep 6, 2007, in forum: C Programming
    Replies:
    345
    Views:
    5,328
    Flash Gordon
    Oct 20, 2007
  4. BaWork

    Qry data to Page

    BaWork, Nov 20, 2006, in forum: ASP General
    Replies:
    3
    Views:
    149
    Anthony Jones
    Nov 20, 2006
  5. Replies:
    9
    Views:
    1,055
    MikeR
    Mar 22, 2010
Loading...

Share This Page