|
 |
access_asp thread: error (0x80040E14) access db and relationships please help
Message #1 by apalmer@n... on Wed, 20 Nov 2002 01:24:04
|
|
here is the error i get
and here is the error i get
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query
that does not include the specified expression 'id' as part of an
aggregate function.
/fleetshare NEW/diary.asp, line 77
it a bit to explain my db so here is a link to download it.
www.nuvo.biz/clients/fleetshare/db/fleetshare2.mdb
and here is the main part of my code. *** without the html formating its
not important plus i didnt want to code dumb.
here is line 77
Set RS = objConn.execute(sSQL)
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% Option Explicit %>
<!--#include file="connections/connection.asp"-->
<%
Dim RScompdetail, RSleginstance, objConn, compid, RecordSet, RSvehicle,
vreg, rs, Post_Code, id, RecordSet1, RSadd, num, sSQL, expr1
id=session ("id")
Set RecordSet = objConn.execute("SELECT details.* From details WHERE
details.id = "&id&"")
Set RecordSet1 = objConn.execute("SELECT details.* From details WHERE
details.id <>" &id&"")
sSQL = "SELECT details.*, items.*, Sum(items.size)AS expr1 FROM details,
items WHERE items.truck_id=details.truck_id AND details.id = "&id&""
%>
<%
While NOT RecordSet.EOF
Set RS = objConn.execute(sSQL)
Response.Write("<table width=100% border=0 align=center cellpadding=10
cellspacing=0>")
Response.Write("<tr bgcolor=#CCCC99>")
if RecordSet.Fields("flagged")="Yes" then Response.Write(" <td
width=40 rowspan=2 align=center><a href=flagstat.asp?flag=No&get_id="&
RecordSet.Fields("det_id")&"><img src=img/flagsm.gif border=0></a></td>")
else Response.Write("<td width=40 rowspan=2><a href=flagstat.asp?
flag=Yes&get_id="& RecordSet.Fields("det_id")&">flag This</a></td>")
Response.Write(" <td width=40 rowspan=2 align=center><a
href=manifest.asp?truck_id="& RecordSet.Fields("truck_id")&"><img
src=img/manifest1.gif border=0></a></td>" )
Response.Write(" <td width=98 rowspan=2 align=center>" &
RecordSet.Fields("uplift_location") & "</td>")
Response.Write(" <td width=38 rowspan=2 align=center>" &
RecordSet.Fields("uplift_date") & "</td>")
Response.Write(" <td width=66 rowspan=2 align=center>" &
RecordSet.Fields("destination_location") & "</td>" )
Response.Write(" <td width=41 rowspan=2 align=center>" &
RecordSet.Fields("destination_date") & "</td>" )
While NOT RS.EOF
Response.Write(" <td width=50 rowspan=2 align=center>" & RS(0) &"</td>" )
RS.MoveNext
WEND
Response.Write(" <td width=54 rowspan=2 align=center></td>" )
Response.Write(" <td width=46 rowspan=2 align=center>" &
RecordSet.Fields("route") & "</td>" )
Response.Write(" <td width=48 rowspan=2 align=center></td>" )
Response.Write(" <td width=54 rowspan=2 align=center></td>" )
Response.Write("</tr>")
Response.Write("</table>")
RecordSet.MoveNext
WEND
%>
<%
RecordSet.Close
Set RecordSet = Nothing
RecordSet1.Close
Set RecordSet1 = Nothing
RS.Close
Set RS = Nothing
%>
driving me crazy please help.
Message #2 by "Ken Schaefer" <ken@a...> on Wed, 20 Nov 2002 12:33:26 +1100
|
|
When you use an aggregate function (like SUM()), you need to group by all
the other fields in your SELECT clause:
Note: that you should *not* use SELECT *
Note: you should not name fields like "ID". If it's a Truck, then call it
TruckID. If it's an Order, call it OrderID and so forth.
Note: make sure you close (and Set = Nothing) you connection object as well!
<%
strSQL = _
"SELECT a.truck_id, a.[ID], Sum(b.Size) " & _
"FROM Items AS a " & _
"INNER JOIN Details AS b " & _
"ON a.truck_ID = b.truck_ID " & _
"WHERE a.[ID] = " & intID & " " & _
"GROUP BY a.truck_id "
Response.Write(strSQL)
%>
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <apalmer@n...>
Subject: [access_asp] error (0x80040E14) access db and relationships please
help
: here is the error i get
:
: and here is the error i get
: Error Type:
: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
: [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query
: that does not include the specified expression 'id' as part of an
: aggregate function.
: /fleetshare NEW/diary.asp, line 77
:
: it a bit to explain my db so here is a link to download it.
: www.nuvo.biz/clients/fleetshare/db/fleetshare2.mdb
:
: and here is the main part of my code. *** without the html formating its
: not important plus i didnt want to code dumb.
:
: here is line 77
: Set RS = objConn.execute(sSQL)
:
: <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
: <% Option Explicit %>
: <!--#include file="connections/connection.asp"-->
: <%
: Dim RScompdetail, RSleginstance, objConn, compid, RecordSet, RSvehicle,
: vreg, rs, Post_Code, id, RecordSet1, RSadd, num, sSQL, expr1
: id=session ("id")
: Set RecordSet = objConn.execute("SELECT details.* From details WHERE
: details.id = "&id&"")
: Set RecordSet1 = objConn.execute("SELECT details.* From details WHERE
: details.id <>" &id&"")
: sSQL = "SELECT details.*, items.*, Sum(items.size)AS expr1 FROM details,
: items WHERE items.truck_id=details.truck_id AND details.id = "&id&""
:
: %>
Message #3 by apalmer@n... on Wed, 20 Nov 2002 02:43:59
|
|
i did what you said but it prints it out like this
http://www.nuvo.biz/clients/fleetshare/index.htm
login with jon and pass = jimbo
> When you use an aggregate function (like SUM()), you need to group by all
the other fields in your SELECT clause:
Note: that you should *not* use SELECT *
Note: you should not name fields like "ID". If it's a Truck, then call it
TruckID. If it's an Order, call it OrderID and so forth.
Note: make sure you close (and Set = Nothing) you connection object as
well!
<%
strSQL = _
"SELECT a.truck_id, a.[ID], Sum(b.Size) " & _
"FROM Items AS a " & _
"INNER JOIN Details AS b " & _
"ON a.truck_ID = b.truck_ID " & _
"WHERE a.[ID] = " & intID & " " & _
"GROUP BY a.truck_id "
Response.Write(strSQL)
%>
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <apalmer@n...>
Subject: [access_asp] error (0x80040E14) access db and relationships please
help
: here is the error i get
:
: and here is the error i get
: Error Type:
: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
: [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query
: that does not include the specified expression 'id' as part of an
: aggregate function.
: /fleetshare NEW/diary.asp, line 77
:
: it a bit to explain my db so here is a link to download it.
: www.nuvo.biz/clients/fleetshare/db/fleetshare2.mdb
:
: and here is the main part of my code. *** without the html formating its
: not important plus i didnt want to code dumb.
:
: here is line 77
: Set RS = objConn.execute(sSQL)
:
: <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
: <% Option Explicit %>
: <!--#include file="connections/connection.asp"-->
: <%
: Dim RScompdetail, RSleginstance, objConn, compid, RecordSet, RSvehicle,
: vreg, rs, Post_Code, id, RecordSet1, RSadd, num, sSQL, expr1
: id=session ("id")
: Set RecordSet = objConn.execute("SELECT details.* From details WHERE
: details.id = "&id&"")
: Set RecordSet1 = objConn.execute("SELECT details.* From details WHERE
: details.id <>" &id&"")
: sSQL = "SELECT details.*, items.*, Sum(items.size)AS expr1 FROM details,
: items WHERE items.truck_id=details.truck_id AND details.id = "&id&""
:
: %>
Message #4 by "Ken Schaefer" <ken@a...> on Wed, 20 Nov 2002 13:55:58 +1100
|
|
Hi,
I do not know what your fields etc are. At the moment I can see that you are
getting a:
<quote>
You tried to execute a query that does not include the specified expression
'ID' as part of an aggregate function
</quote>
error. Please post the SQL statement you are now using.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <apalmer@n...>
Subject: [access_asp] Re: error (0x80040E14) access db and relationships
please help
: i did what you said but it prints it out like this
: http://www.nuvo.biz/clients/fleetshare/index.htm
: login with jon and pass = jimbo
Message #5 by apalmer@n... on Wed, 20 Nov 2002 03:15:08
|
|
here is the sql im using <%
Set strSQL = objConn.execute("SELECT a.truck_id, a.[ID], Sum(b.Size) " & _
"FROM Items AS a " & _
"INNER JOIN Details AS b " & _
"ON a.truck_ID = b.truck_ID " & _
"WHERE a.[ID] = " & id & " " & _
"GROUP BY a.truck_id ")
Response.Write(strSQL)
%>
if you need to see all my code tell us
> here is the error i get
> and here is the error i get
E> rror Type:
M> icrosoft OLE DB Provider for ODBC Drivers (0x80040E14)
[> Microsoft][ODBC Microsoft Access Driver] You tried to execute a query
t> hat does not include the specified expression 'id' as part of an
a> ggregate function.
/> fleetshare NEW/diary.asp, line 77
> it a bit to explain my db so here is a link to download it.
w> ww.nuvo.biz/clients/fleetshare/db/fleetshare2.mdb
> and here is the main part of my code. *** without the html formating its
n> ot important plus i didnt want to code dumb.
> here is line 77
S> et RS = objConn.execute(sSQL)
> <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<> % Option Explicit %>
<> !--#include file="connections/connection.asp"-->
<> %
D> im RScompdetail, RSleginstance, objConn, compid, RecordSet, RSvehicle,
v> reg, rs, Post_Code, id, RecordSet1, RSadd, num, sSQL, expr1
i> d=session ("id")
S> et RecordSet = objConn.execute("SELECT details.* From details WHERE
d> etails.id = "&id&"")
S> et RecordSet1 = objConn.execute("SELECT details.* From details WHERE
d> etails.id <>" &id&"")
s> SQL = "SELECT details.*, items.*, Sum(items.size)AS expr1 FROM details,
i> tems WHERE items.truck_id=details.truck_id AND details.id = "&id&""
> %>
<> %
W> hile NOT RecordSet.EOF
S> et RS = objConn.execute(sSQL)
> Response.Write("<table width=100% border=0 align=center
cellpadding=10
c> ellspacing=0>")
> Response.Write("<tr bgcolor=#CCCC99>")
> if RecordSet.Fields("flagged")="Yes" then Response.Write(" <td
w> idth=40 rowspan=2 align=center><a href=flagstat.asp?flag=No&get_id="&
R> ecordSet.Fields("det_id")&"><img src=img/flagsm.gif
border=0></a></td>")
e> lse Response.Write("<td width=40 rowspan=2><a href=flagstat.asp?
f> lag=Yes&get_id="& RecordSet.Fields("det_id")&">flag This</a></td>")
> Response.Write(" <td width=40 rowspan=2 align=center><a
h> ref=manifest.asp?truck_id="& RecordSet.Fields("truck_id")&"><img
s> rc=img/manifest1.gif border=0></a></td>" )
> Response.Write(" <td width=98 rowspan=2 align=center>" &
R> ecordSet.Fields("uplift_location") & "</td>")
> Response.Write(" <td width=38 rowspan=2 align=center>" &
R> ecordSet.Fields("uplift_date") & "</td>")
> Response.Write(" <td width=66 rowspan=2 align=center>" &
R> ecordSet.Fields("destination_location") & "</td>" )
> Response.Write(" <td width=41 rowspan=2 align=center>" &
R> ecordSet.Fields("destination_date") & "</td>" )
W> hile NOT RS.EOF
R> esponse.Write(" <td width=50 rowspan=2 align=center>" & RS(0)
&"</td>" )
R> S.MoveNext
W> END
> Response.Write(" <td width=54 rowspan=2 align=center></td>" )
> Response.Write(" <td width=46 rowspan=2 align=center>" &
R> ecordSet.Fields("route") & "</td>" )
> Response.Write(" <td width=48 rowspan=2 align=center></td>" )
> Response.Write(" <td width=54 rowspan=2 align=center></td>" )
> Response.Write("</tr>")
R> esponse.Write("</table>")
R> ecordSet.MoveNext
W> END
%> >
<> %
R> ecordSet.Close
S> et RecordSet = Nothing
R> ecordSet1.Close
S> et RecordSet1 = Nothing
R> S.Close
S> et RS = Nothing
%> >
> driving me crazy please help.
Message #6 by apalmer@n... on Wed, 20 Nov 2002 05:39:46
|
|
and here is the code you helped me with befor
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% Option Explicit %>
<!--#include file="connections/connection.asp"-->
<%
Dim RScompdetail, RSleginstance, objConn, compid, RecordSet, RSvehicle,
vreg, rs, Post_Code, id, RecordSet1, RSadd, num, sSQL, expr1, strSQL, intID
id=session ("id")
Set RecordSet = objConn.execute("SELECT details.* From details WHERE
details.id = "&id&"")
Set RecordSet1 = objConn.execute("SELECT details.* From details WHERE
details.id <>" &id&"")
'sSQL = "SELECT details.*, items.*, Sum(items.size)AS expr1 FROM details,
items WHERE details.truck_id=items.truck_id AND details.id = "&id&""
'sSQL = "SELECT Sum(size)AS expr1 FROM items "
'strSQL ="SELECT a.truck_id, a.[ID], Sum(b.size) FROM items AS a INNER
JOIN details AS b ON a.truck_id = b.truck_id WHERE a.[ID] = " & id & "
GROUP BY a.truck_id "
%>
<%
Set strSQL = objConn.execute("SELECT a.truck_id, b.id, Sum(a.size) " & _
"FROM items AS a " & _
"INNER JOIN details AS b " & _
"ON a.truck_id = b.truck_id " & _
"WHERE b.id = " & id & " " & _
"GROUP BY a.truck_id, b.id")
%>
here is where the response write is
<%
While NOT RecordSet.EOF
Response.Write(strSQL)
Response.Write("<table width=100% border=0 align=center cellpadding=10
cellspacing=0>")
Response.Write("<tr bgcolor=#CCCC99>")
if RecordSet.Fields("flagged")="Yes" then Response.Write(" <td
width=40 rowspan=2 align=center><a href=flagstat.asp?flag=No&get_id="&
RecordSet.Fields("det_id")&"><img src=img/flagsm.gif border=0></a></td>")
else Response.Write("<td width=40 rowspan=2><a href=flagstat.asp?
flag=Yes&get_id="& RecordSet.Fields("det_id")&">flag This</a></td>")
Response.Write(" <td width=40 rowspan=2 align=center><a
href=javascript:; onClick=MM_openBrWindow('manifest.asp?truck_id="&
RecordSet.Fields("truck_id")&"','something','width=800,height=500')><img
src=img/manifest1.gif border=0></a></td>" )
Response.Write(" <td width=98 rowspan=2 align=center>" &
RecordSet.Fields("uplift_location") & "</td>")
Response.Write(" <td width=38 rowspan=2 align=center>" &
RecordSet.Fields("uplift_date") & "</td>")
Response.Write(" <td width=66 rowspan=2 align=center>" &
RecordSet.Fields("destination_location") & "</td>" )
Response.Write(" <td width=41 rowspan=2 align=center>" &
RecordSet.Fields("destination_date") & "</td>" )
Response.Write(" <td width=50 rowspan=2 align=center></td>" )
Response.Write(" <td width=54 rowspan=2 align=center></td>" )
Response.Write(" <td width=46 rowspan=2 align=center>" &
RecordSet.Fields("route") & "</td>" )
Response.Write(" <td width=48 rowspan=2 align=center></td>" )
Response.Write(" <td width=54 rowspan=2 align=center></td>" )
Response.Write("</tr>")
Response.Write("</table>")
RecordSet.MoveNext
WEND
%>
and here is the error there is no line code given just the error
Response object error 'ASP 0185 : 8002000e'
Missing Default Property
?
A default property was not found for the object.
go here www.nuvo.biz/clients/fleetshare/index.htm
login with jon
pass jimbo
Message #7 by "Ken Schaefer" <ken@a...> on Wed, 20 Nov 2002 16:49:03 +1100
|
|
Hi there,
The only thing I can see that is missing is an End If before the Wend
Is there any other code on the page?
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <apalmer@n...>
Subject: [access_asp] Re: error (0x80040E14) access db and relationships
please help
: and here is the code you helped me with befor
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #8 by apalmer@n... on Wed, 20 Nov 2002 06:06:25
|
|
here is the entire cod for the page
there is a login page befor this which sets the session id
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% Option Explicit %>
<!--#include file="connections/connection.asp"-->
<%
Dim RScompdetail, RSleginstance, objConn, compid, RecordSet, RSvehicle,
vreg, rs, Post_Code, id, RecordSet1, RSadd, num, sSQL, expr1, strSQL, intID
id=session ("id")
Set RecordSet = objConn.execute("SELECT details.* From details WHERE
details.id = "&id&"")
Set RecordSet1 = objConn.execute("SELECT details.* From details WHERE
details.id <>" &id&"")
'sSQL = "SELECT details.*, items.*, Sum(items.size)AS expr1 FROM details,
items WHERE details.truck_id=items.truck_id AND details.id = "&id&""
'sSQL = "SELECT Sum(size)AS expr1 FROM items "
'strSQL ="SELECT a.truck_id, a.[ID], Sum(b.size) FROM items AS a INNER
JOIN details AS b ON a.truck_id = b.truck_id WHERE a.[ID] = " & id & "
GROUP BY a.truck_id "
%>
<%
Set strSQL = objConn.execute("SELECT a.truck_id, b.id, Sum(a.size) " & _
"FROM items AS a " & _
"INNER JOIN details AS b " & _
"ON a.truck_id = b.truck_id " & _
"WHERE b.id = " & id & " " & _
"GROUP BY a.truck_id, b.id")
%>
<html>
<head>
<title>Fleetshare</title>
<meta http-equiv="description" content="">
<meta http-equiv="keywords" content="">
<META name="description" content="">
<META name="keywords" content="">
<META name="robots" content="all">
<meta HTTP-EQUIV="DC.Creator.CorporateName" CONTENT="Fleetshare">
<meta HTTP-EQUIV="DC.Creator.Jurisdiction" CONTENT="Australia">
<meta HTTP-EQUIV="DC.Publisher" CONTENT="Copyright 2002 Fleetshare">
<meta HTTP-EQUIV="DC.Title" CONTENT="Main">
<meta HTTP-EQUIV="Keywords" SCHEME="Default Keywords" CONTENT="">
<meta name="author" content="Nuvo Group www.nuvo.biz">
<meta http-equiv="Content-Language" content="EN">
<meta name="Revisit-After" content="35 days">
<meta HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<link href="master.css" rel="stylesheet" type="text/css">
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_openBrWindow(theURL,winName,features) { //v2.0
window.open(theURL,winName,features);
}
//-->
</script>
</head>
<body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<table width="90%" height="65" border="0" align="center" cellpadding="10"
cellspacing="0">
<tr>
<td align="left" valign="top"><table width="283" border="0"
cellspacing="0" cellpadding="10">
<tr>
<td>Legend</td>
<td class="bodybold">Edit Manifest</td>
<td> </td>
<td class="bodybold">View Summary</td>
</tr>
</table></td>
</tr>
<tr>
<td align="center" valign="top"><table width="100%" border="0"
align="center" cellpadding="10" cellspacing="0">
<tr>
<td width="40" rowspan="2" align="center" valign="middle"
bgcolor="#F2F1E0" class="bodybold"><img src="img/alert.gif" width="40"
height="36">
</td>
<td width="40" rowspan="2" align="center" valign="middle"
bgcolor="#F2F1E0" class="bodybold"><img src="img/info.gif" width="40"
height="35" border="0"></td>
<td height="34" colspan="2" align="center" valign="top"
bgcolor="#F2F1E0" class="bodybold">Uplift</td>
<td colspan="2" align="center" valign="top" bgcolor="#F2F1E0"
class="bodybold">Destination</td>
<td width="50" rowspan="2" align="center" valign="middle"
bgcolor="#F2F1E0" class="bodybold">Move
<br>
M³</td>
<td width="54" rowspan="2" align="center" valign="middle"
bgcolor="#F2F1E0" class="bodybold"><font face="Verdana, Arial, Helvetica,
sans-serif">Space
<br>
M³</font></td>
<td width="46" rowspan="2" align="center" valign="middle"
bgcolor="#F2F1E0" class="bodybold">Route</td>
<td width="48" rowspan="2" align="center" valign="top"
bgcolor="#F2F1E0" class="bodybold">Fill
It<br>
<br>
<img src="img/fill.gif" width="40" height="25"> </td>
<td width="54" rowspan="2" align="center" valign="top"
bgcolor="#F2F1E0" class="bodybold">Onsell<br>
<br>
<img src="img/onsell.gif" width="40" height="28"> </td>
</tr>
<tr>
<td width="98" align="center" valign="top" bgcolor="#F2F1E0"
class="bodybold">Location</td>
<td width="38" align="center" valign="top" bgcolor="#F2F1E0"
class="bodybold">Date</td>
<td width="66" align="center" valign="top" bgcolor="#F2F1E0"
class="bodybold">Location</td>
<td width="41" align="center" valign="top" bgcolor="#F2F1E0"
class="bodybold">Date</td>
</tr>
</table>
<%
While NOT RecordSet.EOF
Response.Write(strSQL)
Response.Write("<table width=100% border=0 align=center cellpadding=10
cellspacing=0>")
Response.Write("<tr bgcolor=#CCCC99>")
if RecordSet.Fields("flagged")="Yes" then Response.Write(" <td
width=40 rowspan=2 align=center><a href=flagstat.asp?flag=No&get_id="&
RecordSet.Fields("det_id")&"><img src=img/flagsm.gif border=0></a></td>")
else Response.Write("<td width=40 rowspan=2><a href=flagstat.asp?
flag=Yes&get_id="& RecordSet.Fields("det_id")&">flag This</a></td>")
Response.Write(" <td width=40 rowspan=2 align=center><a
href=javascript:; onClick=MM_openBrWindow('manifest.asp?truck_id="&
RecordSet.Fields("truck_id")&"','something','width=800,height=500')><img
src=img/manifest1.gif border=0></a></td>" )
Response.Write(" <td width=98 rowspan=2 align=center>" &
RecordSet.Fields("uplift_location") & "</td>")
Response.Write(" <td width=38 rowspan=2 align=center>" &
RecordSet.Fields("uplift_date") & "</td>")
Response.Write(" <td width=66 rowspan=2 align=center>" &
RecordSet.Fields("destination_location") & "</td>" )
Response.Write(" <td width=41 rowspan=2 align=center>" &
RecordSet.Fields("destination_date") & "</td>" )
Response.Write(" <td width=50 rowspan=2 align=center></td>" )
Response.Write(" <td width=54 rowspan=2 align=center></td>" )
Response.Write(" <td width=46 rowspan=2 align=center>" &
RecordSet.Fields("route") & "</td>" )
Response.Write(" <td width=48 rowspan=2 align=center></td>" )
Response.Write(" <td width=54 rowspan=2 align=center></td>" )
Response.Write("</tr>")
Response.Write("</table>")
RecordSet.MoveNext
WEND
%>
<%
While NOT RecordSet1.EOF
Response.Write("<table width=100% border=0 align=center cellpadding=10
cellspacing=0>")
Response.Write("<tr bgcolor=#F2F1E0>")
if RecordSet1.Fields("flagged")="Yes" then Response.Write(" <td
width=40 rowspan=2 align=center><a href=flagstat.asp?flag=No&get_id="&
RecordSet1.Fields("det_id")&"><img src=img/flag2.gif border=0></a></td>")
else Response.Write("<td width=40 rowspan=2><a href=flagstat.asp?
flag=Yes&get_id="& RecordSet1.Fields("det_id")&">flag This</a></td>")
Response.Write(" <td width=40 rowspan=2 align=center><a
href=javascript:; onClick=MM_openBrWindow('manifest1.asp?truck_id="&
RecordSet1.Fields("truck_id")&"','something','width=800,height=500')><img
src=img/vs.gif border=0></a></td>" )
Response.Write(" <td width=98 rowspan=2 align=center>" &
RecordSet1.Fields("uplift_location") & "</td>")
Response.Write(" <td width=38 rowspan=2 align=center>" &
RecordSet1.Fields("uplift_date") & "</td>")
Response.Write(" <td width=66 rowspan=2 align=center>" &
RecordSet1.Fields("destination_location") & "</td>" )
Response.Write(" <td width=41 rowspan=2 align=center>" &
RecordSet1.Fields("destination_date") & "</td>" )
Response.Write(" <td width=50 rowspan=2 align=center></td>" )
Response.Write(" <td width=54 rowspan=2 align=center></td>" )
Response.Write(" <td width=46 rowspan=2 align=center>" &
RecordSet1.Fields("route") & "</td>" )
Response.Write(" <td width=48 rowspan=2 align=center></td>" )
Response.Write(" <td width=54 rowspan=2 align=center></td>" )
Response.Write("</tr>")
Response.Write("</table>")
RecordSet1.MoveNext
WEND
%>
</td>
</tr>
</table>
</body>
</html>
<%
RecordSet.Close
Set RecordSet = Nothing
RecordSet1.Close
Set RecordSet1 = Nothing
%>
Message #9 by "Ken Schaefer" <ken@a...> on Wed, 20 Nov 2002 17:15:26 +1100
|
|
OK, I know why.
You are doing:
<%
Set strSQL = objConn.Execute("....")
%>
this means that strSQL is reference to a *recordset* object. Then later on
you are doing:
<%
Response.Write(strSQL)
%>
Now, there if no default property of the Recordset object, so you can't
Response.Write() it. Instead, what you want to do is change the first line
of code above to read:
<%
strSQL = "SELECT a.truck_id, b.id, Sum(a.size) " & _
"FROM items AS a " & _
"INNER JOIN details AS b " & _
"ON a.truck_id = b.truck_id " & _
"WHERE b.id = " & id & " " & _
"GROUP BY a.truck_id, b.id"
Response.Write(strSQL)
Set objRS = objConn.Execute(strSQL)
%>
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <apalmer@n...>
Subject: [access_asp] Re: error (0x80040E14) access db and relationships
please help
: here is the entire cod for the page
:
: there is a login page befor this which sets the session id
:
: <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
: <% Option Explicit %>
: <!--#include file="connections/connection.asp"-->
: <%
: Dim RScompdetail, RSleginstance, objConn, compid, RecordSet, RSvehicle,
: vreg, rs, Post_Code, id, RecordSet1, RSadd, num, sSQL, expr1, strSQL,
intID
: id=session ("id")
: Set RecordSet = objConn.execute("SELECT details.* From details WHERE
: details.id = "&id&"")
: Set RecordSet1 = objConn.execute("SELECT details.* From details WHERE
: details.id <>" &id&"")
: 'sSQL = "SELECT details.*, items.*, Sum(items.size)AS expr1 FROM details,
: items WHERE details.truck_id=items.truck_id AND details.id = "&id&""
: 'sSQL = "SELECT Sum(size)AS expr1 FROM items "
: 'strSQL ="SELECT a.truck_id, a.[ID], Sum(b.size) FROM items AS a INNER
: JOIN details AS b ON a.truck_id = b.truck_id WHERE a.[ID] = " & id & "
: GROUP BY a.truck_id "
: %>
: <%
: Set strSQL = objConn.execute("SELECT a.truck_id, b.id, Sum(a.size) " & _
: "FROM items AS a " & _
: "INNER JOIN details AS b " & _
: "ON a.truck_id = b.truck_id " & _
: "WHERE b.id = " & id & " " & _
: "GROUP BY a.truck_id, b.id")
:
: %>
:
: <html>
: <head>
: <title>Fleetshare</title>
: <meta http-equiv="description" content="">
: <meta http-equiv="keywords" content="">
: <META name="description" content="">
: <META name="keywords" content="">
: <META name="robots" content="all">
: <meta HTTP-EQUIV="DC.Creator.CorporateName" CONTENT="Fleetshare">
: <meta HTTP-EQUIV="DC.Creator.Jurisdiction" CONTENT="Australia">
: <meta HTTP-EQUIV="DC.Publisher" CONTENT="Copyright 2002 Fleetshare">
: <meta HTTP-EQUIV="DC.Title" CONTENT="Main">
: <meta HTTP-EQUIV="Keywords" SCHEME="Default Keywords" CONTENT="">
: <meta name="author" content="Nuvo Group www.nuvo.biz">
: <meta http-equiv="Content-Language" content="EN">
: <meta name="Revisit-After" content="35 days">
: <meta HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
: <link href="master.css" rel="stylesheet" type="text/css">
: <script language="JavaScript" type="text/JavaScript">
: <!--
: function MM_openBrWindow(theURL,winName,features) { //v2.0
: window.open(theURL,winName,features);
: }
: //-->
: </script>
: </head>
:
: <body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
: <table width="90%" height="65" border="0" align="center" cellpadding="10"
: cellspacing="0">
: <tr>
: <td align="left" valign="top"><table width="283" border="0"
: cellspacing="0" cellpadding="10">
: <tr>
: <td>Legend</td>
: <td class="bodybold">Edit Manifest</td>
: <td> </td>
: <td class="bodybold">View Summary</td>
: </tr>
: </table></td>
: </tr>
: <tr>
: <td align="center" valign="top"><table width="100%" border="0"
: align="center" cellpadding="10" cellspacing="0">
: <tr>
: <td width="40" rowspan="2" align="center" valign="middle"
: bgcolor="#F2F1E0" class="bodybold"><img src="img/alert.gif" width="40"
: height="36">
: </td>
: <td width="40" rowspan="2" align="center" valign="middle"
: bgcolor="#F2F1E0" class="bodybold"><img src="img/info.gif" width="40"
: height="35" border="0"></td>
: <td height="34" colspan="2" align="center" valign="top"
: bgcolor="#F2F1E0" class="bodybold">Uplift</td>
: <td colspan="2" align="center" valign="top" bgcolor="#F2F1E0"
: class="bodybold">Destination</td>
: <td width="50" rowspan="2" align="center" valign="middle"
: bgcolor="#F2F1E0" class="bodybold">Move
: <br>
: M³</td>
: <td width="54" rowspan="2" align="center" valign="middle"
: bgcolor="#F2F1E0" class="bodybold"><font face="Verdana, Arial, Helvetica,
: sans-serif">Space
: <br>
: M³</font></td>
: <td width="46" rowspan="2" align="center" valign="middle"
: bgcolor="#F2F1E0" class="bodybold">Route</td>
: <td width="48" rowspan="2" align="center" valign="top"
: bgcolor="#F2F1E0" class="bodybold">Fill
: It<br>
: <br>
: <img src="img/fill.gif" width="40" height="25"> </td>
: <td width="54" rowspan="2" align="center" valign="top"
: bgcolor="#F2F1E0" class="bodybold">Onsell<br>
: <br>
: <img src="img/onsell.gif" width="40" height="28"> </td>
: </tr>
: <tr>
: <td width="98" align="center" valign="top" bgcolor="#F2F1E0"
: class="bodybold">Location</td>
: <td width="38" align="center" valign="top" bgcolor="#F2F1E0"
: class="bodybold">Date</td>
: <td width="66" align="center" valign="top" bgcolor="#F2F1E0"
: class="bodybold">Location</td>
: <td width="41" align="center" valign="top" bgcolor="#F2F1E0"
: class="bodybold">Date</td>
: </tr>
: </table>
: <%
: While NOT RecordSet.EOF
: Response.Write(strSQL)
: Response.Write("<table width=100% border=0 align=center cellpadding=10
: cellspacing=0>")
: Response.Write("<tr bgcolor=#CCCC99>")
: if RecordSet.Fields("flagged")="Yes" then Response.Write(" <td
: width=40 rowspan=2 align=center><a href=flagstat.asp?flag=No&get_id="&
: RecordSet.Fields("det_id")&"><img src=img/flagsm.gif border=0></a></td>")
: else Response.Write("<td width=40 rowspan=2><a href=flagstat.asp?
: flag=Yes&get_id="& RecordSet.Fields("det_id")&">flag This</a></td>")
: Response.Write(" <td width=40 rowspan=2 align=center><a
: href=javascript:; onClick=MM_openBrWindow('manifest.asp?truck_id="&
: RecordSet.Fields("truck_id")&"','something','width=800,height=500')><img
: src=img/manifest1.gif border=0></a></td>" )
: Response.Write(" <td width=98 rowspan=2 align=center>" &
: RecordSet.Fields("uplift_location") & "</td>")
: Response.Write(" <td width=38 rowspan=2 align=center>" &
: RecordSet.Fields("uplift_date") & "</td>")
: Response.Write(" <td width=66 rowspan=2 align=center>" &
: RecordSet.Fields("destination_location") & "</td>" )
: Response.Write(" <td width=41 rowspan=2 align=center>" &
: RecordSet.Fields("destination_date") & "</td>" )
: Response.Write(" <td width=50 rowspan=2 align=center></td>" )
: Response.Write(" <td width=54 rowspan=2 align=center></td>" )
: Response.Write(" <td width=46 rowspan=2 align=center>" &
: RecordSet.Fields("route") & "</td>" )
: Response.Write(" <td width=48 rowspan=2 align=center></td>" )
: Response.Write(" <td width=54 rowspan=2 align=center></td>" )
: Response.Write("</tr>")
: Response.Write("</table>")
: RecordSet.MoveNext
: WEND
: %>
:
: <%
: While NOT RecordSet1.EOF
: Response.Write("<table width=100% border=0 align=center cellpadding=10
: cellspacing=0>")
: Response.Write("<tr bgcolor=#F2F1E0>")
: if RecordSet1.Fields("flagged")="Yes" then Response.Write(" <td
: width=40 rowspan=2 align=center><a href=flagstat.asp?flag=No&get_id="&
: RecordSet1.Fields("det_id")&"><img src=img/flag2.gif border=0></a></td>")
: else Response.Write("<td width=40 rowspan=2><a href=flagstat.asp?
: flag=Yes&get_id="& RecordSet1.Fields("det_id")&">flag This</a></td>")
: Response.Write(" <td width=40 rowspan=2 align=center><a
: href=javascript:; onClick=MM_openBrWindow('manifest1.asp?truck_id="&
: RecordSet1.Fields("truck_id")&"','something','width=800,height=500')><img
: src=img/vs.gif border=0></a></td>" )
: Response.Write(" <td width=98 rowspan=2 align=center>" &
: RecordSet1.Fields("uplift_location") & "</td>")
: Response.Write(" <td width=38 rowspan=2 align=center>" &
: RecordSet1.Fields("uplift_date") & "</td>")
: Response.Write(" <td width=66 rowspan=2 align=center>" &
: RecordSet1.Fields("destination_location") & "</td>" )
: Response.Write(" <td width=41 rowspan=2 align=center>" &
: RecordSet1.Fields("destination_date") & "</td>" )
: Response.Write(" <td width=50 rowspan=2 align=center></td>" )
: Response.Write(" <td width=54 rowspan=2 align=center></td>" )
: Response.Write(" <td width=46 rowspan=2 align=center>" &
: RecordSet1.Fields("route") & "</td>" )
: Response.Write(" <td width=48 rowspan=2 align=center></td>" )
: Response.Write(" <td width=54 rowspan=2 align=center></td>" )
: Response.Write("</tr>")
: Response.Write("</table>")
: RecordSet1.MoveNext
: WEND
: %>
:
: </td>
: </tr>
: </table>
: </body>
: </html>
: <%
: RecordSet.Close
: Set RecordSet = Nothing
: RecordSet1.Close
: Set RecordSet1 = Nothing
: %>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
|
View Cart
