null from array to sqlserver

[pettrer]

Hi,

I need to input a null value in a stored procedure (SqlServer). The values are coming from an array in VB.Net.

vinparam(3) = Nothing doesn't seem to cut it (I get an error during runtime saying the value is missing).

vinparam(3) = System.DBNull doesn't work.

As you probably expect, it's the value for vinparam(3) that's supposed to be null (in SqlServer management Studio I use

uv_Mom 'xxx', null, 1

).

So what should I use?

Thanks a lot!

Pettrer

 

[pettrer]

Hi,

I just figured it out!

First I set a string to "null" (it could be named anything) and then, when inserting the parameters, I convert the values with a string set to "null" to the SqlServer equivalent:

vinparam(3) = "null"

(or in my case, I first define the name of the sql parameter and then use a function to retrieve the value:

vinparam(2) = "@Ktider"
vinparam(3) = fKtider()

Private Function fKtider()
        If radioBA.Checked Then
            fKtider = "OL"
        ElseIf radioBB.Checked Then
            fKtider = "NE"
        Else
            fKtider = "null"
        End If
    End Function

)

When the array is filled, I call the fillmydatagridview function:
dim sp as string = "mystoredprocedure"
dgvMygridDataSource = fillmydatagridview(sp, thearray)

Public Function fillmydatagridview(ByVal StoreProc As String, ByVal ParamArray Params() As Object) As DataTable
        Dim cmd As New SqlCommand
        Dim da As New SqlDataAdapter
        Dim i As Integer
        Dim prm As New SqlParameter
        fillmydatagridview = Nothing

        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = StoreProc
        cmd.Connection = myconn
        'even ones are the names, odd ones are the values (see above)
        For i = 0 To Params.Length - 1 Step 2
            prm = New SqlParameter
            prm.ParameterName = Params(i)
            prm.Value = Params(i + 1)
            If prm.Value = "null" Then prm.Value = SqlTypes.SqlChars.Null
            cmd.Parameters.Add(prm)
        Next

        da.SelectCommand = cmd

        fillmydatagridview = New System.Data.DataTable("table")
        da.Fill(fillmydatagridview)
    End Function

So, the important lines are these:

vinparam(3) = "null"

If prm.Value = "null" Then prm.Value = SqlTypes.SqlChars.Null

Worked like a charm for me!

Pettrer

 

[pettrer]

When I come to think about it, I guess another conversion row must be inserted to deal with integer or datetime fileds, for example. I'll post an update if I'll need this code myself.

Likewise, I could as well have set the value to nothing instead of "null", but having set the string to "null" would simplify in the conversion process (if the value was nothing, I wouldn't know if it was a string, integer, and so forth).

Pettrer

 

[cjard]

I never accurately understood your question..

Are you saying youre attempting to pass a String() [ Array Of Strings ] to a stored procedure, and because one of the array elements is null, it is being rejected?

You wont succeed to set a string to DBNull.Value because they are very different. Further, you didnt state what version of what database youre using, so I can only recommend you start with a google search:

pass array of strings to sqlserver stored procedure - Google Search

 

[pettrer]

cjard,

Thanks for trying to help, but I worked it out (see posts above).

Pettrer

 

[cjard]

Indeed, though I think there are better solutions. Note your code will be prone to bugs because you do not appear to have Option Strict/Explicit turned on; youre declaring functionsthat have no return type. Additionally, if you used the dataset designer to create the code necessary to access your stored procedure, I don't think you'd have ever encountered this problem.. The code you have is a little "ropey"