Set sqlDataAdapter Parameter to null in a web method

Posted on 2004-11-17
Last Modified: 2012-05-05
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
Question by:cbasulto
    LVL 3

    Accepted Solution

    if IntOwner<=0 then
     .SelectCommand.Parameters("@Owner").Value = DBNull.Value
    .SelectCommand.Parameters("@Owner").Value = intOwner
    end if

    that should do it.

    Author Comment

    Thanks - that worked. I was trying to convert the integer argument to NULL and it was not working.
    LVL 3

    Expert Comment

    your welcome :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit ( and similar technologies have enjoyed wide adoption, making it possib…
    Envision that you are chipping away at another e-business site with a team of pundit developers and designers. Everything seems, by all accounts, to be going easily.
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now