I am somewhat new to vb.net and asp.net (am a former VB6 turning .net
programmer)...

I have a textbox, which I would like to populate with criteria to show in
the datagrid. This works fine, however, when no text is found in the
textbox, there are no results to display. I would like to if the textbox is
blank, display all records and use the textbox as a filter...

Does anyone have any advice for me on this?

The offending code is below...

SelectCommand="SELECT [fldComputerName], [fldSystemModel], [fldAssetTag],
[fldServiceTag], [fldSupportExpiration], [fldServerApplication] FROM
[tblComputers]
WHERE ([fldComputerName] LIKE '%' + @fldComputerName + '%')">
<SelectParameters>

<asp:ControlParameter ControlID="txtServerName" Name="fldComputerName"
PropertyName="Text" Type="String" />
</SelectParameters>

First, LIKE might not be the best operator if you want to find a string
value in a column (as LIKE %<value>% does).
I suggest using CHARINDEX instead that returns 0 if the value is not found.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/78c10341-8373-4b30-b404-3db20e1a3ac4.htm

This helps the optimizer. In either case you'll likely trigger a row-scan
which is not efficient. If you're worried about performance, you'll need a
WHERE-clause strategy that leverages the column index. LIKE <value>% does,
LIKE %<value> does not.

Next, to alter the behavior of the WHERE clause to permit all rows, simply
add an OR that says if the value in the TextBox is blank (or some special
flag) then accept all the rows

... WHERE (@Parm = '') OR (CHARINDEX (@Parm, myCol) > 0)

Another approach (which might be more efficient is to only use the WHERE
clause when there is a value in the TextBox... but that's a more advanced
approach.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Lee Stevens" <(E-Mail Removed)> wrote in message
news61D2F1A-0468-4161-94E7-(E-Mail Removed)...
>I am somewhat new to vb.net and asp.net (am a former VB6 turning .net
> programmer)...
>
> I have a textbox, which I would like to populate with criteria to show in
> the datagrid. This works fine, however, when no text is found in the
> textbox, there are no results to display. I would like to if the textbox
> is
> blank, display all records and use the textbox as a filter...
>
> Does anyone have any advice for me on this?
>
> The offending code is below...
>
> SelectCommand="SELECT [fldComputerName], [fldSystemModel], [fldAssetTag],
> [fldServiceTag], [fldSupportExpiration], [fldServerApplication] FROM
> [tblComputers]
> WHERE ([fldComputerName] LIKE '%' + @fldComputerName + '%')">
> <SelectParameters>
>
> <asp:ControlParameter ControlID="txtServerName" Name="fldComputerName"
> PropertyName="Text" Type="String" />
> </SelectParameters>

Hello Lee,

Thanks for William's great advice.
... WHERE (@Parm = '') OR (CHARINDEX (@Parm, myCol) > 0)
This line should work for you.

However, please notice that the Parameter @fldComputerName will be NULL if
the textbox is empty.
This is because the "ConvertEmptyStringToNull" property of ControlParameter
is TRUE by default.
We have to change this property(ConvertEmptyStringToNull) to false.

<asp:ControlParameter ControlID="txtServerName" Name="fldComputerName"
PropertyName="Text" Type="String" *ConvertEmptyStringToNull="flase"*/>

By the way, after changed this property, your original SQL command
"...([fldComputerName] LIKE '%' + @fldComputerName + '%')"
will also display all records for you if the textbox is bank.

Hope this helps. Please feel free to let me know if you have anything
unclear. It's my pleasure to assist you.
Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Hello Lee,

This is Wen Yuan again. I just want to check if you have tried my method?
Does it work on your side?
Please let me know if the issue still persists. I'll follow up. It's my
pleasure to assist you.

Have a great day,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.