ObjectDataSource Not Passing DefaultValue to Table Adapter

Question

I have encountered a really weird problem. I am swapping the data source on an ASP.Net ListView from a SqlDataSource to an ObjectDataSource. Everything is working well except two parameters are not being passed from the ObjectDataSource to the Table Adapter. There are four total parameters. Three integers and one string. They are named search, state, category, and subcategory. All have default values that are changed whenever the script manager navigates or a button is clicked. Everything works well except for when category and subcategory are null.

When category and subcategory are null the null values are not passed to table adapter and no data is pulled from the database. I know that the stored procedure and the table adapter are just fine because when I test the query in the database it does just fine when category and subcategory are null. Likewise the preview data from the table adapter in my .xsd file comes back just fine when I test it.

The problem occurs when I run the .aspx page in my browser. There are no records. At first I thought it was a problem passing null values, but when I figured out that the search parameter was being passed as null when empty I realized it was something with category and subcategory specifically. Then I thought it might be due to using null values for integers, so I tried changing my SQL query to work when category = @category OR @category = 0, same for subcategory. Then I set the DefaultValue for both to 0 just like I did for state. That did not solve the problem, it just caused the ObjectDataSource not to pass the 0s for those two parameters.

My ObjectDataSource looks like so

                    <SelectParameters>
                        <asp:Parameter Name="search" Type="String" ConvertEmptyStringToNull="True" DefaultValue="" />
                        <asp:Parameter Name="category" Type="Int32" ConvertEmptyStringToNull="True" DefaultValue="" />
                        <asp:Parameter Name="subcategory" Type="Int32" ConvertEmptyStringToNull="True" DefaultValue="" />
                        <asp:Parameter Name="state" Type="Int32" DefaultValue="0" />
                    </SelectParameters>
                </asp:ObjectDataSource>```
My BLL looks like so:
``` private PostsTableAdapters.GetPostsPagedTableAdapter _postsAdapter = null;
    protected PostsTableAdapters.GetPostsPagedTableAdapter Adapter
    {
        get
        {
            if (_postsAdapter == null)
                _postsAdapter = new PostsTableAdapters.GetPostsPagedTableAdapter();

            return _postsAdapter;
        }
    }
    [System.ComponentModel.DataObjectMethodAttribute(
    System.ComponentModel.DataObjectMethodType.Select, false)]
    public Posts.GetPostsPagedDataTable GetPostsPaged(int startRowIndex, int maximumRows, string search, int category, int subcategory, int state)
    {
        return Adapter.GetPostsPaged(startRowIndex, maximumRows, search, category, subcategory, state);
    }
    public int GetPostsCount(string search, int category, int subcategory, int state)
    {
        return (int)Adapter.GetPostsCount(search, category, subcategory, state);
    }
    [System.ComponentModel.DataObjectMethodAttribute(
    System.ComponentModel.DataObjectMethodType.Select, false)]
    public Posts.GetPostsPagedDataTable GetPostsActivePaged(int startRowIndex, int maximumRows)
    {
        return Adapter.GetPostsActivePaged(startRowIndex, maximumRows);
    }
    public int GetPostsActiveCount()
    {
        return (int)Adapter.GetPostsActiveCount();
    }```

My stored procedure looks like so:
```ALTER PROCEDURE [dbo].[GetPostsPaged]
(
    @startRowIndex int,
    @maximumRows int,
    @search nvarchar(max),
    @category int,
    @subcategory int,
    @state int
)
AS
    SELECT     postid, posttitle, city, image, imagename, date, statename, stateslug, catname, catslug, scatname, scatslug
FROM
   (
       SELECT posts.postid, posts.posttitle, posts.city, posts.image, posts.imagename, posts.date, states.statename, states.stateslug, categories.catname, categories.catslug, subcategories.scatname, subcategories.scatslug,
       ROW_NUMBER() OVER (ORDER BY posts.date DESC) AS RowRank
       FROM posts INNER JOIN categories ON posts.category = categories.catid INNER JOIN states ON posts.state = states.stateid INNER JOIN subcategories ON posts.subcategory = subcategories.scatid
       WHERE (posts.banned = 0) AND (posts.active = 1) AND (posts.posttitle LIKE '%' + @search + '%' OR posts.postbody LIKE '%' + @search + '%' OR posts.city LIKE '%' + @search + '%' OR posts.hashtag LIKE '%' + @search + '%' OR @search IS NULL) AND (posts.category = @category OR @category IS NULL) AND (posts.subcategory = @subcategory OR @subcategory IS NULL) AND (posts.state = @state OR @state = 0)
    ) AS PostsWithRowNumbers
WHERE RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)

Answer 1

What if you change your parameter to a nullable type? I don't think Int32 will work with a null. Try Nullable or "Int32?" maybe?

Answer 2

I ended up having to change the null integers to 0s in both the GetPostsPaged and GetPostsPagedCount queries and set the default values as 0.