sqlsql-servervb.netnullabledbnull

How to Consider NOTHING in VB.Net to NULL in SQL Server via NULLABLE Variable

Parameters in Stored Procedure are @classid INT = NULL, @streamid INT = NULL. Now in VB.NET I have declared two variable Dim classid As Int32?, streamid As Int32? as NULLABLE. If no value is passed to these parameters then it pass NOTHING.

Does NOTHING is considered as NULL in SQL Server or I have to pass DBNULL.Value explicity from code while passing parameters to SP?

Because when I am executing my SP directly from SQL Server then it showing me results but if I am calling my SP from application then it showing me no records.

Passing the parameters from Code:

cmd.SelectCommand.Parameters.AddWithValue("@class", classid.GetValueOrDefault())
cmd.SelectCommand.Parameters.AddWithValue("@stream", streamid.GetValueOrDefault())

I am passing both the parameters as above.

Solution

  • 1: parameters you declared in stored procedure are

    @classid INT = NULL, 
@streamid INT = NULL

    but passing parameters from VB.Net are

    @class
@stream

    you must have the same parameters in boths sides to keep it working.

    2: parameters should be declared as

    @classid INT NULL, 
@streamid INT NULL