Listview result with Where Clause and querystringparameter

Question

User-1634071706 posted

Hi there,

I am trying to publish Access datasource result in aspx page using listview in web expression. Challenge is my datasource field has multiple words and I am trying to filter the result based on specific word.

If I am specifying a word to Default value it is only showing " No Data found Result".

If I specify all the words from the field to Default Value then it shows the result.

See below the Column Trend, it has multiple words in a field. If I specify Stripe in Default Value it doesn't return any record but if I specify Stripe,Fabric then it returns two fields.

My question Is there any wildcard I need to specify to filter a word? Any suggestion will be great! Thx in advance!

Trend

Stripe,Fabric

Stripe,Fabric

Stripe,CB,NP

Stripe,CB,NP

Stripe,CB,NP

Stripe Woven

Stripe, Hoody

Stripe, Polo

Coogi, Stripe

Dragon, Striped

<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="result.mdb" SelectCommand="SELECT [Image] AS Image FROM [File1] WHERE ([Trend] = ?) ORDER BY [ID]">
            <SelectParameters>
                <asp:querystringparameter Name="Trend" QueryStringField="Trend" Type="String" DefaultValue="Stripe" />
            </SelectParameters>
        </asp:AccessDataSource>

Answer 1

User-1199946673 posted

The wildcard charcter in Jet is %. You need to use it in conjunction with the Like Operator

....WHERE Trend Like '%' + ? + '%' ORDER ..... 

this will return all records where the trend field contains the search string

....WHERE Trend Like ? + '%' ORDER ..... 

this will return all records where the trend field starts with the search string

Note...

The last record has the word striped. The first query will also return this record if you enter stripe

Answer 2

User-1634071706 posted

Thanks Hans, I was able to get the operator right. Problem Solved and a point for you.

Now the code looks like this

<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="result.mdb" SelectCommand="SELECT [Image] AS Image FROM [File1] WHERE ([Trend] LIKE '%' + ? + '%') ORDER BY [ID]">
            <SelectParameters>
                <asp:querystringparameter Name="Trend" QueryStringField="Trend" Type="String" DefaultValue="Stripe" />
            </SelectParameters>
        </asp:AccessDataSource>

P.S. I will Close this Post.

Answer 3

User-1462245084 posted

I have a question I have a very similar problem, I have a ControlID="OwnerNameSB" Name="ownername" and a

<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="database/rc-directory.mdb"SelectCommand="SELECT
[ID], [sub division], [ownername], [address], [homephone], [mobilephone], [email], [email2], [comments] FROM [contacts_tbl]  WHERE (([ownername] LIKE '%' + ? + '%') AND ([address] LIKE '%' + ? + '%'))"

my question is I want to be able to search multiple names. Like all words not exact words.   Currenly the only way I can do that is by putting a % (wildcard) in the search box.  ie search box: Jim%Brown and the result would be Jim K Brown.  as I can search for Jim or Brown also works but Jim Brown does not find anything.  Anyway to search all words in search box and not exact match?  any help is greatly appreciated.