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

**Ray at** · Jul 19 '05, 11:07 AM

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

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" <cjmwork@yahoo. co.uk> wrote in message
news:%234$MKx5% 23DHA.3668@TK2M SFTNGP09.phx.gb l...[color=blue]
> I have a query which produces different results in the Access query[/color]
builder[color=blue]
> 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[/color]
the[color=blue]
> 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[/color]
drivers...[color=blue]
>
> Any ideas?
>
> Chris
>
>[/color]

**CJM** · Jul 19 '05, 11:07 AM

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

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% 23DHA.2636@TK2M SFTNGP09.phx.gb l...[color=blue]
> 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
>
>[/color]

**Bob Barrows** · Jul 19 '05, 11:07 AM

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

Could you show us what you get with

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

Bob Barrows
CJM wrote:[color=blue]
> 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% 23DHA.2636@TK2M SFTNGP09.phx.gb l...[color=green]
>> 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[/color][/color]

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

**Ray at** · Jul 19 '05, 11:07 AM

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

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" <cjmwork@yahoo. co.uk> wrote in message
news:ORl5PF6%23 DHA.2524@tk2msf tngp13.phx.gbl. ..[color=blue]
> 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% 23DHA.2636@TK2M SFTNGP09.phx.gb l...[color=green]
> > So you aren't seeing [Rank] from your query in your ASP pages? What[/color][/color]
code[color=blue][color=green]
> > are you using to display the recordset values?
> >
> > --
> >
> > Ray at home
> > Microsoft ASP MVP
> >
> >[/color]
>
>[/color]

**CJM** · Jul 19 '05, 11:07 AM

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

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> </th>
<th> </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 " </td>"
End If
%>
<td><%=.Fields( "HotelName")%>& nbsp;</td>
<td><%=.Fields( "Location")%>&n bsp;</td>
<td><%=.Fields( "Telno")%>&nbsp ;</td>
<%
If sURL <>"" Then
Response.Write "<td><a href=" & sURL & "
target='_blank' >Website</a></td>"
Else
Response.Write "<td>  </td>"
End If
%>
<td><a
href="hotels.as p?id=<%=.Fields ("HotelID")%>"> Details</a> </td>
</tr>
<%
.movenext
Loop
%>

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

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

Chris

"Bob Barrows" <reb01501@NOyah oo.SPAMcom> wrote in message
news:Ov$0YU6%23 DHA.1452@TK2MSF TNGP09.phx.gbl. ..[color=blue]
> Could you show us what you get with
>
> response.write rs.GetString(2, ," | ", "<BR>")
>
> Bob Barrows[/color]

**CJM** · Jul 19 '05, 11:07 AM

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

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** · Jul 19 '05, 11:07 AM

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

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% 23DHA.552@TK2MS FTNGP11.phx.gbl ...[color=blue]
> If you rearrange the order in which you select the columns, does it[/color]
display[color=blue]
> differently? Eg.
>[/color]
[snip]

**Bob Barrows** · Jul 19 '05, 11:07 AM

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

CJM wrote:[color=blue]
> 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...[/color]

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 reb01501@yahoo. com

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"

**Ray at** · Jul 19 '05, 11:07 AM

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

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" <cjmwork@yahoo. co.uk> wrote in message
news:euPk5m6%23 DHA.3220@TK2MSF TNGP10.phx.gbl. ..[color=blue]
> 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% 23DHA.552@TK2MS FTNGP11.phx.gbl ...[color=green]
> > If you rearrange the order in which you select the columns, does it[/color]
> display[color=green]
> > differently? Eg.
> >[/color]
> [snip]
>
>[/color]

**CJM** · Jul 19 '05, 11:07 AM

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

"Bob Barrows" <reb01501@NOyah oo.SPAMcom> wrote in message
news:Offf9q6%23 DHA.2520@TK2MSF TNGP11.phx.gbl. ..[color=blue]
> Are you getting any Ranked records?
>[/color]
No.
[color=blue]
> You are using the same query in both databases?
>[/color]

Exactly the same.
[color=blue]
> 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 reb01501@yahoo. com
>[/color]

Done.

Just out of interest, here are my two connection strings:
'Application("C onnection") = "Provider=Micro soft.Jet.OLEDB. 4.0; Data
Source=travel.m db"
'Application("C onnection") = "PROVIDER=SQLOL EDB;DATA
SOURCE=(local); UID=Username;PW D=password;DATA BASE=Travel"

Cheers

Chris

**CJM** · Jul 19 '05, 11:07 AM

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

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%23 DHA.2592@TK2MSF TNGP10.phx.gbl. ..[color=blue]
> 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?[/color]
What[color=blue]
> 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
>[/color]

**Bob Barrows** · Jul 19 '05, 11:07 AM

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

CJM wrote:[color=blue]
> "Bob Barrows" <reb01501@NOyah oo.SPAMcom> wrote in message
> news:Offf9q6%23 DHA.2520@TK2MSF TNGP11.phx.gbl. ..[color=green]
>> Are you getting any Ranked records?
>>[/color]
> No.
>[color=green]
>> You are using the same query in both databases?
>>[/color]
>
> Exactly the same.
>[color=green]
>> 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 reb01501@yahoo. com
>>[/color]
>
> Done.
>
>
> Just out of interest, here are my two connection strings:
> 'Application("C onnection") = "Provider=Micro soft.Jet.OLEDB. 4.0; Data
> Source=travel.m db"
> 'Application("C onnection") = "PROVIDER=SQLOL EDB;DATA
> SOURCE=(local); UID=Username;PW D=password;DATA BASE=Travel"
>
> Cheers
>
> Chris[/color]

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** · Jul 19 '05, 11:07 AM

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

[color=blue]
> 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.
>[/color]

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

cn.open "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & server.mappath( "travel.mdb ")
' cn.open "Provider=sqlol edb;" & _
' "Data Source=xxxxxxx; " & _
' "Initial Catalog=test;" & _
' "User ID = xxxxx;" & _
' "Password = xxxxxx"
' set rs=server.creat eobject("adodb. recordset")
' cn.qryHotels rs
sSQL="Select 'Ranked' as Source, H.HotelName, H.TelNo, " & _
"H.URL,H.Locati on,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(s SQL,,1)
if rs.eof then
response.write "No records were returned"
else
Response.Write "<table border=1><tr><t d>"
sHTML=rs.GetStr ing(2,,"</td><td>", _
"</td></tr><tr><td>","N A")
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"

**Yan-Hong Huang[MSFT]** · Jul 19 '05, 11:08 AM

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

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.

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

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

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment