Pass null value to a stored proc.

[Stephen]

I have an Access Project with a list of contracts. When a user uses the
"new contract" form, they have the option to specify the employee name
(drop-down) who sold the contract. When then contract is saved, it passes
the contract informtion to a stored procedure to insert it into the
contracts table.

Cometimes, there is not an associated employee. In this case, I need to
pass NULL to the stored procedure. My syntax is:

conn.Execute ("spInsertClientMaintenanceContract @ClientID = " &
Me.ClientID & ", @StartDate = '" & Me.StartDate & "', @Expiration = '" &
Me.Expiration & "', @EmployeeID = " & Nz(Me.EmployeeID, NULL))

However, I cannot get the stored procedure to accept this null value.

How do I pass a NULL value to a stored procedure?

Help?

-Stephen

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't pass a NULL to an SP. You set up the SP so it automatically
defaults to NULL if the parameter is missing. This would mean you'd
have to test each parameter value in the VB code before you create the
call string for the conn.Execute() method.

SP:

CREATE PROCEDURE usp_MySP
@Param1 INTEGER ,
@Param2 VARCHAR(50) = NULL -- default is NULL
AS
.... etc. ...

VB:

dim strParams as string

If Not IsNull(Me!parameter1) Then
strParams = "@Param1 = " & Me!parameter1
End If

If Not IsNull(Me!parameter2) Then
strParams = strParams & ", @Param2 = '" & Me!parameter2 & "'"
End If

If Len(strParams)>0 Then
set rs = conn.execute ("usp_MySP " & strParams)
end if
.... etc. ...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQTUFwIechKqOuFEgEQJfpwCg8xfSqDWmOouwxj866vTLRbMeVt8AoLur
le8chWVhewCOGt0kHgJosG6c
=2YwX
-----END PGP SIGNATURE-----