>> adodb.parameter to handle nulls

[Jonathan]

Hi, using Access 2003 and sql 2005. I hope that this is not a double up as I
received a posting error notice for my previous effort!

I am using fields from a recordset to create parameters for a stored
procedure. The problem is that 1 or more field may contain a null value and
the system spits the dummy when prm=null.

Dim prm As ADODB.Parameter
Dim fld As ADODB.Field
....
Set prm = cmd.CreateParameter("@" & fld.Name, fld.Type, adParamInput,
Len(nz(fld.value),"")), fld.value)

Is there a way to pass null using a parameter to a stored procedure?

That is, using nz(fld.value, ?? ) what can I replace ?? with - bearing in
mind that a field could be text, numeric or date.

Any suggestions or ideas appreciated
Many thanks,
Jonathan

 

[Sylvain Lafontaine]

Did you try using the Null value?

Set prm = cmd.CreateParameter("@" & fld.Name, fld.Type, adParamInput,
Len(nz(fld.value),"")), Null)

or:
cmd.CreateParameters("@" & fld.Name).value = Null


Usually, the best way would be simply to do (or enter) nothing when you have
a null value:

Set prm = cmd.CreateParameter("@" & fld.Name, fld.Type, adParamInput, 0)


Alternatively, you can use a variable set to the null value or set the value
to Empty:

Declare n as variant
n = null
cmd.CreateParameters("@" & fld.Name).value = null

or:

cmd.CreateParameters("@" & fld.Name).value = Empty

Notice also that if the property NamedParameters is set to False (the
default); the local names of the parameters (such as « "@" & fld.Name ») are
irelevant and only the ordinal order of the parameters (ie., the order in
which they have been created) will be used when transmitting the values of
the parameters to the stored procedure.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Jonathan]

Excellent, thanks Sylvain

Jonathan

Did you try using the Null value?

Set prm = cmd.CreateParameter("@" & fld.Name, fld.Type, adParamInput,
Len(nz(fld.value),"")), Null)

or:
cmd.CreateParameters("@" & fld.Name).value = Null


Usually, the best way would be simply to do (or enter) nothing when you have
a null value:

Set prm = cmd.CreateParameter("@" & fld.Name, fld.Type, adParamInput, 0)


Alternatively, you can use a variable set to the null value or set the value
to Empty:

Declare n as variant
n = null
cmd.CreateParameters("@" & fld.Name).value = null

or:

cmd.CreateParameters("@" & fld.Name).value = Empty

Notice also that if the property NamedParameters is set to False (the
default); the local names of the parameters (such as « "@" & fld.Name ») are
irelevant and only the ordinal order of the parameters (ie., the order in
which they have been created) will be used when transmitting the values of
the parameters to the stored procedure.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)