Hi!



I am trying to use ADO to execute a stored procedure in SQL.  I would like 

to be able to have some of the parameters be null.  I have been searching 

around and think that I need to use the Parameter.Attributes or 

ParameterAttributesEnum and set it to adParamNullable.  I can't seem to 

get the right syntax to do that or find any sort of example of this out 

there.  If anyone has an idea of how to do this, any help you can give 

would be greatly appreciated.



Thanks,

Bill

Hi Bill,



I think you can do this within your Stored Procedure. e.g



-------------------------------------------------------



Create Procedure sp_Test

(

	@Parameter1 varchar(20) = NULL,

	@Parameter2 varchar(20) = NULL

)



AS



INSERT INTO .....

-------------------------------------------------------



The "= NULL" tells it to default to null if nothing is supplied.





Hope this helps.





-----Original Message-----

From: Bill Reynolds [mailto:breyn@g...]

Sent: 16 October 2001 15:07

To: ASPToday Discuss

Subject: [asptoday_discuss] ADO and Parameter.Attributes





Hi!



I am trying to use ADO to execute a stored procedure in SQL.  I would like

to be able to have some of the parameters be null.  I have been searching

around and think that I need to use the Parameter.Attributes or

ParameterAttributesEnum and set it to adParamNullable.  I can't seem to

get the right syntax to do that or find any sort of example of this out

there.  If anyone has an idea of how to do this, any help you can give

would be greatly appreciated.



Thanks,

Bill

Thanks Joe - will give it a try!



Thanks,

Bill Reynolds

Web Designer

Graphic Enterprises, Inc.

3874 Highland Park NW

North Canton, OH 44720.8080

xxx-xxx-xxxx  (p)

xxx-xxx-xxxx  (f)



   





-----Original Message-----

From: Joe Hughes [mailto:JoeHughes@M...]

Sent: Tuesday, October 16, 2001 3:35 PM

To: ASPToday Discuss

Subject: [asptoday_discuss] RE: ADO and Parameter.Attributes



Hi Bill,



I think you can do this within your Stored Procedure. e.g



-------------------------------------------------------



Create Procedure sp_Test

(

	@Parameter1 varchar(20) =3D NULL,

	@Parameter2 varchar(20) =3D NULL

)



AS



INSERT INTO .....

-------------------------------------------------------



The "=3D NULL" tells it to default to null if nothing is supplied.





Hope this helps.





-----Original Message-----

From: Bill Reynolds [mailto:breyn@g...]

Sent: 16 October 2001 15:07

To: ASPToday Discuss

Subject: [asptoday_discuss] ADO and Parameter.Attributes





Hi!



I am trying to use ADO to execute a stored procedure in SQL.  I would

like

to be able to have some of the parameters be null.  I have been

searching

around and think that I need to use the Parameter.Attributes or

ParameterAttributesEnum and set it to adParamNullable.  I can't seem to

get the right syntax to do that or find any sort of example of this out

there.  If anyone has an idea of how to do this, any help you can give

would be greatly appreciated.



Thanks,

Bill

I have not had a problem sending null values as input parms to stored 

procedures.  in fact, I have a generic stored proc execution module that I 

pass the command object into (by ref) once all the parms are defined and 

values are set.  Then in the command execution routine, I actually run 

through the parameter values change any zero length values to NULL 

deliberately.



This allows procs to run even when some optional values have not been 

collected in the application.  Here's the code excerp:



' ***** 6/29/01 ADDED GENERIC DATA CLEANUP on way to DB for empty fields 

******

' EMPTY PARM FIELD DATA CLEANUP - Set all fields with len = 0 to NULL. 

' This will prevent many ADO related errors by automatically converting 

empty string or empty values values to NULLS for DB saves.



IF  oCmd.Parameters.Count > 0 then

	For Each parm in oCmd.Parameters 

		IF Len ( parm ) = 0 then

			oCmd.Parameters ( parm.name ) = NULL

		END IF

	Next

END IF



'***********************************************************

' Run the DatabBase Command set up in the calling function *

'***********************************************************

oCmd.Execute

vRC			= oCmd.Parameters ("RETURN_VALUE")

sCmdMsgOut	= oCmd.Parameters ("@msg_out")



' *** end code excerp



I have had a baffling problem when I run a stored proc with a null value 

in a database field that does NOT allow nulls.



When this happens (in a late step withing my stored proc) I do not get any 

ASP Error or ADO connection error notification.  I only get an Empty value 

for the stored proc return code.  Catching this condition was a bit tricky 

to catch unless you are expecting it (my initial RC <> 0 check was not 

able to catch this!)



Hope this helps,



Rai Bianchi







> Hi!

> 

> I am trying to use ADO to execute a stored procedure in SQL.  I would 

like 

> to be able to have some of the parameters be null.  I have been 

searching 

> around and think that I need to use the Parameter.Attributes or 

> ParameterAttributesEnum and set it to adParamNullable.  I can't seem to 

> get the right syntax to do that or find any sort of example of this out 

> there.  If anyone has an idea of how to do this, any help you can give 

> would be greatly appreciated.

> 

> Thanks,

> Bill