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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gilweber3Author Commented:
Outstanding, that did the trick. Thanks for taking a look at this.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.