Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Select Qry: Differing results via Access & ADO (& SQL Server)

Reply
Thread Tools

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

 
 
CJM
Guest
Posts: n/a
 
      02-25-2004
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


 
Reply With Quote
 
 
 
 
Ray at
Guest
Posts: n/a
 
      02-25-2004
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" <(E-Mail Removed)> wrote in message
news:%234$MKx5%(E-Mail Removed)...
> 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
>
>



 
Reply With Quote
 
 
 
 
CJM
Guest
Posts: n/a
 
      02-25-2004
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%(E-Mail Removed)...
> 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
>
>



 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      02-25-2004
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%(E-Mail Removed)...
>> 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"


 
Reply With Quote
 
Ray at
Guest
Posts: n/a
 
      02-25-2004
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" <(E-Mail Removed)> wrote in message
news:ORl5PF6%(E-Mail Removed)...
> 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%(E-Mail Removed)...
> > 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
> >
> >

>
>



 
Reply With Quote
 
CJM
Guest
Posts: n/a
 
      02-25-2004
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")%>">Detai ls</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" <(E-Mail Removed)> wrote in message
news:Ov$0YU6%(E-Mail Removed)...
> Could you show us what you get with
>
> response.write rs.GetString(2,," | ", "<BR>")
>
> Bob Barrows



 
Reply With Quote
 
CJM
Guest
Posts: n/a
 
      02-25-2004
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...



 
Reply With Quote
 
CJM
Guest
Posts: n/a
 
      02-25-2004
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%(E-Mail Removed)...
> If you rearrange the order in which you select the columns, does it

display
> differently? Eg.
>

[snip]


 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      02-25-2004
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 http://www.velocityreviews.com/forums/(E-Mail Removed)

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"


 
Reply With Quote
 
Ray at
Guest
Posts: n/a
 
      02-25-2004
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" <(E-Mail Removed)> wrote in message
news:euPk5m6%(E-Mail Removed)...
> 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%(E-Mail Removed)...
> > If you rearrange the order in which you select the columns, does it

> display
> > differently? Eg.
> >

> [snip]
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Translating Oracle Package Call from ADO to ADO.Net dmiratsky@yahoo.com ASP .Net 0 02-14-2005 10:39 PM
Transfer ADO Code to ADO.NET ronaldlee ASP .Net 1 12-17-2004 04:08 PM
Accessing an ADO Recordset or Record from ADO.NET nita ASP .Net 1 11-20-2004 07:06 AM
ado connection via sybase sql anywhere rob ASP General 0 02-19-2004 12:38 AM
Ado sort error-Ado Sort -Relate, Compute By, or Sort operations cannot be done on column(s) whose key length is unknown or exceeds 10 KB. Navin ASP General 1 09-09-2003 07:16 AM



Advertisments