bemara57
asked on
How do I add wild cards to my Access WHERE condition in ASP.NET?
I'm creating a search that just takes the user's input parameter and adds it to the WHERE condition of the SQL statement. My problem is that I'm using Access and it has a '?' for the parameter string and I'm trying to add wild cards to the parameter. So if a user input 'lion', I want it to perform WHERE name LIKE % lion%. Here's what I got but I'm sure on how to incorporate this syntax:
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="<%$ connectionStrings: catalogMDB %>"
SelectCommand="SELECT [product_sku], [product_name], [product_price], [product_dimension], [product_image] FROM [Products] WHERE [product_name] LIKE ?">
<SelectParameters>
<asp:QueryStringParameter Name="keyword" QueryStringField="keyword"
</SelectParameters>
</asp:AccessDataSource>
I tried to put this but didn't work and gave me server error:
WHERE [product_name] LIKE % ?%">
How do I do this?
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="<%$ connectionStrings: catalogMDB %>"
SelectCommand="SELECT [product_sku], [product_name], [product_price], [product_dimension], [product_image] FROM [Products] WHERE [product_name] LIKE ?">
<SelectParameters>
<asp:QueryStringParameter Name="keyword" QueryStringField="keyword"
</SelectParameters>
</asp:AccessDataSource>
I tried to put this but didn't work and gave me server error:
WHERE [product_name] LIKE % ?%">
How do I do this?
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
If the data file is Access, you would use * as a wild card (SQL Server and others would use %)
SelectCommand="SELECT [product_sku], [product_name], [product_price], [product_dimension], [product_image] FROM [Products] WHERE [product_name] LIKE *" & strUserInput & "*">
SelectCommand="SELECT [product_sku], [product_name], [product_price], [product_dimension], [product_image] FROM [Products] WHERE [product_name] LIKE *" & strUserInput & "*">
Or possibly this (+'s instead of &'s, depending on the front-end environment):
SelectCommand="SELECT [product_sku], [product_name], [product_price], [product_dimension], [product_image] FROM [Products] WHERE [product_name] LIKE *" + strUserInput + "*">
SelectCommand="SELECT [product_sku], [product_name], [product_price], [product_dimension], [product_image] FROM [Products] WHERE [product_name] LIKE *" + strUserInput + "*">
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thanks all but actually MS Access used % to work. This is what I used and it worked:
WHERE [product_name] LIKE '% ' + ? + '%'
This works too:
WHERE [product_name] LIKE '% ' + ? + '%'
I'm using ASP.NET 2.0. Perhaps this has something to do with it. Anyways, thanks for the direction.
WHERE [product_name] LIKE '% ' + ? + '%'
This works too:
WHERE [product_name] LIKE '% ' + ? + '%'
I'm using ASP.NET 2.0. Perhaps this has something to do with it. Anyways, thanks for the direction.
ASKER
I meant this works too:
WHERE [product_name] LIKE '% ' & ? & '%'
WHERE [product_name] LIKE '% ' & ? & '%'
>Thanks all but actually MS Access used % to work
LOL! That makes sense. You're opening this as an ADO recordset. The * wildcard would be used within a stored query in the Access environment, or if a DAO recordset were being used (which is what most of us Access folks are used to).
LOL! That makes sense. You're opening this as an ADO recordset. The * wildcard would be used within a stored query in the Access environment, or if a DAO recordset were being used (which is what most of us Access folks are used to).
mx