Problems with null text boxes when doing like search

I have three text boxes firstname, lastname, dob. It is set up to do a like search on the the text boxes. If a user wants to do a search for just the last name it will not work. You must type in something in all three boexes. Doesn't anybody know how to correct this. Here is the code. Thanks.

<%

@.PageLanguage="VB" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

scriptrunat="server">ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

GridView1.Visible =

False

GridView2.Visible =

FalseEndSubProtectedSub Button1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles Button1.Click

GridView1.DataSourceID =

"SqlDataSource1"

GridView1.DataBind()

GridView2.DataSourceID =

Nothing

GridView2.DataBind()

GridView1.Visible =

TrueEndSubProtectedSub Button2_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles Button2.Click

GridView2.DataSourceID =

"SqlDataSource2"

GridView2.DataBind()

GridView1.DataSourceID =

Nothing

GridView1.DataBind()

GridView2.Visible =

TrueEndSub

ProtectedSub SqlDataSource1_Selecting(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)EndSub

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headid="Head1"runat="server"><title>Meditech Radiology Numbers</title><linkhref="StyleSheet.css"rel="stylesheet"type="text/css"/>

</

head>

<

body><formid="form1"runat="server"><divid="master_headertop"><asp:HyperLinkID="HomePageLink"runat="server"ImageUrl="~/images/headertop_img.jpg"/></div>

<divid="master_menu"><divclass="sidebar"><divclass="sidebarheader">

Demographic search

</div><divid="master_sidebarSamples"class="sidebarcontent"> <table><tr><tdstyle="width: 100px"><asp:LabelID="Label1"runat="server"Text="First"></asp:Label></td><tdstyle="width: 100px"><asp:TextBoxID="FirstName"runat="server"></asp:TextBox></td></tr><tr><tdstyle="width: 100px; height: 26px;"><asp:LabelID="Label2"runat="server"Text="Last"></asp:Label></td><tdstyle="width: 100px; height: 26px;"><asp:TextBoxID="LastName"runat="server"></asp:TextBox></td></tr><tr><tdstyle="width: 100px"><asp:LabelID="Label3"runat="server"Text="DOB"></asp:Label></td><tdstyle="width: 100px"><asp:TextBoxID="DOB"runat="server"></asp:TextBox></td></tr><tr><tdstyle="width: 100px"></td><tdstyle="width: 100px"><asp:ButtonID="Button1"runat="server"Text="Search"/></td></tr></table></div></div><divclass="sidebar"><divclass="sidebarheader">

Search by Mr#

</div><divid="master_sidebarWalkthroughs"class="sidebarcontent"> <table><tr><tdstyle="width: 100px"><asp:LabelID="Label5"runat="server"Text="MR #"></asp:Label></td><tdstyle="width: 100px"><asp:TextBoxID="MR"runat="server"></asp:TextBox></td></tr><tr><tdstyle="width: 100px"></td><tdstyle="width: 100px"><asp:ButtonID="Button2"runat="server"Text="Search"/></td></tr></table></div></div> <br/> </div><divid="master_content"style="height: 399px">

<asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="False"DataSourceID="SqlDataSource1"><Columns><asp:BoundFieldDataField="LAST"HeaderText="LAST"SortExpression="LAST"/><asp:BoundFieldDataField="FIRST"HeaderText="FIRST"SortExpression="FIRST"/><asp:BoundFieldDataField="DOB"HeaderText="DOB"SortExpression="DOB"/><asp:BoundFieldDataField="RAD #"HeaderText="RAD #"SortExpression="RAD #"/><asp:BoundFieldDataField="MR#"HeaderText="MR#"SortExpression="MR#"/><asp:BoundFieldDataField="EXAM#"HeaderText="EXAM#"SortExpression="EXAM#"/><asp:BoundFieldDataField="EXAM DATE"HeaderText="EXAM DATE"SortExpression="EXAM DATE"/><asp:BoundFieldDataField="EXAM NAME"HeaderText="EXAM NAME"SortExpression="EXAM NAME"/><asp:BoundFieldDataField="ORDER DOC"HeaderText="ORDER DOC"SortExpression="ORDER DOC"/><asp:BoundFieldDataField="Column 9"HeaderText="Column 9"SortExpression="Column 9"/></Columns></asp:GridView><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:RADConnectionString2 %>"SelectCommand="SELECT * FROM [Full File through 2006] WHERE (([FIRST] LIKE '%' + @.FIRST + '%') AND ([LAST] LIKE '%' + @.LAST + '%') AND ([DOB] LIKE '%' + @.DOB + '%'))"OnSelecting="SqlDataSource1_Selecting"><SelectParameters><asp:ControlParameterControlID="FirstName"DefaultValue=" "Name="FIRST"PropertyName="Text"Type="String"/><asp:ControlParameterControlID="LastName"DefaultValue=" "Name="LAST"PropertyName="Text"Type="String"/><asp:ControlParameterControlID="DOB"DefaultValue=" "Name="DOB"PropertyName="Text"Type="String"/></SelectParameters></asp:SqlDataSource><asp:GridViewID="GridView2"runat="server"AutoGenerateColumns="False"DataSourceID="SqlDataSource2"><Columns><asp:BoundFieldDataField="LAST"HeaderText="LAST"SortExpression="LAST"/><asp:BoundFieldDataField="FIRST"HeaderText="FIRST"SortExpression="FIRST"/><asp:BoundFieldDataField="DOB"HeaderText="DOB"SortExpression="DOB"/><asp:BoundFieldDataField="RAD #"HeaderText="RAD #"SortExpression="RAD #"/><asp:BoundFieldDataField="MR#"HeaderText="MR#"SortExpression="MR#"/><asp:BoundFieldDataField="EXAM#"HeaderText="EXAM#"SortExpression="EXAM#"/><asp:BoundFieldDataField="EXAM DATE"HeaderText="EXAM DATE"SortExpression="EXAM DATE"/><asp:BoundFieldDataField="EXAM NAME"HeaderText="EXAM NAME"SortExpression="EXAM NAME"/><asp:BoundFieldDataField="ORDER DOC"HeaderText="ORDER DOC"SortExpression="ORDER DOC"/><asp:BoundFieldDataField="Column 9"HeaderText="Column 9"SortExpression="Column 9"/></Columns></asp:GridView><asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:RADConnectionString3 %>"SelectCommand="SELECT * FROM [Full File through 2006] WHERE ([MR#] = @.column1)"><SelectParameters><asp:ControlParameterControlID="MR"Name="column1"PropertyName="Text"Type="String"/></SelectParameters></asp:SqlDataSource></div><br/><br/>

