atuljadhavnetafim
Active Member
- Joined
- Apr 7, 2012
- Messages
- 341
- Office Version
- 365
- Platform
- Windows
Dear Expert,
i have VBA code which call to store procedure in SQL server and getting data as set in store procedure
it's working fin in my system, but it is not working in another System it getting error in
Set rs = cmd.Execute(, , adCmdStoredProc)
the code is
code
Sub Button1_Click()
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim par As String
Dim WSP1 As Worksheet
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
Application.DisplayStatusBar = True
Application.StatusBar = "Contacting SQL Server..."
' Remove any values in the cells where we want to put our Stored Procedure's results.
Dim rngRange As Range
Set rngRange = Range(Cells(8, 2), Cells(Rows.Count, 1)).EntireRow
rngRange.ClearContents
' Log into our SQL Server, and run the Stored Procedure
con.Open "Provider=SQLOLEDB;Data Source=vcsql;Initial Catalog=CreditControl;Integrated Security=SSPI;Trusted_Connection=Yes;"
cmd.ActiveConnection = con
Dim prmFinal_District As ADODB.Parameter
' Set up the parameter for our Stored Procedure
' (Parameter types can be adVarChar,adDate,adInteger)
cmd.Parameters.Append cmd.CreateParameter("Final_District", adVarChar, adParamInput, 50, Range("D2").Text)
Application.StatusBar = "Running stored procedure..."
cmd.CommandText = "Payer_DSO_Sales"
Set rs = cmd.Execute(, , adCmdStoredProc)
' Copy the results to cell B7 on the first Worksheet
Set WSP1 = Worksheets(1)
WSP1.Activate
If rs.EOF = False Then WSP1.Cells(8, 2).CopyFromRecordset rs
rs.Close
Set rs = Nothing
Set cmd = Nothing
con.Close
Set con = Nothing
Application.StatusBar = "Data successfully updated."
End Sub
code/
i have VBA code which call to store procedure in SQL server and getting data as set in store procedure
it's working fin in my system, but it is not working in another System it getting error in
Set rs = cmd.Execute(, , adCmdStoredProc)
the code is
code
Sub Button1_Click()
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim par As String
Dim WSP1 As Worksheet
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
Application.DisplayStatusBar = True
Application.StatusBar = "Contacting SQL Server..."
' Remove any values in the cells where we want to put our Stored Procedure's results.
Dim rngRange As Range
Set rngRange = Range(Cells(8, 2), Cells(Rows.Count, 1)).EntireRow
rngRange.ClearContents
' Log into our SQL Server, and run the Stored Procedure
con.Open "Provider=SQLOLEDB;Data Source=vcsql;Initial Catalog=CreditControl;Integrated Security=SSPI;Trusted_Connection=Yes;"
cmd.ActiveConnection = con
Dim prmFinal_District As ADODB.Parameter
' Set up the parameter for our Stored Procedure
' (Parameter types can be adVarChar,adDate,adInteger)
cmd.Parameters.Append cmd.CreateParameter("Final_District", adVarChar, adParamInput, 50, Range("D2").Text)
Application.StatusBar = "Running stored procedure..."
cmd.CommandText = "Payer_DSO_Sales"
Set rs = cmd.Execute(, , adCmdStoredProc)
' Copy the results to cell B7 on the first Worksheet
Set WSP1 = Worksheets(1)
WSP1.Activate
If rs.EOF = False Then WSP1.Cells(8, 2).CopyFromRecordset rs
rs.Close
Set rs = Nothing
Set cmd = Nothing
con.Close
Set con = Nothing
Application.StatusBar = "Data successfully updated."
End Sub
code/