Passing Params To Stored Procedure

[jo15765]

I asked a question a few days ago about how to hide/unhide text box/labels on a user form here: https://www.accessforums.net/showthread.php?t=65341



I now need a way to pass the values into a SQL Stored Procedure - which I will use an ADODB connection and pass the params that way. Now here is where I am unclear on how to code....

Say I have 4 text boxes on the user form that could be populated, obviously I would need 4 params in the stored procedure. Would it be best to set things up like this in my VBA

With cmd.Parameters
    .Append cmd.CreateParameter("Param1", adVarChar, adParamInput, 8000, textbox1.Value)
    .Append cmd.CreateParameter("Param2", adVarChar, adParamInput, 8000, textbox2.Value)
    .Append cmd.CreateParameter("Param3", adVarChar, adParamInput, 8000, textbox3.Value)
    .Append cmd.CreateParameter("Param4", adVarChar, adParamInput, 8000, textbox4.Value)
End With

And check for null values on the SQL side of things?

[jwhite]

See: https://www.accessforums.net/showthread.php?t=65502

[jo15765]

That shows how to pass parameters. My question is about how to handle null parameters.

[jwhite]

Apologies, mis-read.

For parameters where the input to the proc can be Null, here is an example:

Set prmGeneral = .CreateParameter("XYZ", adInteger, adParamInput)
If lngNewStatus = 0 Then
prmGeneral.Value = Null
Else
prmGeneral.Value = lngNewStatus
End If
.Parameters.Append prmGeneral

Then in the proc check for Null if required and process accordingly.