Solved

Passing NULL parameters to Stored Proc in Excel VBA

Posted on 2014-07-24
6
1,079 Views
Last Modified: 2014-10-08
I'm trying to call a stored procedure that contains NULL parameters using Excel VBA.

The code works in SQL Server Management Studio -
Exec SP_AML_WIPS_Account_Data_Lookup '5670934',NULL,NULL

Here's my code vba code -

    Dim intDDA As Variant
    Dim intTIN As Variant
    Dim intPN As Variant
   
    intDDA = ThisWorkbook.Worksheets("WIPS Data Lookup").Range("E3").Value
    intTIN = ThisWorkbook.Worksheets("WIPS Data Lookup").Range("E5").Value
    intPN = ThisWorkbook.Worksheets("WIPS Data Lookup").Range("E7").Value
   
    If intDDA = 0 Then
       intDDA = Null
     Else
      intDDA = ThisWorkbook.Worksheets("WIPS Data Lookup").Range("E3").Value
    End If
   
    If intTIN = 0 Then
       intTIN = "Null"
    Else
      intTIN = ThisWorkbook.Worksheets("WIPS Data Lookup").Range("E5").Value
    End If
   
    If intPN = 0 Then
       intPN = "Null"
    Else
      intPN = ThisWorkbook.Worksheets("WIPS Data Lookup").Range("E7").Value
    End If
   
    With ActiveWorkbook.Connections("WIPs_Account_Data").OLEDBConnection
          .CommandText = "EXECUTE dbo.SP_AML_WIPS_Account_Data_Lookup '" & intDDA & "'" & "," & "'" & intTIN & "'" & "," & " '" & intPN & "'"   'Three parameters
    End With
    ActiveWorkbook.Connections("WIPs_Account_Data").Refresh

When I check the value of .CommantText, it reads

?.commandtext
EXECUTE dbo.SP_AML_WIPS_Account_Data_Lookup '6681365,'',''

I need it read as **EXECUTE dbo.SP_AML_WIPS_Account_Data_Lookup '6681365,NULL, NULL

I can't get NULL to be included in my EXECUTE statement, what am I missing, thanks for your help.
0
Comment
Question by:gilweber3
  • 2
  • 2
6 Comments
 
LVL 4

Expert Comment

by:Nuno Rogado
ID: 40218041
Hi,

I would change:
 intDDA = ThisWorkbook.Worksheets("WIPS Data Lookup").Range("E3").Value
to
 intDDA = "'" & ThisWorkbook.Worksheets("WIPS Data Lookup").Range("E3").Value & "'"
same to other 2 parameters

and then change:
 .CommandText = "EXECUTE dbo.SP_AML_WIPS_Account_Data_Lookup '" & intDDA & "'" & "," & "'" & intTIN & "'" & "," & " '" & intPN & "'"   'Three parameters
to:
 .CommandText = "EXECUTE dbo.SP_AML_WIPS_Account_Data_Lookup " & intDDA & "," & intTIN & "," & intPN

I hope this answer will help you solving your problem.

Best Regards,
Nuno Rogado
0
 

Author Comment

by:gilweber3
ID: 40218079
Thanks for your help on this but still no go.

When I debug .commandtext I get

?.commandtext
EXECUTE dbo.SP_AML_WIPS_Account_Data_Lookup 6681365,,

I need .commandtext to be
EXECUTE dbo.SP_AML_WIPS_Account_Data_Lookup 6681365, NULL, NULL

Just can't get the NULL parameters into the EXECUTE statement.

Thanks anyway.
0
 
LVL 4

Accepted Solution

by:
Nuno Rogado earned 500 total points
ID: 40218094
Please change:

If intDDA = 0 Then
       intDDA = Null
     Else

to:

If Val(intDDA) = 0 Then
       intDDA = "Null"
     Else

for all 3 parameters
1
 

Author Comment

by:gilweber3
ID: 40219612
Outstanding, that did the trick. Thanks for taking a look at this.
0

Featured Post

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.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Video by: Zack
Viewers will learn about using Excel in a browser with Excel Online.
Viewers will learn the basics of the new Quick Analysis feature in Excel 2013.

939 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now