Help With Null Value Problem in asp.net DB Query

[dascott20]

I've been working on building a back end on an access database that allows users to search records using a web search box. I've been able to successfully set up simple search query's but have run into trouble where multiple criteria with "AND" statements (OR statements would return too much to be useful). My problem is this, when all five text boxes are filled out the query returns records no problem, but if even one is left blank i get nothing what so ever. Is there any thing I can do in either SQL or VB to either ignore null values entirely or convert them to wildcard values?

[dascott20]

The SQL code is:

SELECT [Full Title] AS Full_Title, [Author List] AS Author_List, [Title Call Number] AS Title_Call_Number, Series, Location 
FROM Library 
WHERE ([Full Title] LIKE '%' + ? + '%') AND ([Author List] LIKE '%' + ? + '%') AND ([Title Call Number] LIKE '%' + ? + '%') AND (Location LIKE '%' + ? + '%') AND ([Search Terms] LIKE '%' + ? + '%') ORDER BY [Full Title]

[Frinavale]

Could you use an OR instead of an AND in your SQL query to get what you're looking for?

If not, I suggest that you build it according to what the user has entered:

Dim sqlQuery as String = " SELECT [Full Title] AS Full_Title, [Author List] AS Author_List, [Title Call Number] AS Title_Call_Number, Series, Location " _
" FROM Library "_
" WHERE "
 
Dim whereStatement As String
If String.IsNullOrEmpty(txt_Title) = False Then
 whereStatement = "([Full Title] LIKE '%' + ? + '%')"
End If
 
If String.IsNullOrEmpty(txt_AuthorList) = False Then
  whereStatement = " AND ([Author List] LIKE '%' + ? + '%')"
End If
 
'....

The second approach is messy so I suggest you check out the first suggestion first :)

-Frinny

[dascott20]

@Frinavale
Lol Sorry the first suggestion was the code I am currently using and it doesn't display null values. I'll try this approach though. Messy is ok It has to be easy on the user end not necessarily for me :P. I could use an OR statement but that risks information overload on the user end.

[Frinavale]

Perhaps ask the Access Experts how to create a sql query that best suits your needs before pursuing the messy path :)

[NeoPa]

Unless you have the ANSI-92 option set in Access you would use '*' instead of '%'.

String concatenation is normally done with & rather than + (although + should work in most cases. See Using "&" and "+" in WHERE Clause).

I'm not sure what the ? means or does in your code, but that would need to be replaced by the value (or Null) of whatever the operator selected.

Otherwise, it seems you have the fundamental concept right already.

Welcome to Bytes!

[dascott20]

I apologize there seems to be miscommunication. The Access Database is already up and functional. Where I am having trouble is with Asp.net. In Access for a wildcard query if a field is left blank then acess automatically ignores that field and pulls up the rest of the results based on the the others(i.e. if i have four fields for search criteria and leave one blank, Access will ignore that one and search the other three.) whereas if a box is left blank in asp.net it will look for a null value in that field and since that field is not blank in the database that record will be omitted despite the contents of the other three fields. I'm looking to simulate what is going on in Access.

In an idea inspred by Frinavale I am currently trying to get the "SelectCommand=" to change depending on what textboxes actually have a string by using a VB event.

Here's what I have so far:

Public Class SearchResults2
    Inherits System.Web.UI.Page
    Dim sqlQuery As String
    Dim whereStatement As String
 
 
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
 
    End Sub
 
    Private Sub SearchResults2_PreLoad(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreLoad
        Library.SelectCommand = " SELECT [Full Title] AS Full_Title, [Author List] AS Author_List, [Title Call Number] AS Title_Call_Number, [Series] AS Series, [Location] AS Location"
        Library.SelectCommand &= "FROM Library "
        Library.SelectCommand &= "WHERE "
        If Not String.IsNullOrWhiteSpace(TSearch.Text) Then
            Library.SelectCommand &= "([Full Title] LIKE '%' + ? + '%')"
        ElseIf String.IsNullOrWhiteSpace(TSearch.Text) Then
            Library.SelectCommand &= "([Full Title] Like '%')"
 
        End If
 
        If Not String.IsNullOrWhiteSpace(AUSearch.Text) Then
            Library.SelectCommand &= " AND ([Author List] LIKE '%' + ? + '%')"
        ElseIf String.IsNullOrWhiteSpace(AUSearch.Text) Then
            Library.SelectCommand &= " AND ([Author List] Like '%')"
 
        End If
 
        If Not String.IsNullOrWhiteSpace(CNSearch.Text) Then
            Library.SelectCommand &= " AND ([Title Call Number] LIKE '%' + ? + '%')"
        ElseIf String.IsNullOrWhiteSpace(CNSearch.Text) Then
            Library.SelectCommand &= " AND ([Title Call Number] LIKE '%')"
 
        End If
 
        If Not String.IsNullOrWhiteSpace(LSearch.Text) Then
            Library.SelectCommand &= " AND ([Title Call Number] LIKE '%' + ? + '%')"
        ElseIf String.IsNullOrWhiteSpace(LSearch.Text) Then
            Library.SelectCommand &= " AND ([Title Call Number] LIKE '%')"
 
        End If
        If Not String.IsNullOrWhiteSpace(KWSearch.Text) Then
            Library.SelectCommand &= " AND ([Search Terms] LIKE '%' + ? + '%')"
        ElseIf String.IsNullOrWhiteSpace(KWSearch.Text) Then
            Library.SelectCommand &= " AND ([Search Terms] LIKE '%')"
        End If
    End Sub
 
    Private Sub SearchResults2_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRender
        TSearch.Text = Request.QueryString("Title").ToString
        AUSearch.Text = Request.QueryString("Author").ToString
        CNSearch.Text = Request.QueryString("CallNumber").ToString
        LSearch.Text = Request.QueryString("Location").ToString
        KWSearch.Text = Request.QueryString("Key_Words").ToString
 
 
 
    End Sub
 

[dascott20]

@dascott20
Keep In mind, that I was hired on as a student in net admin, not programming or web development. However this is my tasking at work so I can't hand it off. So please leave clear suggestions (Small words and color pictures appreciated :P).

[Frinavale]

What you have should work (are you having problems with it?) but I recommend that you create your query using a StringBuilder instead of concatenating everything into the Library.SelectCommand. The reason I recommend this is because it takes less resources to use a StringBuilder than to concatenate strings together (strings are immutable and cannot be changed so when you add 2 together you are creating a new string...whereas a StringBuilder is better for performance).

Please note that this is not really necessary to accomplish your goal but will speed up your application slightly.

-Frinny

[dascott20]

This is to let everyone know that I have solved this issue, the answer posted by Frinavale below was very helpful in that process. If you are reading this to solve a simisare problem, below is a solid setup, with two important factors to remember.

1)Declare All your variables.

2)Keep in mind what order you are coding youre commands.