help

**Skeen** · Sep 11th, 2000, 08:24 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>

**sonicblis** · Sep 11th, 2000, 08:39 AM

It may be this little snippet:

Code:

Set rsTotRev = cnAcqAcc.Execute(ssSQL) 
rsTotRev.MoveFirst 
TotRev = rsTotRev("TotalRevenue")

The MoveFirst command only works with the right kind of cursor, which I do not believe is default. Reverse movement of the cursor is reserved for the slower cursors. But, you should be at the first record that matches your sql statement anyway so you might just be able to leave that part out all together.

Let me know if that helps.

**Skeen** · Sep 11th, 2000, 09:11 AM

HI sonicblis

I reckon the problem is to do with the arrays and counting through the file at this part here:

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

I get a warning message saying subscript out of range - but the arrays are dimmed as dynamic arrays so I can't understand why it dosn't work. I've found out that I may need to redim each array or place them into a structure and redim that, but I haven't got a clue how this is done. If you know any "Preserve ReDim" syntax I'd be most greatfull

Cheers 'n' Beers - Skeen

**sonicblis** · Sep 11th, 2000, 09:16 AM

Are you just trying to get the results of one record with the id cookie? If it's just one record from an Oracle Database, just use <%=recordset("fieldname")%> and skip the whole

Code:

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

part.

**Skeen** · Sep 11th, 2000, 09:30 AM

Hi Chris

"StartDateTime", "EndDateTime"..etc...etc are all field names in an oracle table. This data is info logged about internet calls. Basically, people can go onto the database to findout how much time and cash they have spent surfing the net and each call is logged and recorded individually. So there is much more than one piece of data, but I need to darw out all the call data for a specific number.
For instance, all the calls ade from the number, - 0800 080808 for example, I want to display an HTML table of the view from the oracle database and so I want to put all the startdatetime values for 0800 080808 into one array - and so on, then I can associate the array with the relavent field in the nice looking html table.
This probably sounds a bit of a mouthful but I really am a complete beginner ( 2 months with vb - 1 month with oracle). I appreciate the help though - cheers.

Skeen

**sonicblis** · Sep 11th, 2000, 09:40 AM

It sounds like you can just use a loop. If you get your record set by using the

Code:

Set rsCDR = cnAcqAcc.Execute(cmmSQL)

...then you can use a loop to iterate through all the records returned, like so...

Code:

<table>
   <tr>
      <td>For Calls 800 0080880</td>
   </tr>
<%
Do while not rsCDR.EOF
   response.write "<tr>"
   response.write "<td>"
   response.write rsCDR("TimeStart")
   response.write "</td>"
   response.write "<td>"
   
   *** All your other fields ***

   response.write "</td>"
   response.write "</tr>"
Loop
%>
</table>

This will give you all of your rows in the Oracle table in an HTML table. Obviously you would need a <td></td> pair for each field and you can put labels for your fields outside your loop on the top. I think this will do what you want to accomplish.

**OneSource** · Sep 11th, 2000, 10:44 AM

I can't test out this code in VBScript (therefore, I can't guarantee that the code below is correct), but in VB, this is how you would re-dimension your dynamic arrays:

Code:

i = 1 
While Not rsCDR.EOF

Redim Preserve CDRs1(i)
Redim Preserve CDRs2(i)
Redim Preserve CDRs3(i)
Redim Preserve CDRs4(i)
Redim Preserve CDRs5(i)
Redim Preserve CDRs6(i)
Redim Preserve CDRs7(i)

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

I hope this helps.

All the best.

Thread: help

Thread Tools

Display

What's the error?

Just One Record?

A nice Loop

Redim Preserve

Posting Permissions