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?
-
-
Code
The SQL code is:
Code: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]
-
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:
The second approach is messy so I suggest you check out the first suggestion first :)Code: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 '....
-FrinnyComment
-
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.Comment
-
Perhaps ask the Access Experts how to create a sql query that best suits your needs before pursuing the messy path :)Comment
-
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!Comment
-
asp.net where I'm at now
I apologize there seems to be miscommunicatio n. 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:
Code: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 SubComment
-
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.SelectC ommand. 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...wherea s 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.
-FrinnyComment
-
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.Comment
Comment