Solved

Passing NULL parameters to Stored Proc in Excel VBA

Posted on 2014-07-24
6
1,050 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In this article I will describe the Backup & Restore 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.
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…
Viewers will learn the basics of the new Quick Analysis feature in Excel 2013.
Viewers will learn how to share Excel data with others from desktop Excel, as well as Excel Online via OneDrive, and embed an Excel file on a website.

757 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

24 Experts available now in Live!

Get 1:1 Help Now