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
14  1864 
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" <cj*****@yahoo. co.uk> wrote in message
news:%2******** **********@TK2M SFTNGP09.phx.gb l... 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,
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:%2******** **********@TK2M SFTNGP09.phx.gb l... 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
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:%2******** **********@TK2M SFTNGP09.phx.gb l... 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"
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" <cj*****@yahoo. co.uk> wrote in message
news:OR******** ********@tk2msf tngp13.phx.gbl. .. 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:%2******** **********@TK2M SFTNGP09.phx.gb l... 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
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")%>  ;</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" <re******@NOyah oo.SPAMcom> wrote in message
news:Ov******** ********@TK2MSF TNGP09.phx.gbl. .. Could you show us what you get with
response.write rs.GetString(2, ," | ", "<BR>")
Bob Barrows
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...
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:%2******** *********@TK2MS FTNGP11.phx.gbl ... If you rearrange the order in which you select the columns, does it
display differently? Eg.
[snip]
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 re******@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"
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" <cj*****@yahoo. co.uk> wrote in message
news:eu******** ********@TK2MSF TNGP10.phx.gbl. .. 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:%2******** *********@TK2MS FTNGP11.phx.gbl ... If you rearrange the order in which you select the columns, does it
display differently? Eg.
[snip]
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: serge |
last post by:
Using "SELECT * " is a bad practice even
when using a VIEW instead of a table?
I have some stored procedures that are
identical with the difference of one statement
in the WHERE clause. If I create a single View
and specify also in this View the WHERE clause
that is common in these stored procedures, I
will have the new stored procecures changed to
be like:
|
by: K.Fawcett |
last post by:
I am doing a simple insert into a table and I would like the data to be
retrieved in the order it is inserted not by what appears to be
alphabetical order.
insert into mytbl values('c')
insert into mytbl values('b')
insert into mytbl values('a')
select * from mytbl yields
|
by: JMCN |
last post by:
hello
i have your basic select case question. i created a combo box and save
it as a query. so whenever the user selects the value and clicks the
export button, the select case should then export the data. however,
it does not export the it. so then i converted the export macro to VB
and used "call" to call function but it did not work. what am i doing
wrong or more precisely what am i leaving out?
thanks in advance,
jung
|
by: Lynnannemarie |
last post by:
I'm having problems with the following,
I originally had a PKnumber EmployeeID then had to change it to a text
field so it would accept both numbers and letters. Now it doesn't
work. Any suggestions?
Private Sub List2_AfterUpdate()
Dim varItem As Variant
Dim strTemp As String
Dim strSQL As String
|
by: Anna |
last post by:
Hi all,
I have a query in ASP.NET that selects text from a memo field in Access
(among other things). The query appears to be truncating the text at
255 characters. I've done a little research on this and found some
other Google Groups threads that were helpful, but I still don't have a
solution. My query is rather complicated and uses a union, but not the
keyword distinct. The query is displayed in a datagrid, and I don't
know how I...
| | |
by: parwal.sandeep |
last post by:
Hello grp!
i'm using INNODB tables which are using frequently .
if i fire a SELECT query which fetch major part of table it usually
take 10-20 seconds to complete.
in mean time if any UPDATE qry comes for a perticular row which is part
of SELECT qry i want to know that whether UPDATE will wait for
completing SELECT qry or not,
or it simply executing without bothering SELECT qry .
|
by: Manikandan |
last post by:
Hi,
I have a datatable with rows.
When I used datatable.select with values it is working properly,
But when I use the select with variables it is not working.
I tried with putting '(single quote),"" '(double quote) and many
combination, it is not returning any rows
Sample code for demonstartion
DataTable aDataTable = new DataTable();
aDataTable.Columns.Add("s1", typeof(string));
aDataTable.Columns.Add("s2", typeof(string));
|
by: Jon Bilbao |
last post by:
I´m trying a select clause in two steps because it´s too complex.
First:
SELECT Reference, Results.idEnsayo, Results.Num_taladro, min(Results.dTime)
+ 500 AS tIni, max(Results.dTime) - 500 AS tLast
FROM Results INNER JOIN Ensayos ON Results.idEnsayo=Ensayos.idEnsayo
WHERE (Ensayos.Reference=9)
GROUP BY Reference, Results.idEnsayo, Num_Taladro;
|
by: P3Eddie |
last post by:
Hello all!
I don't know if this can even be done, but I'm sure you will either help or suggest another avenue to accomplish the same.
My problem may be a simple find duplicates / do something with only one of the duplicates issue, but I got to the point where an update query with a nested select subquery would work wonderfully, if only it would work!
I have several fields in a master Access 2000 table, some of which are id, fname,...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
