Caution when passing sqlparameter in. Net Environment

Check the followings out, I encounter with an exception when running the following code:

Datalist1.datasource = sqlhelper. executedatatable (commandtype. storedprocedure, "artspgetpainting01", new sqlparameter ("@ isforrecentorsearch", 0 ));


And I get the following message:

Procedure or function 'artspgetpainting01 'expects parameter' @ isforrecentorsearch', which was not supplied.

Open the immediate window and input the command to check the values for testing:

? New sqlparameter ("@ isforrecentorsearch", 1)

{@ Isforrecentorsearch}

Base {system. Data. Common. dbparameter }:{@ isforrecentorsearch}

Compareinfo: None

Dbtype: int32

Direction: Input

Isnullable: false

Localeid: 0

Offset: 0

Parametername: "@ isforrecentorsearch"

Precision: 0

Scale: 0

Size: 0

Sourcecolumn :""

Sourcecolumnnullmapping: false

Sourceversion: Current

Sqldbtype: int

Sqlvalue: {1}

Typename :""

Udttypename :""

Value: 1

Xmlschemacollectiondatabase :""

Xmlschemacollectionname :""

Xmlschemacollectionowningschema :""

? New sqlparameter ("@ isforrecentorsearch", 0)

{@ Isforrecentorsearch}

Base {system. Data. Common. dbparameter }:{@ isforrecentorsearch}

Compareinfo: None

Dbtype: int64

Direction: Input

Isnullable: false

Localeid: 0

Offset: 0

Parametername: "@ isforrecentorsearch"

Precision: 0

Scale: 0

Size: 0

Sourcecolumn :""

Sourcecolumnnullmapping: false

Sourceversion: Current

Sqldbtype: bigint

Sqlvalue: NULL

Typename :""

Udttypename :""

Value: NULL

Xmlschemacollectiondatabase :""

Xmlschemacollectionname :""

Xmlschemacollectionowningschema :""

? New sqlparameter ("@ isforrecentorsearch", (object) 0)

{@ Isforrecentorsearch}

Base {system. Data. Common. dbparameter }:{@ isforrecentorsearch}

Compareinfo: None

Dbtype: int32

Direction: Input

Isnullable: false

Localeid: 0

Offset: 0

Parametername: "@ isforrecentorsearch"

Precision: 0

Scale: 0

Size: 0

Sourcecolumn :""

Sourcecolumnnullmapping: false

Sourceversion: Current

Sqldbtype: int

Sqlvalue: {0}

Typename :""

Udttypename :""

Value: 0

Xmlschemacollectiondatabase :""

Xmlschemacollectionname :""

Xmlschemacollectionowningschema :""

Google exception message, and find the resolution of this issue:

Http://msdn.microsoft.com/zh-cn/library/0881fz2y.aspx
When an object is specified in the value parameter, sqldbtype is inferred from the. NET Framework type of the object.
Use this overload of the sqlparameter constructor to specify the integer parameter value. Because this overload accepts the object type value, when this value is zero, the integer must be converted to the object type, as shown in the C # example below.
Parameter = new sqlparameter ("@ pname", convert. toint32 (0 ));
If this conversion is not performed, the compiler considers that you are trying to call the sqlparameter (string, sqldbtype) constructor overload.

Http://msdn.microsoft.com/en-us/library/0881fz2y.aspx
When you specify an object in the value parameter, the sqldbtype is inferred from the Microsoft. NET Framework type of the object.
Use caution when you use this overload of the sqlparameter constructor to specify integer parameter values. because this overload takes a value of type object, you must convert the integral value to an object type when the value is zero, as the following C # example demonstrates.
Parameter = new sqlparameter ("@ pname", convert. toint32 (0 ));
If you do not perform this conversion, the compiler assumes that you are trying to call the sqlparameter (string, sqldbtype) constructor overload.

In briefly, when input '0', the program invoke sqlparameter constructor 'sqlparameter (string, sqldbtype) ', not 'sqlparameter (string, object )', the essential is that zero can not be convert to 'object' value implicitly.

Pertinent info: