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.
gilweber3Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Nuno RogadoConnect With a Mentor Commented:
Please change:

If intDDA = 0 Then
       intDDA = Null
     Else

to:

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

for all 3 parameters
2
 
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
 
gilweber3Author Commented:
Outstanding, that did the trick. Thanks for taking a look at this.
0
All Courses

From novice to tech pro — start learning today.