</form>

</

body>

</

html>

I had a similar situation and solved it using a very cheap method. You might get a better method from someone else.

What I did was that I used to check if the textbox is empty, if it was, then I used to remove that part of the string from the full query string. So its a bunch of if-then to build a query string-

hope this helps,

bullpit

|||

I sure hope there is a better way cause i don't know how to do that. LOL Thanks for the help. I will try it not to sure how far i will get though.

|||

LIKE as you are using it should match anything that contains the string anywhere. The problem is that the Default Value is a space, so it is only trying to match a first name that contains a space. If you change

<asp:ControlParameterControlID="FirstName"DefaultValue=" "Name="FIRST"PropertyName="Text"Type="String"/>

To This

<asp:ControlParameterControlID="FirstName"DefaultValue=""Name="FIRST"PropertyName="Text"Type="String"/>

It should work for you. That is the same for last name, DOB...

|||Something like this:

string qString ="";if(TextBox1.Text.Trim() ==""){string st ="([FIRST] LIKE '%' + @.FIRST + '%') AND ")";if(qString.Contains(st)int i = strWhere.LastIndexOf(st); qString = qString.Remove(i, st.Length); }

bullpit|||

Benners_J:

LIKE as you are using it should match anything that contains the string anywhere. The problem is that the Default Value is a space, so it is only trying to match a first name that contains a space. If you change

<asp:ControlParameterControlID="FirstName"DefaultValue=" "Name="FIRST"PropertyName="Text"Type="String"/>

To This

<asp:ControlParameterControlID="FirstName"DefaultValue=""Name="FIRST"PropertyName="Text"Type="String"/>

It should work for you. That is the same for last name, DOB...

I think since it is an AND type query, no matter what you put there, if the AND criteria is not met, it won't show the results.

bullpit

|||

That is accurate. If the criteria is not met for any part of the WHERE clause, those records will not be returned. However, LIKE does a pattern match, and the '%' means match anything. The following query will return all records (I just tried in in SQL Server 2005, anyway).

SELECT * FROM [Table] WHERE [FirstName] LIKE('%%')

That is essentially what the query will run if @.FIRST is an empty string "". If @.FIRST contains a space, however, it will only match records where the first name contains a space.

|||

Yup, I tried that too after you pointed it out and it works.

Thanks,

bullpit

|||

I have tried what yall suggested, but having no luck. Is the below right?

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:RADConnectionString2 %>"SelectCommand="SELECT * FROM [Full File through 2006] WHERE (([FIRST] LIKE '%%' + @.FIRST + '%%') AND ([LAST] LIKE '%%' + @.LAST + '%%') AND ([DOB] LIKE '%%' + @.DOB + '%%'))"OnSelecting="SqlDataSource1_Selecting"><SelectParameters><asp:ControlParameterControlID="FirstName"DefaultValue=""Name="FIRST"PropertyName="Text"Type="String"/><asp:ControlParameterControlID="LastName"DefaultValue=""Name="LAST"PropertyName="Text"Type="String"/><asp:ControlParameterControlID="DOB"DefaultValue=""Name="DOB"PropertyName="Text"Type="String"/></SelectParameters></asp:SqlDataSource>|||

Why do you have two %s in your query?

bullpit

|||

Have your like clause like this:

LIKE '%' +@.FIRST+ '%'

bullpit

|||I thought that was the change that was suggested inBenners_J post. What was suggested?|||

You got confused with this query. He gave this query just to show the difference when there is a blank space in the input field. This is not what he wanted you to implement. He just wanted you to set the default value = "" instead of " ".

SELECT * FROM [Table] WHERE [FirstName] LIKE('%%')

hope this helps,

bullpit

|||Oh i had tried that already that is why there were spaces in there. Have tried it with spaces and without. Still want work.|||

I am talking about removing the spaces in the deafult value of the textbox. Are you talking about the same thing?