Avatar of printmedia
printmedia

asked on 

VBScript recordset (loading a chart) "Not a valid integer value" error

Hi all.

I have a form that has a control (chart1) which displays a graph, but I get the following error when I add the line:  .Fields.Append "PL", RTrim(adVarChar), 32

The error I get is: '<the value of PL>' is not a valid integer value at line (chart1.RecordSet = gTopOppRS)
"PL" is a varchar (holds the productline short description).

This works fine when I do not include the line:  .Fields.Append "PL", RTrim(adVarChar), 32

What am I doing wrong?

Sub AXFormChange(Sender)
 
    LoadChart 'Runs the script that displays the bar graph
End Sub
 
''''''''''''''''''''''''''
'NEW SCRIPT FOR DISPLAYING CHART
Dim gTopOppRS
 
Sub LoadChart()
 
  BuildTopOppRS
 
  GetTopOppData
 
  Dim strSQL
 
   Dim objCon
 
   Dim objRS
 
  Dim I
 
strSQL = "SELECT   SDI.SUMTOTALSALES As Amount, SDI.SALESDATAYEAR AS Year, SDI.PRODUCTLINE AS PL"
 
   strSQL = strSQL  & " FROM SALESBYPRODUCTLINE SDI"
 
   strSQL = strSQL  & " WHERE  SDI.ACCOUNTID = '" & frmAccountDetail.CurrentID & "'"
 
  Set objCon = Application.GetNewConnection
 
  Set objRS = CreateObject("ADODB.Recordset")
 
   objRS.CursorType = 3 'adOpenStatic
 
  objRS.CursorLocation = 3 'adUseClient
 
   objRS.LockType = 3 'adLockOptimistic
 
   objRS.Open strSQL, objCon  'Provider
 
   If objRS.RecordCount > 0  Then
 
      chart1.RecordSet = gTopOppRS
 
      chart1.Refresh
 
      chart1.Visible = True
 
  Else
 
      chart1.Visible = False
  
      exit sub
  End If
 
End Sub
 
Sub BuildTopOppRS
 
   Set gTopOppRS = CreateObject("ADODB.Recordset")
 
    With gTopOppRS
 
          .Fields.Append "YEAR", adDouble, 32
 
          .Fields.Append "AMOUNT",adCurrency, 12
 
          .Fields.Append "PL", RTrim(adVarChar), 32
 
          .Open
 
     End With
 
End sub
 
Sub GetTopOppData
 
   Dim strSQL
 
   Dim objCon
 
   Dim objRS
 
   Dim I
 
   Dim MAX
 
 '  strSQL = "SELECT   SDI.SUMTOTALSALES As Amount, SDI.SALESDATAYEAR AS Year"
 
   'strSQL = strSQL  & " FROM SDISALESDATA SDI"
 
   'strSQL = strSQL  & " WHERE SDI.ACCOUNTID = '" & frmAccountDetail.CurrentID & "'"
strSQL = "SELECT   SDI.SUMTOTALSALES As Amount, SDI.SALESDATAYEAR AS Year,SDI.PRODUCTLINE AS PL"
 
   strSQL = strSQL  & " FROM SALESBYPRODUCTLINE SDI"
 
   strSQL = strSQL  & " WHERE  SDI.ACCOUNTID = '" & frmAccountDetail.CurrentID & "'"
 
   Set objCon = Application.GetNewConnection
 
   Set objRS = CreateObject("ADODB.Recordset")
 
   objRS.CursorType = 3 'adOpenStatic
 
   objRS.CursorLocation = 3 'adUseClient
 
   objRS.LockType = 3 'adLockOptimistic
 
   objRS.Open strSQL, objCon  'Provider
 
   If objRS.RecordCount > 0  Then
 
      For I = 1 to objRS.RecordCount
 
 
         gTopOppRS.AddNew
 
         gTopOppRS.Fields("YEAR").Value = I
 
         If not(IsNull(objRS.Fields("YEAR").Value))Then
 
            gTopOppRS.Fields("YEAR").Value = objRS.Fields("YEAR").Value
 
         Else
 
            gTopOppRS.Fields("YEAR").Value = ""
 
         End IF
 
 
         If not(IsNull(objRS.Fields("AMOUNT").Value))Then
 
            gTopOppRS.Fields("AMOUNT").Value = objRS.Fields("AMOUNT").Value
 
         Else
 
            gTopOppRS.Fields("AMOUNT").Value = 0.0
 
         End IF
 
         If not(IsNull(objRS.Fields("PL").Value))Then
 
            gTopOppRS.Fields("PL").Value = objRS.Fields("PL").Value
 
         Else
 
            gTopOppRS.Fields("PL").Value = ""
 
         End IF
 
 
        gTopOppRS.Update
 
         objRS.MoveNext
 
       next
 
       gTopOppRS.MoveFirst
 
 
 
   End If
 
   objRS.Close
 
   Set objCon = Nothing
 
   Set objRS = Nothing
 
End Sub

Open in new window

VB Script

Avatar of undefined
Last Comment
printmedia

8/22/2022 - Mon