DataSource failing with null integer parameter

Discussion in 'ASP .Net Web Controls' started by Assimalyst, Oct 13, 2006.

  1. Assimalyst

    Assimalyst Guest

    Hi,

    I am trying to create a search page using a number of fields, mostly
    strings, one is an integer. That allows the user to use any combination
    of the fields to search with.

    I have a datasource configured using the asp.net (v2.0) tools and the
    parameters are passed from controls to a stored procedure. I have set
    default values for the strings to '%' and left the default for the
    integer blank. When i test the query in configuration it works fine
    with a blank integer. But when i run it in the webpage the datasource
    returns null if the integer field is blank. I have checked this with
    the following code:

    protected void gvSearchResults_DataBound(object sender, EventArgs e)
    {
    DataView DV =
    (DataView)this.DataSourceSearchResultsGV.Select(DataSourceSelectArguments.Empty);
    if (DV != null)
    {
    . . . .

    If i remove the integer field from the query it all works fine, if i
    enter an integer it works fine. So it seems to be some sort of issue
    converting the blank textbox control text (that the integer is passed
    from) for use in the datasource.

    Any ideas how to fix this?

    Here's the query:

    @pkiBarcodeNumber varchar(8),
    @szFirstName varchar(24),
    @szSurname varchar(40),
    @szDateOfBirth varchar(8),
    @szPostcode varchar(8)

    AS


    SELECT pkiBarcodeNumber, szFirstName, szSurname, szDateOfBirth,
    szPostcode
    FROM tblApplicationForms
    WHERE pkiBarcodeNumber = CASE WHEN @pkiBarcodeNumber IS NULL THEN
    pkiBarcodeNumber ELSE @pkiBarcodeNumber END
    AND szFirstName LIKE '%' + @szFirstName + '%'
    AND szSurname LIKE '%' + @szSurname + '%'
    AND szDateOfBirth LIKE '%' + @szDateOfBirth + '%'
    AND szPostcode LIKE '%' + @szPostcode + '%'
    ORDER BY pkiBarcodeNumber

    Thanks
    Assimalyst, Oct 13, 2006
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Alan Silver
    Replies:
    2
    Views:
    6,686
    Alan Silver
    Jul 12, 2005
  2. Narshe
    Replies:
    4
    Views:
    13,205
    Teemu Keiski
    Dec 26, 2005
  3. Replies:
    5
    Views:
    26,597
    Mike Schilling
    Mar 29, 2006
  4. Ben
    Replies:
    1
    Views:
    1,594
    Alexey Smirnov
    Jun 11, 2007
  5. Replies:
    1
    Views:
    88
    Matt Garrish
    Dec 30, 2004
Loading...

Share This Page