|
 |
access_asp thread: Query works in access but returns no records from asp
Message #1 by "Patrick Anigbo" <dirosky@h...> on Thu, 18 Jul 2002 11:39:23
|
|
Hi all,
I have a query in access that works fine and returns a record but when I
pass the parameters from asp, it does not return any records. I have
checked that it is passing the right parameters and it is. Can't figure
out what is going on.
<B><U>Access query:</B></U>
SELECT (AgencyCostMonth!SumOfCost)+(PermCostsMonth!SumOfTotalCost)+
(ReliefStaffCost!SumOfTotalCost) AS Total, AgencyCostMonth.SumOfCost AS
TAgency, PermCostsMonth.SumOfTotalCost AS TPerm,
ReliefStaffCost.SumOfTotalCost AS TRelief
FROM AgencyCostMonth, PermCostsMonth, ReliefStaffCost;
<B><U>ASP Code:</B></U>
Dim objR, ObjCom
Set ObjCom = Server.CreateObject("ADODB.Command")
With ObjCom
.ActiveConnection = objConn5
.CommandType = adCmdStoredProc
.CommandText = "GrandTotalQry"
.Parameters.Append .CreateParameter("param1", advarchar,
adParamInput, 50, Session("Service"))
.Parameters.Append .CreateParameter("param2", advarchar,
adParamInput, 50, Request("Month"))
.Parameters.Append .CreateParameter("param3", advarchar,
adParamInput, 50, Request("Year"))
End With
Set objR = ObjCom.Execute
If Not objR.EOF Then
Response.Write _
"<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3""
ALIGN=""RIGHT"">" & _
" <TR>" & _
" <TH WIDTH=120>Grand Total</TH>" & _
" <TH WIDTH=100>Total for Permanent Staff</TH>" & _
" <TH WIDTH=100>Total for Relief Staff</TH>" & _
" <TH WIDTH=100>Total for Agency Staff</TH>" & _
" </TR>"
Do While Not objR.EOF
Response.Write "<TR ALIGN=CENTER>"
Response.Write _
"<TD>"&objR("Total")&"</TD>" & _
"<TD>"&objR("TPerm")&"</TD>" & _
"<TD>"&objR("TRelief")&"</TD>" & _
"<TD>"&objR("TAgency")&"</TD>" & _
"</TR>"
objR.MoveNext
Loop
Response.Write "</TABLE>"
objR.close
Set objR = Nothing
Else
Response.Write "<CENTER><H2>There are no costs for your
selection</H2></CENTER>"
Response.Write "<CENTER><H2>Service:"&Session("Service")
&"<BR>Month:"&Request("Month")&"<BR>Year:"&Request("Year")
&"</H2></CENTER>"
End If
Message #2 by "Ken Schaefer" <ken@a...> on Fri, 19 Jul 2002 12:54:12 +1000
|
|
You need to use ANSI SQL from ASP - what type of JOIN do you want to do
between the tables specified in the FROM clause?
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Patrick Anigbo" <dirosky@h...>
Subject: [access_asp] Query works in access but returns no records from asp
: I have a query in access that works fine and returns a record but when I
: pass the parameters from asp, it does not return any records. I have
: checked that it is passing the right parameters and it is. Can't figure
: out what is going on.
:
: <B><U>Access query:</B></U>
:
: SELECT (AgencyCostMonth!SumOfCost)+(PermCostsMonth!SumOfTotalCost)+
: (ReliefStaffCost!SumOfTotalCost) AS Total, AgencyCostMonth.SumOfCost AS
: TAgency, PermCostsMonth.SumOfTotalCost AS TPerm,
: ReliefStaffCost.SumOfTotalCost AS TRelief
: FROM AgencyCostMonth, PermCostsMonth, ReliefStaffCost;
:
: <B><U>ASP Code:</B></U>
:
: Dim objR, ObjCom
:
: Set ObjCom = Server.CreateObject("ADODB.Command")
: With ObjCom
: .ActiveConnection = objConn5
: .CommandType = adCmdStoredProc
: .CommandText = "GrandTotalQry"
: .Parameters.Append .CreateParameter("param1", advarchar,
: adParamInput, 50, Session("Service"))
: .Parameters.Append .CreateParameter("param2", advarchar,
: adParamInput, 50, Request("Month"))
: .Parameters.Append .CreateParameter("param3", advarchar,
: adParamInput, 50, Request("Year"))
: End With
:
: Set objR = ObjCom.Execute
:
: If Not objR.EOF Then
: Response.Write _
: "<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3""
: ALIGN=""RIGHT"">" & _
: " <TR>" & _
: " <TH WIDTH=120>Grand Total</TH>" & _
: " <TH WIDTH=100>Total for Permanent Staff</TH>" & _
: " <TH WIDTH=100>Total for Relief Staff</TH>" & _
: " <TH WIDTH=100>Total for Agency Staff</TH>" & _
: " </TR>"
: Do While Not objR.EOF
: Response.Write "<TR ALIGN=CENTER>"
: Response.Write _
: "<TD>"&objR("Total")&"</TD>" & _
: "<TD>"&objR("TPerm")&"</TD>" & _
: "<TD>"&objR("TRelief")&"</TD>" & _
: "<TD>"&objR("TAgency")&"</TD>" & _
: "</TR>"
: objR.MoveNext
: Loop
: Response.Write "</TABLE>"
: objR.close
: Set objR = Nothing
: Else
: Response.Write "<CENTER><H2>There are no costs for your
: selection</H2></CENTER>"
: Response.Write "<CENTER><H2>Service:"&Session("Service")
: &"<BR>Month:"&Request("Month")&"<BR>Year:"&Request("Year")
: &"</H2></CENTER>"
: End If
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #3 by "Patrick Anigbo" <dirosky@h...> on Fri, 19 Jul 2002 09:27:02
|
|
> You need to use ANSI SQL from ASP - what type of JOIN do you want to do
between the tables specified in the FROM clause?
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Patrick Anigbo" <dirosky@h...>
Subject: [access_asp] Query works in access but returns no records from
asp
: I have a query in access that works fine and returns a record but when I
: pass the parameters from asp, it does not return any records. I have
: checked that it is passing the right parameters and it is. Can't figure
: out what is going on.
:
: <B><U>Access query:</B></U>
:
: SELECT (AgencyCostMonth!SumOfCost)+(PermCostsMonth!SumOfTotalCost)+
: (ReliefStaffCost!SumOfTotalCost) AS Total, AgencyCostMonth.SumOfCost AS
: TAgency, PermCostsMonth.SumOfTotalCost AS TPerm,
: ReliefStaffCost.SumOfTotalCost AS TRelief
: FROM AgencyCostMonth, PermCostsMonth, ReliefStaffCost;
:
: <B><U>ASP Code:</B></U>
:
: Dim objR, ObjCom
:
: Set ObjCom = Server.CreateObject("ADODB.Command")
: With ObjCom
: .ActiveConnection = objConn5
: .CommandType = adCmdStoredProc
: .CommandText = "GrandTotalQry"
: .Parameters.Append .CreateParameter("param1", advarchar,
: adParamInput, 50, Session("Service"))
: .Parameters.Append .CreateParameter("param2", advarchar,
: adParamInput, 50, Request("Month"))
: .Parameters.Append .CreateParameter("param3", advarchar,
: adParamInput, 50, Request("Year"))
: End With
:
: Set objR = ObjCom.Execute
:
: If Not objR.EOF Then
: Response.Write _
: "<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3""
: ALIGN=""RIGHT"">" & _
: " <TR>" & _
: " <TH WIDTH=120>Grand Total</TH>" & _
: " <TH WIDTH=100>Total for Permanent Staff</TH>" & _
: " <TH WIDTH=100>Total for Relief Staff</TH>" & _
: " <TH WIDTH=100>Total for Agency Staff</TH>" & _
: " </TR>"
: Do While Not objR.EOF
: Response.Write "<TR ALIGN=CENTER>"
: Response.Write _
: "<TD>"&objR("Total")&"</TD>" & _
: "<TD>"&objR("TPerm")&"</TD>" & _
: "<TD>"&objR("TRelief")&"</TD>" & _
: "<TD>"&objR("TAgency")&"</TD>" & _
: "</TR>"
: objR.MoveNext
: Loop
: Response.Write "</TABLE>"
: objR.close
: Set objR = Nothing
: Else
: Response.Write "<CENTER><H2>There are no costs for your
: selection</H2></CENTER>"
: Response.Write "<CENTER><H2>Service:"&Session("Service")
: &"<BR>Month:"&Request("Month")&"<BR>Year:"&Request("Year")
: &"</H2></CENTER>"
: End If
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Hi Ken,
The query in access is based on other queries. Are you suggesting that I
run the ANSI SQL from the asp page?
The query in question:
1. Total: Adds the sum on the totals from AgencyCostMonth,
PermCostsMonth, ReliefStaffCost(queries)
2. The other three fields (i.e. TPerm, TAgency & TRelief) simply sum up
the respective queries; PermCostsMonth, AgencyCostMonth, ReliefStaffCost.
3. Of the three queries in (2) above I am only able to run an asp page
correctly for PermCostsMonth but they all work fine in Access.
Any ideas?
Thanks, Patrick
Message #4 by "Ken Schaefer" <ken@a...> on Mon, 22 Jul 2002 11:51:51 +1000
|
|
Can you the queries individually?
(sorry about my previous post - I didn't realise that you were running
queries that were already inside Access - I though they were tables).
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Patrick Anigbo" <dirosky@h...>
Subject: [access_asp] Re: Query works in access but returns no records from
asp
: > You need to use ANSI SQL from ASP - what type of JOIN do you want to do
: between the tables specified in the FROM clause?
:
: Cheers
: Ken
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: "Patrick Anigbo" <dirosky@h...>
: Subject: [access_asp] Query works in access but returns no records from
: asp
:
:
: : I have a query in access that works fine and returns a record but when I
: : pass the parameters from asp, it does not return any records. I have
: : checked that it is passing the right parameters and it is. Can't figure
: : out what is going on.
: :
: : <B><U>Access query:</B></U>
: :
: : SELECT (AgencyCostMonth!SumOfCost)+(PermCostsMonth!SumOfTotalCost)+
: : (ReliefStaffCost!SumOfTotalCost) AS Total, AgencyCostMonth.SumOfCost AS
: : TAgency, PermCostsMonth.SumOfTotalCost AS TPerm,
: : ReliefStaffCost.SumOfTotalCost AS TRelief
: : FROM AgencyCostMonth, PermCostsMonth, ReliefStaffCost;
: :
: : <B><U>ASP Code:</B></U>
: :
: : Dim objR, ObjCom
: :
: : Set ObjCom = Server.CreateObject("ADODB.Command")
: : With ObjCom
: : .ActiveConnection = objConn5
: : .CommandType = adCmdStoredProc
: : .CommandText = "GrandTotalQry"
: : .Parameters.Append .CreateParameter("param1", advarchar,
: : adParamInput, 50, Session("Service"))
: : .Parameters.Append .CreateParameter("param2", advarchar,
: : adParamInput, 50, Request("Month"))
: : .Parameters.Append .CreateParameter("param3", advarchar,
: : adParamInput, 50, Request("Year"))
: : End With
: :
: : Set objR = ObjCom.Execute
: :
: : If Not objR.EOF Then
: : Response.Write _
: : "<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3""
: : ALIGN=""RIGHT"">" & _
: : " <TR>" & _
: : " <TH WIDTH=120>Grand Total</TH>" & _
: : " <TH WIDTH=100>Total for Permanent Staff</TH>" & _
: : " <TH WIDTH=100>Total for Relief Staff</TH>" & _
: : " <TH WIDTH=100>Total for Agency Staff</TH>" & _
: : " </TR>"
: : Do While Not objR.EOF
: : Response.Write "<TR ALIGN=CENTER>"
: : Response.Write _
: : "<TD>"&objR("Total")&"</TD>" & _
: : "<TD>"&objR("TPerm")&"</TD>" & _
: : "<TD>"&objR("TRelief")&"</TD>" & _
: : "<TD>"&objR("TAgency")&"</TD>" & _
: : "</TR>"
: : objR.MoveNext
: : Loop
: : Response.Write "</TABLE>"
: : objR.close
: : Set objR = Nothing
: : Else
: : Response.Write "<CENTER><H2>There are no costs for your
: : selection</H2></CENTER>"
: : Response.Write "<CENTER><H2>Service:"&Session("Service")
: : &"<BR>Month:"&Request("Month")&"<BR>Year:"&Request("Year")
: : &"</H2></CENTER>"
: : End If
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:
: Hi Ken,
:
: The query in access is based on other queries. Are you suggesting that I
: run the ANSI SQL from the asp page?
:
: The query in question:
: 1. Total: Adds the sum on the totals from AgencyCostMonth,
: PermCostsMonth, ReliefStaffCost(queries)
:
: 2. The other three fields (i.e. TPerm, TAgency & TRelief) simply sum up
: the respective queries; PermCostsMonth, AgencyCostMonth, ReliefStaffCost.
:
: 3. Of the three queries in (2) above I am only able to run an asp page
: correctly for PermCostsMonth but they all work fine in Access.
:
: Any ideas?
:
: Thanks, Patrick
|
|
|
View Cart
