通过Excel VBA加入SQL表时出现错误
我需要一些帮助。我想通过SQL在Excel中提取具有连接查询的数据,但通过下面的代码运行时,我得到的调试错误..通过Excel VBA加入SQL表时出现错误
recset.Open strSQL, con, adOpenDynamic, adLockOptimistic
错误 - 运行时错误-2147467259
下面是代码.....请帮助:困惑:
Sub Show_data()
Dim con As ADODB.Connection
Dim recset As ADODB.Recordset
Dim ConnectionString As String
Dim strSQL As String
Set con = New ADODB.Connection
Set recset = New ADODB.Recordset
''Check for the connectivity or connected to the xx network
On Error GoTo errHandler
errHandler:
If Err.Number = -2147467259 Then
MsgBox "Please check for the xx connectivity ", vbExclamation + vbOKOnly
Exit Sub
End If
ConnectionString = "Provider=MSDASQL;User ID=myuser;password= mypass;Data Source=mys"
con.Open ConnectionString
'Set and Excecute SQL Command'
strSQL = "SELECT B.USER_NAME AS CREATED_BY, A.CREATION_DATE, C.USER_NAME, A.LAST_UPDATE_DATE, A.PFIZER_ITEMCODE, A.SYSTEM_ITEMCODE AS ORACLE_ITEM_CODE," & _
"A.ITEM_DESCRIPTION, A.BATCH_NUMBER, A.MFR_CODE, A.MFR_DESC AS MFR_DESCRIPTION, TO_CHAR(A.MFR_DATE,'DD-MON-YYYY')As MFR_DATE, TO_CHAR(A.EXPIRY_DATE,'DD-MON-YYYY')As EXPIRY_DATE," & _
"TO_CHAR(A.EFFECTIVE_FROM,'DD-MON-YYYY') AS EFFECTIVE_FROM," & _
"A.EFFECTIVE_TO, A.EXCISE AS EXCISE_AMOUNT, A.EXCISE_RATE, A.P2S, A.P2R, A.MRP, A.STATE_CODE, A.STATE," & _
"(CASE SUBSTR(A.SYSTEM_ITEMCODE,6,2) WHEN ('PI') THEN 'OIP' WHEN ('PF') THEN 'OPF' ELSE 'OWL' END)AS LEGAL_ENTITY" & _
"FROM xxdhl_pf_batch_pricing A, fnd_user B, fnd_user c" & _
"WHERE 1=1" & _
"AND A.CREATED_BY = B.USER_ID" & _
"AND A.LAST_UPDATED_BY = C.USER_ID"
If (ActiveSheet.cmbLE.Text) <> "" Then
strSQL = strSQL & " LEGAL_ENTITY='" & ActiveSheet.cmbLE.Text & "'"
End If
If (ActiveSheet.cmbProduct.Text) <> "" Then
If (ActiveSheet.cmbLE.Text) <> "" Then
strSQL = strSQL & " AND [ORACLE_ITEM_CODE]='" & ActiveSheet.cmbProduct.Text & "'"
Else
strSQL = strSQL & " [ORACLE_ITEM_CODE]='" & ActiveSheet.cmbProduct.Text & "'"
End If
End If
If (ActiveSheet.txtBatch.Text) <> "" Then
If (ActiveSheet.cmbLE.Text) <> "" Or (ActiveSheet.cmbProduct.Text) <> "" Then
strSQL = strSQL & " AND [BATCH_NUMBER]='" & ActiveSheet.txtBatch.Text & "'"
Else
strSQL = strSQL & " [BATCH_NUMBER]='" & ActiveSheet.txtBatch.Text & "'"
End If
End If
'Open Recordset
Set recset.ActiveConnection = con
recset.Open strSQL, con, adOpenDynamic, adLockOptimistic
'Copy the data
If recset.RecordCount > 0 Then
Sheets("Sheet2").Range("A1").CopyFromRecordset recset
' ActiveSheet.Range("A1").CopyFromRecordset recset
Else
MsgBox "No Data Available", vbExclamation + vbOKOnly
Exit Sub
End If
recset.Close
con.Close
End Sub
按你的要求,我不得不打印STRSQL
SELECT B.USER_NAME AS CREATED_BY, A.CREATION_DATE, C.USER_NAME,
A.LAST_UPDATE_DATE,
A.PFIZER_ITEMCODE, A.SYSTEM_ITEMCODE AS ORACLE_ITEM_CODE,
A.ITEM_DESCRIPTION, A.BATCH_NUMBER,
A.MFR_CODE, A.MFR_DESC AS MFR_DESCRIPTION,
TO_CHAR(A.MFR_DATE,'DD-MON-YYYY')As MFR_DATE,
TO_CHAR(A.EXPIRY_DATE,'DD-MON-YYYY')As EXPIRY_DATE,
TO_CHAR(A.EFFECTIVE_FROM,'DD-MON-YYYY') AS EFFECTIVE_FROM,
A.EFFECTIVE_TO, A.EXCISE AS EXCISE_AMOUNT,
A.EXCISE_RATE, A.P2S,
A.P2R, A.MRP, A.STATE_CODE, A.STATE,
(CASE SUBSTR(A.SYSTEM_ITEMCODE,6,2) WHEN ('PI') THEN 'OIP' WHEN ('PF') THEN 'OPF' ELSE 'OWL' END)AS LEGAL_ENTITY
FROM xxdhl_pf_batch_pricing A, fnd_user B, fnd_user c
WHERE 1=1 AND A.CREATED_BY = B.USER_ID AND A.LAST_UPDATED_BY = C.USER_ID AND [A.LEGAL_ENTITY]='J0012' AND [A.SYSTEM_ITEMCODE]='1407_PI-J0012'
最后,我已经解决了错误.....实际上在一个编码中,我已经给出了参数,其中报价给出了问题......即,
以前我是运行查询与下面的代码,
strSQL & " AND [ORACLE_ITEM_CODE]='"
只是东阳有报价
"["
... WS收到这个错误。
但是,消除其优良的工作后同样....
我想感谢大家谁分享了他们解决错误知识...这也有助于我获得我的知识...
谢谢全部
如果你有这样的代码
"WHERE 1=1" & _
"AND A.CREATED_BY = B.USER_ID" & _
将呈现
"WHERE 1=1AND A.CREATED_BY = B.USER_ID"
即之间没有空格1=1
和AND
尝试把一个空间,每行收盘报价前:
"WHERE 1=1 " & _
"AND A.CREATED_BY = B.USER_ID " & _
嗨,感谢您的快速解决方案,但即使更改上述相同的错误m面临 –
哪一行代码会产生错误?你能打印strSQL变量吗?如果使用诸如“SELECT * FROM Table”之类的东西,错误仍然会发生吗? – ChipsLetten
如果您可以在执行之前将strSQL的内容发布到问题上,那最好。 –
使用“& _”扩展程序可添加到字符串变量的字符数有限制。
芯片对Debug.Print变量的建议会告诉你这个查询在你尝试执行之前是否可行。
当我创建我的SQL的查询,我用这样的过程中避免截断:
strSQL = "SELECT "
strSQL = strSQL & "Field1, Field2, Field3 "
strSQL = strSQL & "FROM MyTable "
在每行的结尾加上一个空格确保安迪上面指出的问题不会发生。
与安迪乔伊纳的建议类似,你实际上在几个地方的末尾错过了一个空间。例如
"(CASE SUBSTR(A.SYSTEM_ITEMCODE,6,2) WHEN ('PI') THEN 'OIP' WHEN ('PF') THEN 'OPF' ELSE 'OWL' END)AS LEGAL_ENTITY" & _
"FROM xxdhl_pf_batch_pricing A, fnd_user B, fnd_user c" & _
看起来像:
"(CASE SUBSTR(A.SYSTEM_ITEMCODE,6,2) WHEN ('PI') THEN 'OIP' WHEN ('PF') THEN OPF' ELSE 'OWL' END)AS LEGAL_ENTITYFROM xxdhl_pf_batch_pricing A, fnd_user B, fnd_user c" & _
从而省去您的FROM子句。
是的,我尝试过空间,但仍然面临相同的错误 –
正如前面建议,我们需要看到完整的查询,因为它正在传递到SQL – Gene
嗨,我已经把调试sql的打印查询...请参阅和让我知道如果有...请 –
你可以在运行之前打印strSQL并附加到问题上吗? –
您使用的是ADO吗? – Tarik
是tarik ....... –