使用数据库中的ADOB连接处理数据到Excel时，除以零错误

I was trying to call sql query from a macro and getting division by zero error. The same macro works when i run a small query. The query is huge and has multiple select statements. I have included 'Set Arithabort On' and 'Set Arithabort off in my SQL query. The macro is generating headers from the SQL table but not the data when i am using CopyFromRecordset to spreadsheet. Please let me know if you need more information.

我试图从宏调用sql查询并获得除零错误。当我运行一个小查询时,相同的宏工作。查询很大,有多个select语句。我已经在我的SQL查询中包含了“Set Arithabort On”和“Set Arithabort”。当我使用CopyFromRecordset到电子表格时,宏正在从SQL表生成头,但不生成数据。如果您需要更多信息,请与我们联系。

Screenshot:

Sub trial()
    Dim Cnn As New ADODB.Connection
    Dim Rst As New ADODB.Recordset
    Dim ConnectionString, SqlTextFile, SqlStatement As String

     'Prevent screenupdating when this macro runs
    Application.ScreenUpdating = False

     'Clear the destination sheet
    ThisWorkbook.Sheets("Sheet1").Cells.ClearContents


     'Connection to SQl Server
    ConnectionString = "Provider=SQLOLEDB;Data Source=.......;Initial Catalog=...........;Trusted_connection=yes;"

    Cnn.Open ConnectionString
    Cnn.CommandTimeout = 900

    SqlTextFile = "C:\Users\Domain Name\Desktop\SQL FILE\SQLQuery2.sql"
    Debug.Print SqlTextFile
    Dim hFile As Long
    hFile = FreeFile
    Open SqlTextFile For Input As #hFile
    SqlStatement = Input$(LOF(hFile), hFile)
    Close #hFile
    Debug.Print SqlStatement


    Rst.Open SqlStatement, Cnn

            For intColIndex = 0 To Rst.Fields.Count - 1
            Selection.Offset(0, intColIndex).Value = Rst.Fields(intColIndex).Name
            Next

        'Copy Data to Excel'
    Sheet1.Range("A2").CopyFromRecordset Rst
End Sub

 - **MY SQL Query**

SET ARITHABORT OFF  SET ARITHIGNORE OFF  SET ANSI_WARNINGS OFF
SELECT DISTINCT A.CONSUMED_TYPE,C.ACCT_IDFR, A.URJA_TYPE_NAME,
C.TRANSACTIONID AS PREV_TRANID, A.ENEINV_IDFR AS PREV_ENEINV_IDFR,SUM(A.TOTAL_CONSUMED) AS PREV_TOTAL_CONSUMED,C.CRAT_USER_ID,
D.TRANSACTIONID AS CURR_TRANID, B.ENEINV_IDFR AS CURR_ENEINV_IDFR, SUM(B.TOTAL_CONSUMED) AS CURR_TOTAL_CONSUMED,D.CRAT_USER_ID,
(SUM(A.TOTAL_CONSUMED)-SUM(B.TOTAL_CONSUMED))AS DIFF, 
(((SUM(A.TOTAL_CONSUMED)-SUM(B.TOTAL_CONSUMED))*100)/SUM(A.TOTAL_CONSUMED))AS VARIATION
FROM UMS_CONSUMPTION_BY_METER A, UMS_CONSUMPTION_BY_METER B, UMS_ENEINV C,UMS_ENEINV D
WHERE A.URJA_TYPE_NAME=B.URJA_TYPE_NAME AND A.ENEINV_IDFR=C.ENEINV_IDFR AND B.ENEINV_IDFR=D.ENEINV_IDFR 
AND C.ACCT_IDFR=D.ACCT_IDFR AND A.CONSUMED_TYPE=B.CONSUMED_TYPE AND A.CONSUMED_TYPE='Consumption'
AND YEAR(A.MAXENDDATE)=YEAR(B.MAXENDDATE)-1 AND MONTH(A.MAXENDDATE)=MONTH(B.MAXENDDATE) 
AND D.CRAT_USER_ID<>'SYSTEM' ---AND D.LU_DATE>='03/01/2014'
AND CAST(CONVERT(VARCHAR,D.LU_DATE,101)AS DATETIME)=CAST(CONVERT(VARCHAR,GETDATE(),101)AS DATETIME)
GROUP BY A.CONSUMED_TYPE,C.ACCT_IDFR, A.URJA_TYPE_NAME,C.TRANSACTIONID, A.ENEINV_IDFR, C.CRAT_USER_ID,
D.TRANSACTIONID, B.ENEINV_IDFR, D.CRAT_USER_ID HAVING SUM(A.TOTAL_CONSUMED)>SUM(B.TOTAL_CONSUMED) AND
(((SUM(A.TOTAL_CONSUMED)-SUM(B.TOTAL_CONSUMED))*100)/SUM(A.TOTAL_CONSUMED))>'50' 
UNION ALL
SELECT DISTINCT A.CONSUMED_TYPE,C.ACCT_IDFR, A.URJA_TYPE_NAME,
C.TRANSACTIONID AS PREV_TRANID, A.ENEINV_IDFR AS PREV_ENEINV_IDFR,SUM(A.TOTAL_CONSUMED) AS PREV_CONSUMED,C.CRAT_USER_ID,
D.TRANSACTIONID AS CURR_TRANID, B.ENEINV_IDFR AS CURR_ENEINV_IDFR, SUM(B.TOTAL_CONSUMED) AS CURR_CONSUMED,D.CRAT_USER_ID,
(SUM(A.TOTAL_CONSUMED)-SUM(B.TOTAL_CONSUMED))AS DIFF, 
(((SUM(B.TOTAL_CONSUMED)-SUM(A.TOTAL_CONSUMED))*100)/SUM(A.TOTAL_CONSUMED))AS VARIATION
FROM UMS_CONSUMPTION_BY_METER A, UMS_CONSUMPTION_BY_METER B, UMS_ENEINV C,UMS_ENEINV D
WHERE A.URJA_TYPE_NAME=B.URJA_TYPE_NAME AND A.ENEINV_IDFR=C.ENEINV_IDFR AND B.ENEINV_IDFR=D.ENEINV_IDFR 
AND C.ACCT_IDFR=D.ACCT_IDFR AND A.CONSUMED_TYPE=B.CONSUMED_TYPE AND A.CONSUMED_TYPE='Consumption'
AND YEAR(A.MAXENDDATE)=YEAR(B.MAXENDDATE)-1 AND MONTH(A.MAXENDDATE)=MONTH(B.MAXENDDATE) 
AND D.CRAT_USER_ID<>'SYSTEM' ---AND D.LU_DATE>='03/01/2014'
AND CAST(CONVERT(VARCHAR,D.LU_DATE,101)AS DATETIME)=CAST(CONVERT(VARCHAR,GETDATE(),101)AS DATETIME)
GROUP BY A.CONSUMED_TYPE,C.ACCT_IDFR, A.URJA_TYPE_NAME,C.TRANSACTIONID, A.ENEINV_IDFR, C.CRAT_USER_ID,
D.TRANSACTIONID, B.ENEINV_IDFR, D.CRAT_USER_ID HAVING SUM(B.TOTAL_CONSUMED)>SUM(A.TOTAL_CONSUMED) 
AND (((SUM(B.TOTAL_CONSUMED)-SUM(A.TOTAL_CONSUMED))*100)/SUM(A.TOTAL_CONSUMED))>'50' 
UNION ALL
SELECT DISTINCT A.CONSUMED_TYPE,C.ACCT_IDFR, A.URJA_TYPE_NAME,
C.TRANSACTIONID AS PREV_TRANID, A.ENEINV_IDFR AS PREV_ENEINV_IDFR,SUM(A.TOTAL_CONSUMED) AS PREV_TOTAL_CONSUMED,C.CRAT_USER_ID,
D.TRANSACTIONID AS CURR_TRANID, B.ENEINV_IDFR AS CURR_ENEINV_IDFR, SUM(B.TOTAL_CONSUMED) AS CURR_TOTAL_CONSUMED,D.CRAT_USER_ID,
(SUM(A.TOTAL_CONSUMED)-SUM(B.TOTAL_CONSUMED))AS DIFF, 
(((SUM(A.TOTAL_CONSUMED)-SUM(B.TOTAL_CONSUMED))*100)/SUM(A.TOTAL_CONSUMED))AS VARIATION
FROM UMS_CONSUMPTION_BY_METER A, UMS_CONSUMPTION_BY_METER B, UMS_ENEINV C,UMS_ENEINV D
WHERE A.URJA_TYPE_NAME=B.URJA_TYPE_NAME AND A.ENEINV_IDFR=C.ENEINV_IDFR AND B.ENEINV_IDFR=D.ENEINV_IDFR 
AND C.ACCT_IDFR=D.ACCT_IDFR AND A.CONSUMED_TYPE=B.CONSUMED_TYPE AND A.CONSUMED_TYPE='Demand'
AND YEAR(A.MAXENDDATE)=YEAR(B.MAXENDDATE)-1 AND MONTH(A.MAXENDDATE)=MONTH(B.MAXENDDATE) 
AND D.CRAT_USER_ID<>'SYSTEM' ---AND D.LU_DATE>='03/01/2014'
AND CAST(CONVERT(VARCHAR,D.LU_DATE,101)AS DATETIME)=CAST(CONVERT(VARCHAR,GETDATE(),101)AS DATETIME)
GROUP BY A.CONSUMED_TYPE,C.ACCT_IDFR, A.URJA_TYPE_NAME,C.TRANSACTIONID, A.ENEINV_IDFR, C.CRAT_USER_ID,
D.TRANSACTIONID, B.ENEINV_IDFR, D.CRAT_USER_ID HAVING SUM(A.TOTAL_CONSUMED)>SUM(B.TOTAL_CONSUMED) AND
(((SUM(A.TOTAL_CONSUMED)-SUM(B.TOTAL_CONSUMED))*100)/SUM(A.TOTAL_CONSUMED))>'50' 
UNION ALL
SELECT DISTINCT A.CONSUMED_TYPE,C.ACCT_IDFR, A.URJA_TYPE_NAME,
C.TRANSACTIONID AS PREV_TRANID, A.ENEINV_IDFR AS PREV_ENEINV_IDFR,SUM(A.TOTAL_CONSUMED) AS PREV_CONSUMED,C.CRAT_USER_ID,
D.TRANSACTIONID AS CURR_TRANID, B.ENEINV_IDFR AS CURR_ENEINV_IDFR, SUM(B.TOTAL_CONSUMED) AS CURR_CONSUMED,D.CRAT_USER_ID,
(SUM(A.TOTAL_CONSUMED)-SUM(B.TOTAL_CONSUMED))AS DIFF, 
(((SUM(B.TOTAL_CONSUMED)-SUM(A.TOTAL_CONSUMED))*100)/SUM(A.TOTAL_CONSUMED))AS VARIATION
FROM UMS_CONSUMPTION_BY_METER A, UMS_CONSUMPTION_BY_METER B, UMS_ENEINV C,UMS_ENEINV D
WHERE A.URJA_TYPE_NAME=B.URJA_TYPE_NAME AND A.ENEINV_IDFR=C.ENEINV_IDFR AND B.ENEINV_IDFR=D.ENEINV_IDFR 
AND C.ACCT_IDFR=D.ACCT_IDFR AND A.CONSUMED_TYPE=B.CONSUMED_TYPE AND A.CONSUMED_TYPE='Demand'
AND YEAR(A.MAXENDDATE)=YEAR(B.MAXENDDATE)-1 AND MONTH(A.MAXENDDATE)=MONTH(B.MAXENDDATE) 
AND D.CRAT_USER_ID<>'SYSTEM' ---AND D.LU_DATE>='03/01/2014'
AND CAST(CONVERT(VARCHAR,D.LU_DATE,101)AS DATETIME)=CAST(CONVERT(VARCHAR,GETDATE(),101)AS DATETIME)
GROUP BY A.CONSUMED_TYPE,C.ACCT_IDFR, A.URJA_TYPE_NAME,C.TRANSACTIONID, A.ENEINV_IDFR, C.CRAT_USER_ID,
D.TRANSACTIONID, B.ENEINV_IDFR, D.CRAT_USER_ID HAVING SUM(B.TOTAL_CONSUMED)>SUM(A.TOTAL_CONSUMED) 
AND (((SUM(B.TOTAL_CONSUMED)-SUM(A.TOTAL_CONSUMED))*100)/SUM(A.TOTAL_CONSUMED))>'50'
SET ARITHABORT ON  SET ARITHIGNORE ON  SET ANSI_WARNINGS ON

1 个解决方案

#1

Take a peek at the denominators in your SQL query, and consider replacing them with:

查看SQL查询中的分母,并考虑将其替换为:

NullIf(...some expression... ,0)

So, if the denominator equals ZERO, then the expression would essentially be n/null which will return NULL and not throw an error

因此,如果分母等于ZERO,那么表达式基本上是n / null,它将返回NULL并且不会抛出错误