Обсуждение вопросов, связанных с компанией Microsoft, ее продуктами MS SQL Server, MS Windows, MS Office и др.
|
|
Привет всем.
У меня проблема. Я никак не могу вызивать stored procedure-у SQL Server-а с параметрами в VBA.
Вот процедура:
код SQL
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45: CREATE PROCEDURE dbo.spRetrieveOutwardsAmountsByPeriond @strDateStart char(6), @strDateEnd char(6)
------------------------------------------------------------------------
/*@Date DDMMYY*/
AS
------------------------------------------------------------------------
BEGIN
------------------------------------------------------------------------
SET DATEFORMAT dmy
------------------------------------------------------------------------
TRUNCATE TABLE dbo.T_BM_OUTWARDS
------------------------------------------------------------------------
DECLARE @dtCurrent datetime
DECLARE @dtEnd datetime
DECLARE @strCurrent char(6)
------------------------------------------------------------------------
SET @dtCurrent = cast ( substring( @strDateStart, 1, 2)
+ '-'
+ substring( @strDateStart, 3, 2)
+ '-20'
+ substring( @strDateStart, 5, 2)
as datetime )
SET @dtEnd = cast ( substring( @strDateEnd, 1, 2)
+ '-'
+ substring( @strDateEnd, 3, 2)
+ '-20'
+ substring( @strDateEnd, 5, 2)
as datetime )
------------------------------------------------------------------------
WHILE DATEDIFF( Day, @dtCurrent, @dtEnd ) >= 0
BEGIN
------------------------------------------------------------------------
SET @strCurrent = RIGHT( CAST( 100 + Day(@dtCurrent ) as char(3)), 2)
+ RIGHT( CAST( 100 + Month(@dtCurrent ) as char(3)), 2)
+ RIGHT( CAST( Year(@dtCurrent ) as char(4)), 2)
------------------------------------------------------------------------
EXEC dbo.spRetrieveOutwardsAmounts @strCurrent
------------------------------------------------------------------------
SET @dtCurrent = DATEADD( Day, 1, @dtCurrent)
------------------------------------------------------------------------
END
------------------------------------------------------------------------
END
------------------------------------------------------------------------
GO
А вот мой код в VBA
код Visual Basic
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43: '----------------------------------------------------------------
Const m_cadOpenStatic = 3
Const m_cadLockOptimistic = 3
Const m_cadCmdText = &H1
'----------------------------------------------------------------
Dim m_cnnSQL As Object
Dim m_cmdSQL As Object
'----------------------------------------------------------------
...
Dim strConnection As String
'----------------------------------------------------------------
strConnection = "Provider=SQLOLEDB.1;" & _
"Password=" & ThisWorkbook.Worksheets("Connection").Range("cellPassword") & ";" & _
"Persist Security Info=True;" & _
"User ID=" & ThisWorkbook.Worksheets("Connection").Range("cellUserID") & ";" & _
"Initial Catalog=" & ThisWorkbook.Worksheets("Connection").Range("cellInitialCatalog") & ";" & _
"Data Source=" & ThisWorkbook.Worksheets("Connection").Range("cellDataSource")
'----------------------------------------------------------------
Set m_cnnSQL = CreateObject("ADODB.Connection")
m_cnnSQL.Open strConnection
'----------------------------------------------------------------
Const adCmdStoredProc = 4
Const adVarChar = 200
Const adChar = 129
Const adParamInput = 1
'----------------------------------------------------------------
Dim m_cmdProc As Object
Set m_cmdProc = CreateObject("ADODB.Command")
'----------------------------------------------------------------
With m_cmdProc
.ActiveConnection = m_cnnSQL
.CommandType = adCmdStoredProc 'adCmdText
.CommandTimeout = 60000 ' 10 minutes (use 0 for indefnite)
.Prepared = False ' not to precompile the command
.CommandText = "spRetrieveOutwardsAmountsByPeriond" ' stored procedure name
End With
'----------------------------------------------------------------
m_cmdProc.Parameters("@strDateStart").Value = "010408"
m_cmdProc.Parameters("@strDateEnd").Value = "300408"
'----------------------------------------------------------------
m_cmdProc.Execute
'----------------------------------------------------------------
Я пытался делать это с разными способами, в частности:
код Visual Basic
1:
2:
3:
4:
5:
6:
7:
Set objParam1 = m_cmdProc.CreateParameter
objParam1.Type = adChar
objParam1.Name = "@strDateStart"
objParam1.Size = 6
objParam1.Direction = adParamInput
objParam1.Value = "010408"
m_cmdProc.Parameters.Append objParam1
Никак!
Надеюсь на вашу помощь.
|
| Ответить на сообщение » |
