Results 1 to 2 of 2

Thread: Syntax Error

  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: Syntax Error

    Hi: Can any one please tell me why it gives error of variable not found on line:
    cmADO.Parameters.Append cmADO.CreateParameter("Output", adInteger, adParamOutput, 8, TotalCount)

    Thanks.


    Code:
    Sub GetSummary()
      Dim i As Integer
      Dim sqlquery As String
    
      Erase sClientName, sDistributeID, sLower, sUpper, sBalRangeID
    
      sqlquery = "SELECT DISTINCT BalRangeID, clientID, AssignID , BalLower, Balupper FROM tbl_Distlist_AssignID"
      Debug.Print sqlquery
      oDBUtil.OpenADORecordSet sqlquery, cnADOSQL, rsADO, 0, adCmdUnknown
      Do While Not rsADO.EOF
        ReDim Preserve sClientName(i)
        ReDim Preserve sDistributeID(i)
        ReDim Preserve sLower(i)
        ReDim Preserve sUpper(i)
        ReDim Preserve sBalRangeID(i)
        sClientName(i) = rsADO!ClientID
        sDistributeID(i) = rsADO!AssignID
        sBalRangeID(i) = rsADO!BalRangeID
        sLower(i) = rsADO!BalLower
        sUpper(i) = rsADO!BalUpper
        i = i + 1
        rsADO.MoveNext
      Loop
      Set rsADO = Nothing
      If i <> 0 Then
        For i = 0 To UBound(sDistributeID)
          oDBUtil.ExecuteParamADOCommand cnADOSQL, cmADO, "sp_DistList_DEP_Summary_Test", adCmdStoredProc
          cmADO.Parameters.Append cmADO.CreateParameter("Input", adVarChar, adParamInput, 3, Trim(sDistributeID(i)))
          cmADO.Parameters.Append cmADO.CreateParameter("Input", adVarChar, adParamInput, 10, Trim(sClientName(i)))
          cmADO.Parameters.Append cmADO.CreateParameter("Input", adCurrency, adParamInput, 8, Val(sLower(i)))
          cmADO.Parameters.Append cmADO.CreateParameter("Input", adCurrency, adParamInput, 8, Val(sUpper(i)))
          cmADO.Parameters.Append cmADO.CreateParameter("Output", adInteger, adParamOutput, 8, TotalCount)
          Set rsADO = cmADO.Execute
          grdSummary.Rows = i + 2
          grdSummary.TextMatrix(i + 1, 1) = sBalRangeID(i)
          grdSummary.TextMatrix(i + 1, 2) = sClientName(i)
          grdSummary.TextMatrix(i + 1, 3) = sDistributeID(i)
          grdSummary.TextMatrix(i + 1, 4) = "" & rsADO!DEPCount
          grdSummary.TextMatrix(i + 1, 5) = "" & rsADO!DEPBal
          grdSummary.TextMatrix(i + 1, 6) = "" & rsADO!DEPMin
          grdSummary.TextMatrix(i + 1, 7) = "" & rsADO!DEPMax
          grdSummary.TextMatrix(i + 1, 8) = "" & rsADO!DEPAvg
          grdSummary.TextMatrix(i + 1, 9) = "" & rsADO!DEPAvg
          grdSummary.TextMatrix(i + 1, 10) = "" & TotalCount
          Set rsADO = Nothing
        Next i
      End If
    End Sub
    SQL SERVER
    ----------
    Code:
    CREATE PROCEDURE sp_DistList_DEP_Summary_Test
         @WorkList varchar(3),
         @Clt_ID varchar(10),
         @Min money,
         @Max money,
        @TotalCount int = 0 OUTPUT
    AS
    
    if not(@max = 0 )
         BEGIN
              SELECT  SUM(DUPLICATECOUNT) AS DEPCount, SUM(Cur_BAl) AS DEPBal, 
                   MIN(Cur_BAl) AS DEPMin, MAX(Cur_BAl) AS DEPMax, 
                   AVG(Cur_BAl) AS DEPAvg
              FROM    tbl_DistList_DEP
                      WHERE   (Clt_ID = @Clt_Id) 
                   AND (Cur_BAl BETWEEN @Min AND @Max) 
                   AND (WorkList = @Worklist)
    
            SET @TotalCount = @@ROWCOUNT
         END
    ELSE
         BEGIN
              SELECT SUM(DUPLICATECOUNT) AS DEPCount, SUM(Cur_BAl) AS DEPBal, 
                   MIN(Cur_BAl) AS DEPMin, MAX(Cur_BAl) AS DEPMax, 
                   AVG(Cur_BAl) AS DEPAvg
              FROM    tbl_DistList_DEP
                      WHERE   (Clt_ID = @Clt_Id) 
                   AND (Cur_BAl >@Min) 
                   AND (WorkList = @Worklist)
    
            SET @TotalCount = @@ROWCOUNT
         END
    GO
    Last edited by loquin; 01-31-07 at 12:39. Reason: add code tags for readability

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I don't see a Dim for TotalCount.
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •