Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1306
  • Last Modified:

Passing NULL parameters to Stored Proc in Excel VBA

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
gilweber3
Asked:
gilweber3
  • 2
  • 2
1 Solution
 
Nuno RogadoCommented:
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
 
gilweber3Author Commented:
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
 
Nuno RogadoCommented:
Please change:

If intDDA = 0 Then
       intDDA = Null
     Else

to:

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

for all 3 parameters
2
 
gilweber3Author Commented:
Outstanding, that did the trick. Thanks for taking a look at this.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now