DbNull.Value Stored Procedure Parameter?
I have a web service that has a generic function that returns a dataset from the results of stored procedures ... Some of the stored procedures have optional parameters where the value can be null, but not all the time.
Anyway, I am trying to pass a parameter that is DBNull.Value
and I get this An error occured while creating the XML document. back after that
Leaving this setting aside, it works great ... but really would like to know why DBNull.Value is causing this problem.
source to share
I suppose because the System.DBNull value is null in the database table, but a null field in the procedure is effectively equivalent to the null / nothing keyword. Not null. I'm not sure about the technical differences under the hood.
But in your saved proc, you can just default to its null and not send the value like you already did, or I believe if you sent null / nothing it will work as well.
source to share
You can pass a NULL value to the SqlParemeter, but you must do some type conversion to ensure that the correct null value is passed.
This example has a parameter called "Count", which is an integer that is passed in as null:
Using dtResult as New DataTable
Using cn as SqlConnection = New SqlConnection ("ConnectionString")
Using sqlcmd as SqlCommand - New SqlCommand("StoredProceName", cn) with {.CommandType=CommandType.StoredProcedure}
Dim sp As SqlParameter
sp = sqlcmd.Parameters.Add("@Count", SqlDbType.Int)
sp.value = CType(Nothing, SqlTypes.SqlInt32)
Using myDR as SqlDataReader = sqlcmd.ExecuteReader
dtResult.Load(myDR)
end using
return dtResult
End Using ' For sqlcmd
cn.Close() ' Close the connection
end using ' For cn
end using ' For dtResult
source to share