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.

0


source to share


2 answers


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.

+1


source


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

      

0


source







All Articles