problems with strings

[Skeen]

How-de-doo-de

I'm using VBscript to withdraw data from an oracle table, and want to display said data in a flashy html table. I'm getitng an error message on the following bit of code and I'm stuck (so to speak). There are 7 fields and multiple records in each field. I want to draw them all out, assign the records for each field to individual arrays and then place into an HTML table. The error I get is

<-- subscript out of range -->

Heres the bit thats falling over:

cmmSQL= "SELECT StartDateTime, EndDateTime, DurationSecs, CallersNumber, DialledNumber,TerminatingNumber, ValuePence FROM Opal_Data WHERE TerminatingNumber = '" & PRS & "' "

Set rsCDR = cnAcqAcc.Execute( cmmSQL )
rsCDR.MoveFirst
i = 1
While not rsCDR.EOF
CDRs1(i)=rsCDR.Fields("StartDateTime") 'error msg is here
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

can anyone shed a liitle light 'cos it looks like it should work to me?

Cheers 'n' Beers

[Clunietp]

What are CDRs1 --> CDRs7? What are these?

[Mensa]

It looks to my like you have not defined CDRs1 through CDRs7 as a large enough array.

A "subscript out of range" error is typically given when you are trying to assign values to an array variable outside the array size definition.

e.g.
Dim strSample(10) As String
Dim intCtr As Integer

For intCtr = 1 to 11
strSample(intCtr) = "Whatever text"
Next intCtr

The line 'strSample(intCtr) = "Whatever text"' will give an "subscript out of range" error when intCtr = 11 because the array size definition is 10.

Hope this helps.

[Skeen]

Cheers Mensa and Clunietp,

I'm still stuck,
CDRs1-->CDRs7 are arryas which I have defined thus:

' Dim CDRs1 ()
' Dim CDRs2 ().............etc..etc..

I know its bad practice not to declare a length for the array, however, shoudn't the array size itself in this instance?

I'll post my complete code below to show you what I mean:


<%

Dim cnAcqAcc
Dim cmAcqAcc
Dim rsPRS
Dim cmSQL
Dim cmmSQL
Dim cmpSQL
Dim c1SQL
Dim c2SQL
Dim c3SQL
Dim c4SQL
Dim c5SQL
Dim c6SQL
Dim c7SQL
Dim sSQL
Dim ssSQL
Dim sssSQL
Dim PRS
Dim CDRs
Dim CDRs1 (5)
Dim CDRs2 (5)
Dim CDRs3 (5)
Dim CDRs4 (5)
Dim CDRs5 (5)
Dim CDRs6 (5)
Dim CDRs7 (5)
Dim TotTime
Dim TotRev
Dim TotCall
Dim rsCDR
Dim rsCDR1
Dim rsCDR2
Dim rsCDR3
Dim rsCDR4
Dim rsCDR5
Dim rsCDR6
Dim rsCDR7
Dim rsTotTime
Dim rsTotRev
Dim rsTotCall
Dim rsID
Dim rsSL
Dim Security
'On Error Resume Next

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)


'User = Request.Form("T1")
'Pass = Request.Form("T2")

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)


cmmSQL= "SELECT StartDateTime, EndDateTime, DurationSecs, CallersNumber, DialledNumber,TerminatingNumber, ValuePence FROM Opal_Data WHERE TerminatingNumber = '" & PRS & "' "

Set rsCDR = cnAcqAcc.Execute( cmmSQL )
rsCDR.MoveFirst

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

CDRs = rsCDR ( "StartDateTime, EndDateTime, DurationSecs, CallersNumber, DialledNumber,TerminatingNumber, ValuePence" )
Response.Write (CDRs1)
'Count records

c1SQL= "SELECT StartDateTime FROM Opal_Data WHERE TerminatingNumber = '" & PRS & "' "

Set rsCDR1 = cnAcqAcc.Execute( c1SQL )
rsCDR1.MoveFirst
CDRs1 = rsCDR1 ( "StartDateTime" )
'Response.Write (CDRs1)

c2SQL= "SELECT EndDateTime FROM Opal_Data WHERE TerminatingNumber = '" & PRS & "' "

Set rsCDR2 = cnAcqAcc.Execute( c2SQL )
rsCDR2.MoveFirst
CDRs2 = rsCDR2 ( "EndDateTime" )
'Response.Write (CDRs2)

c3SQL= "SELECT DurationSecs FROM Opal_Data WHERE TerminatingNumber = '" & PRS & "' "

Set rsCDR3 = cnAcqAcc.Execute( c3SQL )
rsCDR3.MoveFirst
CDRs3 = rsCDR3 ( "DurationSecs" )
'Response.Write (CDRs3)

c4SQL= "SELECT CallersNumber FROM Opal_Data WHERE TerminatingNumber = '" & PRS & "' "

Set rsCDR4 = cnAcqAcc.Execute( c4SQL )
rsCDR4.MoveFirst
CDRs4 = rsCDR4 ( "CallersNumber" )
'Response.Write (CDRs4)

c5SQL= "SELECT DialledNumber FROM Opal_Data WHERE TerminatingNumber = '" & PRS & "' "

Set rsCDR5 = cnAcqAcc.Execute( c5SQL )
rsCDR5.MoveFirst
CDRs5 = rsCDR5 ( "DialledNumber" )
'Response.Write (CDRs5)

c6SQL= "SELECT TerminatingNumber FROM Opal_Data WHERE TerminatingNumber = '" & PRS & "' "

Set rsCDR6 = cnAcqAcc.Execute( c6SQL )
rsCDR6.MoveFirst
CDRs6 = rsCDR6 ( "TerminatingNumber" )
'Response.Write (CDRs6)

c7SQL= "SELECT ValuePence FROM Opal_Data WHERE TerminatingNumber = '" & PRS & "' "

Set rsCDR7 = cnAcqAcc.Execute( c7SQL )
rsCDR7.MoveFirst
CDRs7 = rsCDR7 ( "ValuePence" )
'Response.Write (CDRs7)
' 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

'Set rsSL = Nothing

'If StoredPassword = Pass AND Security < 5 Then
'Response.Redirect "MayneMenuDev.asp"


'ElseIf StoredPassword = Pass AND Security = 5 Then
'Response.Redirect "Administrator_Level.asp"


'ElseIf StoredPassword <> Pass Then
'Response.Redirect "Sorry.asp"
'End If

'End function
%>

<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>


Cheers 'n' Beers

Ed.

[Al Smith]

Hi,
I wonder if your subscript error is comming from the rsCDR.Fields instead of your CDRs1(i) variable.
What happens if you use the Field.(Index Number)?

e.g.

While not rsCDR.EOF
CDRs1(i)=rsCDR.Fields(0) 'error msg is here
CDRs2(i)=rsCDR.Fields(1)
CDRs3(i)=rsCDR.Fields(2)
CDRs4(i)=rsCDR.Fields(3)
CDRs5(i)=rsCDR.Fields(4)
CDRs6(i)=rsCDR.Fields(5)
CDRs7(i)=rsCDR.Fields(6)
i = i + 1
Wend

Al.

[Serge]

Why not to use GetRows method, which creates 2 dimensional array???

<%
Dim cn
Dim rs
Dim arrRecords

Set cn = Server.CreateObject("ADODB.Conenction")
Set rs = Server.CreateObject("ADODB.Recordset")

cn.Open "DSN=YourDSN", UserName, Password
rs.Open "Select * From MyTable", cn, 3 'adOpenStatic

arrRecords = rs.GetRows

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
%>


This will create arrRecords with 2 dimensions: arrRecords(Column, Row).

Regards,