Classic ASP Parameters passing nulls (or empty parms) and ordering

I'm using classic ASP and trying to create 开发者_JS百科parameters for a stored procedure call for a procedure that has optional (=NULL) parameters. One of the parameters, @maxrows, is required.

When I try to pass via the following call:

With objCommand
.ActiveConnection = oConn
.CommandText = "usp_client_onsite_search"
.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter("maxRows",adInteger,adParamInput,4,10)
.Parameters.Append .CreateParameter("Firstname", adVarchar, adParamInput,50 , vbnull)

End With

SQL server shows this "exec usp_client_onsite_search 10,'1'"

In other words, the parameters aren't being passed according to name; they're passed according to order. And some might not be present in each call.

I'm not sure if you're asking about NULL or parameter order.

On the parameter order, if you set the NamedParameters property of the command object to 'True', then parameters will be passed through by name (see the MSDN for more details on this property)

As for the NULL, instead of passing vbNull, try passing Null. However I'm not sure if this would work or not.

However if you get the named parameters working, you may be able to do if there are default values for the parameters in the stored procedure you're calling. This way simply not specifying the parameter will give a NULL value in the target sproc.

IIRC this behavior depends on the SQL Driver you use. I have seen this behaviour in the past too, and changing the driver would correct this behavior.

For SQL Server, there are several options for ADO, such as de ODBC driver, the OLEDB driver, the MSDASQL driver, the SQL Server Native Client driver, etc. Which one are you using?

Try this instead:

.Parameters.Append 
.CreateParameter("Firstname", adVarchar, adParamInput , 50 , Null )