[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

Set sqlDataAdapter Parameter to null in a web method

I am writing a webmethod that uses a sqlDataAdapter to call a stored procedure within my SQL database. The function arguments are used to pass parameters to the stored procedure which then filter the selected rows. I have written the stored procedure so that it will ignore parameters that are null if the user does not want to filter on those fields. My problem is that I don't know how to pass a null value to the sqlDataAdapter parameters collection.

How would I change this code to change my integer arguments to NULL if they are passed with a value of say ZERO indicating that this field should not be filtered on?

    <WebMethod()> Public Function GetTasks(ByVal strForeignTable As String, _
    ByVal intForeignKey As Integer, ByVal intOwner As Integer, _
    ByVal intSupervisor As Integer) As DataSetTasks

        Dim Tasks As New DataSetTasks
        With SqlDataAdapterTasks

            .SelectCommand.Parameters("@ForeignTable").Value = strForeignTable
            .SelectCommand.Parameters("@ForeignKey").Value = intForeignKey
            .SelectCommand.Parameters("@Owner").Value = intOwner
            .SelectCommand.Parameters("@Supervisor").Value = intSupervisor
        End With
        Return Tasks
    End Function
  • 2
1 Solution
if IntOwner<=0 then
 .SelectCommand.Parameters("@Owner").Value = DBNull.Value
.SelectCommand.Parameters("@Owner").Value = intOwner
end if

that should do it.
cbasultoAuthor Commented:
Thanks - that worked. I was trying to convert the integer argument to NULL and it was not working.
your welcome :)

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now