Skeen
Sep 11th, 2000, 08:23 AM
Hiyas Peeps
Can anyone see why my code ain't workin'? I'm using VBscript in an .asp to retrieve data from a database. Its going to be web enabled, and all a I want to do is reprduce an oracle table, but only the rows that belong to the user. I can insert data into various tables using an asp but the code to view the database keeps falling over. Its somthing to do with dynamic arrays, but I can't find anything wrong with the code! Any info or help would be hugely appreciated.
Cheers n beers.
Code:
<%
Dim i
Dim cnAcqAcc
Dim cmAcqAcc
Dim rsPRS
Dim cmSQL
Dim cmmSQL
Dim sSQL
Dim ssSQL
Dim sssSQL
Dim PRS
Dim CDRs()
Dim CDRs1()
Dim CDRs2()
Dim CDRs3()
Dim CDRs4()
Dim CDRs5()
Dim CDRs6()
Dim CDRs7()
Dim TotTime
Dim TotRev
Dim TotCall
Dim rsCDR
Dim rsTotTime
Dim rsTotRev
Dim rsTotCall
Dim rsID
Dim rsSL
Dim Security
Set cnAcqAcc = CreateObject("ADODB.Connection")
cnAcqAcc.ConnectionString = "Provider=MSDASQL;DSN=AqAc;UID=AcqAcc_owner;PWD=AcqAcc_owner;"
cnAcqAcc.open
Custom_ID = Request.Cookies("cID")
'Response.Write (Custom_ID)
cmSQL = "SELECT PRS_Number FROM Service_Details WHERE Customer_ID = '" & Custom_ID & "' "
Set rsPRS = cnAcqAcc.Execute(cmSQL)
rsPRS.MoveFirst
PRS = rsPRS("PRS_Number")
'Response.Write (PRS)
mmSQL = "SELECT StartDateTime, EndDateTime, DurationSecs, CallersNumber, DialledNumber,TerminatingNumber, ValuePence FROM Opal_Data WHERE TerminatingNumber = '" & PRS & "' "
Set rsCDR = cnAcqAcc.Execute(cmmSQL)
rsCDR.MoveNext
i = 1
While Not rsCDR.EOF
CDRs1(i) = rsCDR.Fields("StartDateTime")
CDRs2(i) = rsCDR.Fields("EndDateTime")
CDRs3(i) = rsCDR.Fields("DurationSecs")
CDRs4(i) = rsCDR.Fields("CallersNumber")
CDRs5(i) = rsCDR.Fields("DialledNumber")
CDRs6(i) = rsCDR.Fields("TerminatingNumber")
CDRs7(i) = rsCDR.Fields("ValuePence")
i = i + 1
Wend
Response.Cookies("cID")= Stored_ID
sSQL = "SELECT SUM(DurationSecs) AS TotalTime FROM Opal_Data WHERE TerminatingNumber = '" & PRS & "' "
Set rsTotTime = cnAcqAcc.Execute(sSQL)
rsTotTime.MoveFirst
TotTime = rsTotTime("TotalTime")
ssSQL = "SELECT SUM(ValuePence) AS TotalRevenue FROM Opal_Data WHERE TerminatingNumber = '" & PRS & "' "
Set rsTotRev = cnAcqAcc.Execute(ssSQL)
rsTotRev.MoveFirst
TotRev = rsTotRev("TotalRevenue")
sssSQL = "SELECT COUNT(ValuePence) AS TotalCalls FROM Opal_Data WHERE TerminatingNumber = '" & PRS & "' "
Set rsTotCall = cnAcqAcc.Execute(sssSQL
rsTotCall.MoveFirst
TotCall = rsTotCall("TotalCalls")
cnAcqAcc.Close
Set rsPRS = Nothing
Set rsCDR1 = Nothing
Set rsCDR2 = Nothing
Set rsCDR3 = Nothing
Set rsCDR4 = Nothing
Set rsCDR5 = Nothing
Set rsCDR6 = Nothing
Set rsCDR7 = Nothing
Set rsTotTime = Nothing
Set rsTotRev = Nothing
Set rsTotCall = Nothing
%>
<html>
<head>
<title>User Data - Acquist Accounts</title>
</head>
<body>
<h2> The start date and time is <% = CDRs1 %></h2><p>
<h2> The end date and time is <% =CDRs2 %></h2><p>
<h2> The duration of the hit in seconds is <% =CDRs3 %> seconds</h2><p>
<h2> The callers number is <% =CDRs4 %></h2><p>
<h2> The dialled Number is <% =CDRs5 %></h2><p>
<h2> The terminating/PRS number is <% =CDRs6 %></h2><p>
<h2> The value of the call in pence is <% =CDRs7 %> pence</h2><p>
<h3> The total number of calls to this service is <% =TotCall %> .</h3><p>
<h3> The total duration of all calls to this service is <% =TotTime %> seconds.</h3><p>
<h3> The total reveue generated by these calls is <% =TotRev %> pence.</h3><p>
</body>
</html>
Can anyone see why my code ain't workin'? I'm using VBscript in an .asp to retrieve data from a database. Its going to be web enabled, and all a I want to do is reprduce an oracle table, but only the rows that belong to the user. I can insert data into various tables using an asp but the code to view the database keeps falling over. Its somthing to do with dynamic arrays, but I can't find anything wrong with the code! Any info or help would be hugely appreciated.
Cheers n beers.
Code:
<%
Dim i
Dim cnAcqAcc
Dim cmAcqAcc
Dim rsPRS
Dim cmSQL
Dim cmmSQL
Dim sSQL
Dim ssSQL
Dim sssSQL
Dim PRS
Dim CDRs()
Dim CDRs1()
Dim CDRs2()
Dim CDRs3()
Dim CDRs4()
Dim CDRs5()
Dim CDRs6()
Dim CDRs7()
Dim TotTime
Dim TotRev
Dim TotCall
Dim rsCDR
Dim rsTotTime
Dim rsTotRev
Dim rsTotCall
Dim rsID
Dim rsSL
Dim Security
Set cnAcqAcc = CreateObject("ADODB.Connection")
cnAcqAcc.ConnectionString = "Provider=MSDASQL;DSN=AqAc;UID=AcqAcc_owner;PWD=AcqAcc_owner;"
cnAcqAcc.open
Custom_ID = Request.Cookies("cID")
'Response.Write (Custom_ID)
cmSQL = "SELECT PRS_Number FROM Service_Details WHERE Customer_ID = '" & Custom_ID & "' "
Set rsPRS = cnAcqAcc.Execute(cmSQL)
rsPRS.MoveFirst
PRS = rsPRS("PRS_Number")
'Response.Write (PRS)
mmSQL = "SELECT StartDateTime, EndDateTime, DurationSecs, CallersNumber, DialledNumber,TerminatingNumber, ValuePence FROM Opal_Data WHERE TerminatingNumber = '" & PRS & "' "
Set rsCDR = cnAcqAcc.Execute(cmmSQL)
rsCDR.MoveNext
i = 1
While Not rsCDR.EOF
CDRs1(i) = rsCDR.Fields("StartDateTime")
CDRs2(i) = rsCDR.Fields("EndDateTime")
CDRs3(i) = rsCDR.Fields("DurationSecs")
CDRs4(i) = rsCDR.Fields("CallersNumber")
CDRs5(i) = rsCDR.Fields("DialledNumber")
CDRs6(i) = rsCDR.Fields("TerminatingNumber")
CDRs7(i) = rsCDR.Fields("ValuePence")
i = i + 1
Wend
Response.Cookies("cID")= Stored_ID
sSQL = "SELECT SUM(DurationSecs) AS TotalTime FROM Opal_Data WHERE TerminatingNumber = '" & PRS & "' "
Set rsTotTime = cnAcqAcc.Execute(sSQL)
rsTotTime.MoveFirst
TotTime = rsTotTime("TotalTime")
ssSQL = "SELECT SUM(ValuePence) AS TotalRevenue FROM Opal_Data WHERE TerminatingNumber = '" & PRS & "' "
Set rsTotRev = cnAcqAcc.Execute(ssSQL)
rsTotRev.MoveFirst
TotRev = rsTotRev("TotalRevenue")
sssSQL = "SELECT COUNT(ValuePence) AS TotalCalls FROM Opal_Data WHERE TerminatingNumber = '" & PRS & "' "
Set rsTotCall = cnAcqAcc.Execute(sssSQL
rsTotCall.MoveFirst
TotCall = rsTotCall("TotalCalls")
cnAcqAcc.Close
Set rsPRS = Nothing
Set rsCDR1 = Nothing
Set rsCDR2 = Nothing
Set rsCDR3 = Nothing
Set rsCDR4 = Nothing
Set rsCDR5 = Nothing
Set rsCDR6 = Nothing
Set rsCDR7 = Nothing
Set rsTotTime = Nothing
Set rsTotRev = Nothing
Set rsTotCall = Nothing
%>
<html>
<head>
<title>User Data - Acquist Accounts</title>
</head>
<body>
<h2> The start date and time is <% = CDRs1 %></h2><p>
<h2> The end date and time is <% =CDRs2 %></h2><p>
<h2> The duration of the hit in seconds is <% =CDRs3 %> seconds</h2><p>
<h2> The callers number is <% =CDRs4 %></h2><p>
<h2> The dialled Number is <% =CDRs5 %></h2><p>
<h2> The terminating/PRS number is <% =CDRs6 %></h2><p>
<h2> The value of the call in pence is <% =CDRs7 %> pence</h2><p>
<h3> The total number of calls to this service is <% =TotCall %> .</h3><p>
<h3> The total duration of all calls to this service is <% =TotTime %> seconds.</h3><p>
<h3> The total reveue generated by these calls is <% =TotRev %> pence.</h3><p>
</body>
</html>