3
I have the following SQL query that keeps giving me a runtime error 800a0401when running through an asp page. The query is below:
SELECT (Sum(Abs(DSum("Cath_type","tbl1a_IC")))/10*100) AS CT_Total, (Sum(Abs(DSum("Insertion_site","tbl1a_IC")))/10*100) AS IS_Total, (Sum(Abs(DSum("Skin_prep","tbl1a_IC")))/10*100) AS SP_Total, (Sum(Abs(DSum("PP_eqip","tbl1a_IC")))/10*100) AS PPE_Total, Sum(Abs(DSum("Hand_hy","tbl1a_IC")))/10*100 AS HH_Total, (Sum(Abs(DSum("Aseptic_tec","tbl1a_IC")))/10*100) AS AAT_Total, (Sum(Abs(DSum("Dressing","tbl1a_IC")))/10*100) AS D_Total, (Sum(Abs(DSum("SDos","tbl1a_IC")))/10*100) AS SDOS_Total, (Sum(Abs(DSum("Doc","tbl1a_IC")))/10*100) AS DOC_Total
FROM tbl1a_IC
GROUP BY tbl1a_IC.OBID, IIf([Dressing]=Yes,"1","0"), IIf([SDoS]=Yes,"1","0"), IIf([Doc]=Yes,"1","0"), IIf([PP_eqip]=Yes,"1","0"), IIf([Hand_hy]=Yes,"1","0"), IIf([Aseptic_tec]=Yes,"1","0"), IIf([Cath_type]=Yes,"1","0"), IIf([Insertion_site]=Yes,"1","0"), IIf([Skin_prep]=Yes,"1","0")
HAVING (((tbl1a_IC.OBID)=1));
This is the page code:
<html>
<head>
<title>test</title>
</head>
<body bgcolor="black" text="white">
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsInfection 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query to query the database
'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("infection.mdb")
'Create an ADO recordset object
Set rsInfection = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT (Sum(Abs(DSum("Cath_type","tbl1a_IC")))/10*100) AS CT_Total, (Sum(Abs(DSum("Insertion_site","tbl1a_IC")))/10*100) AS IS_Total, (Sum(Abs(DSum("Skin_prep","tbl1a_IC")))/10*100) AS SP_Total, (Sum(Abs(DSum("PP_eqip","tbl1a_IC")))/10*100) AS PPE_Total, Sum(Abs(DSum("Hand_hy","tbl1a_IC")))/10*100 AS HH_Total, (Sum(Abs(DSum("Aseptic_tec","tbl1a_IC")))/10*100) AS AAT_Total, (Sum(Abs(DSum("Dressing","tbl1a_IC")))/10*100) AS D_Total, (Sum(Abs(DSum("SDos","tbl1a_IC")))/10*100) AS SDOS_Total, (Sum(Abs(DSum("Doc","tbl1a_IC")))/10*100) AS DOC_Total
FROM tbl1a_IC
GROUP BY tbl1a_IC.OBID, IIf([Dressing]=Yes,"1","0"), IIf([SDoS]=Yes,"1","0"), IIf([Doc]=Yes,"1","0"), IIf([PP_eqip]=Yes,"1","0"), IIf([Hand_hy]=Yes,"1","0"), IIf([Aseptic_tec]=Yes,"1","0"), IIf([Cath_type]=Yes,"1","0"), IIf([Insertion_site]=Yes,"1","0"), IIf([Skin_prep]=Yes,"1","0")
HAVING (((tbl1a_IC.OBID)=1)));"
response.write vbNewLine & strSQL & "<br>" & vbNewLine
'Open the recordset with the SQL query
rsInfection.Open strSQL, adoCon
'Loop through the recordset
Do While not rsInfection.EOF
'Write the HTML to display the current record in the recordset
Response.Write ("<br>")
Response.Write (rsInfection("CT_Total"))
Response.Write ("<br>")
Response.Write (rsInfection("IS_Total"))
Response.Write ("<br>")
Response.Write (rsInfection("SP_Total"))
Response.Write ("<br>")
Response.Write (rsInfection("PPE_Total"))
Response.Write ("<br>")
Response.Write (rsInfection("HH_Total"))
Response.Write ("<br>")
Response.Write (rsInfection("AT_Total"))
Response.Write ("<br>")
Response.Write (rsInfection("D_Total"))
Response.Write ("<br>")
Response.Write (rsInfection("SDOS_Total"))
Response.Write ("<br>")
Response.Write (rsInfection("DOC_Total"))
Response.Write ("<br>")
'Move to the next record in the recordset
rsInfection.MoveNext
Loop
'Reset server objects
rsInfection.Close
Set rsInfection = Nothing
Set adoCon = Nothing
%>
</body>
</html>
Any help you could give me would be much appreciated. Im pretty new to this.
