Solved

Excel VBA to call SQL Stored procedure

Posted on 2015-01-21
12
115 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 51

Expert Comment

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

Expert Comment

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

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 51

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 51

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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

622 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