Solved

Excel VBA to call SQL Stored procedure

Posted on 2015-01-21
12
107 Views
Last Modified: 2015-01-22
I have problem using VBA to call. No error pop up and nothing is returned, Any idea ?


Tks
Result.pdf
0
Comment
Question by:AXISHK
  • 6
  • 5
12 Comments
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40561452
Shouldn't you define the cmd and Rs variables?
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40561456
Also try Rs.Open cmd instead of the Execute method.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40561458
Sorry but starting to get refreshed with ADO. For long that I don't use it.
You need to specify  that you want to run a Stored Procedure so add the following line:
 Cmd.CommandType = adCmdStoredProc
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:AXISHK
ID: 40561460
Pop up another message ...

actually, I follow the coding on the link below,,
http://mikesknowledgebase.azurewebsites.net/pages/SQLServer/RunStoredProcedureFromExcel.htm

Tks
Result1.png
0
 
LVL 15

Assisted Solution

by:Haris Djulic
Haris Djulic earned 200 total points
ID: 40561462
Hi,

in order to reuse the code for this purpose i prepare the function to open connection:

Private Cn As ADODB.Connection
Public Function ConnectionString() As ADODB.Connection

    Dim connstr As String

 connstr = "Provider=SQLOLEDB;Server=(local);Database=MYDB;Trusted_connection=yes"
    
    If Cn Is Nothing Then
    Set Cn = New ADODB.Connection
        With Cn
        .ConnectionString = connstr
        .CommandTimeout = 0
        .Open
        End With
    End If
    
   Set ConnectionString = Cn
End Function

Open in new window


then i have function to call the procedure with 1 parameter
Private rs As New ADODB.recordset
Private cmd As New ADODB.Command

Public Function sql_query(proc_name As String, param1 As String)
Set Cn = ConnectionString()
Dim cmd As New ADODB.Command
Dim rs As New ADODB.recordset
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenStatic
    With cmd
        .CommandText = proc_name
        .CommandType = adCmdStoredProc
        .ActiveConnection = Cn
        .Parameters.Refresh
        .Parameters(1).Value = param1
    End With
rs.Open cmd
Set sql_query= rs
End Function

Open in new window



and finally i have sub which will cal the function and display the results on the sheet..

sub display_proc_result

' call of the function
Set rs = sql_query("wbdisplaybook", "P000210584")

'display retrived dataset

Me.Range("A8:P1000").ClearContents
 col = 0
 Row = 8

      Do While Not rs.EOF
  
  If Row = 8 Then
      Do While col < rs.Fields.Count
      Cells(Row, col + 1) = rs.Fields(col).Name
      col = col + 1
    Loop
    End If
    
    Row = Row + 1
    col = 0
    
    Do While col < rs.Fields.Count
      Cells(Row, col + 1) = rs.Fields(col).Value
      col = col + 1
    Loop
  
    rs.MoveNext
  Loop
  
  Me.Range("A8:P1000").EntireRow.Columns.AutoFit
end sub

Open in new window

0
 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 300 total points
ID: 40561465
AXISHK, maybe you didn't see my last comment:
You need to specify  that you want to run a Stored Procedure so add the following line:
  Cmd.CommandType = adCmdStoredProc
0
 

Author Comment

by:AXISHK
ID: 40561480
Yes, already added that but still don't retrieve any record...

Public Const ConPro As String = "Provider=sqloledb;Server=10.0.x.x;Database=HKG;Uid=report;Pwd=xxxxl"
Dim conn As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40561482
Even the commandtype?
What's the error now?
0
 

Author Comment

by:AXISHK
ID: 40561499
I have rewritten the code, it stop at ".Parameter(1),Value = stPar1

Run-time error '3265':
Item cannot be found in the collection corresponding to the requested name or ordinal.

conn.Open ConPro
cmd.ActiveConnection = conn

Rs.CursorLocation = adUseClient
Rs.CursorType = adOpenStatic
With cmd
    .CommandText = "wbdisplaybook"
    .ActiveConnection = conn
    .Parameters.Refresh
    .Parameters(1).Value = stPar1
End With

Rs.Open cmd
0
 

Author Comment

by:AXISHK
ID: 40562095
Try to use Haris's proposed macro to test the stored proc but it return with error. Any idea ?

Tks
StoredProc.xlsm
0
 

Author Comment

by:AXISHK
ID: 40563766
Find a root problem,

If i take away redirecting to a temporary table and select the records from a table directly, my code works fine.
However, whole store procedure need to use few temporary table in order to get a result. any idea how to solve it ? Tks


CREATE PROCEDURE [dbo].[sp_wbDisplayBook] @INVENTBATCHID VARCHAR(3000)
AS
BEGIN
SELECT A.INVENTBATCHID,D.ITEMID ,D.THK_INVENTD2ID, D.THK_INVENTD3ID, D.THK_INVENTD4ID, D.THK_INVENTDGOLDTYPEID, THK_REFBOMID,
CASE WHEN A.COSTGROUPID NOT LIKE 'ST%' THEN 4 ELSE B.KTL_STONETYPE END AS KTL_STONETYPE,
CASE WHEN A.COSTGROUPID NOT LIKE 'ST%' THEN SUM(OUTQTY) ELSE 0 END AS GoldCost,
CASE WHEN A.COSTGROUPID LIKE 'ST%' THEN CAST(CAST(SUM(OutQtySec) AS decimal) AS NVARCHAR(10))
                  +SUBSTRING(A.COSTGROUPID,4,3)+CAST(CAST(SUM(OUTQTY) AS decimal(20,2)) AS NVARCHAR(10)) Else '' END AS Material
 into #TM1
 FROM DynamicsAX_PRO.dbo.THK_LASTWIPMATERIALTRANS A
INNER JOIN DynamicsAX_PRO.dbo.BOMCostGroup B ON B.COSTGROUPID=A.COSTGROUPID
INNER JOIN DynamicsAX_PRO.dbo.INVENTBATCH C ON A.INVENTBATCHID=C.INVENTBATCHID AND C.DATAAREAID='OPR'
INNER JOIN DynamicsAX_PRO.dbo.BOMVERSION D ON C.THK_REFBOMID=D.BOMID AND C.ITEMID=D.ITEMID AND D.DATAAREAID='OPR'
inner join dbo.split(@INVENTBATCHID,',') as e on A.INVENTBATCHID COLLATE DATABASE_DEFAULT = e.Data COLLATE DATABASE_DEFAULT
GROUP BY SUBSTRING(A.COSTGROUPID,4,3),CASE WHEN A.COSTGROUPID NOT LIKE 'ST%' THEN 4 ELSE B.KTL_STONETYPE END,
A.COSTGROUPID,A.INVENTBATCHID,D.ITEMID,D.THK_INVENTD2ID,D.THK_INVENTD3ID,D.THK_INVENTD4ID,D.THK_INVENTDGOLDTYPEID,
THK_REFBOMID

select * from #TM1
END
0
 

Author Closing Comment

by:AXISHK
ID: 40563886
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question