Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   ASP .Net (http://www.velocityreviews.com/forums/f29-asp-net.html)
-   -   Trying to filter an Access database with a Field LIKE "%" + "@PARAM" + "%" (http://www.velocityreviews.com/forums/t293482-trying-to-filter-an-access-database-with-a-field-like-param.html)

mosscliffe 05-16-2006 03:58 PM

Trying to filter an Access database with a Field LIKE "%" + "@PARAM" + "%"
 
I have been messing with the above all afternoon.

I must just be thick

I am using an AccessDataSource

I have tried modifying the select but I can not find the right syntax
to concatenate the LIKE part of the test by surrounding my textbox
field value with a leading and closing %.

I have tried the filter property but that is a bit of a mystery, I
can't work out how to create Filter Expression.

What is the quickest and best method. People must do this hundreds of
times a day.

DataSource SELECT Select * from glossary where name like '%' +
'@PARAM' + '%' >>>FAILS (SQL ERROR)

DataSource SELECT Select * from glossary where name like '%' + 'word'
+ '%' >>>>WORKS

Please help and thanks in advance.

Tim


Ken Cox [Microsoft MVP] 05-17-2006 01:12 AM

Re: Trying to filter an Access database with a Field LIKE "%" + "@PARAM" + "%"
 
Hi Tim,

You not showing how you're adding the parameter:

<asp:accessdatasource id="AccessDataSource1" runat="server"
datafile="~/App_Data/blrp.mdb" selectcommand="SELECT * FROM [Documents]
WHERE ([Caption] LIKE '%' + ? + '%')">
<selectparameters>
<asp:parameter defaultvalue="open" name="Caption"
type="String" />
</selectparameters>
</asp:accessdatasource>

"mosscliffe" <page77.office@googlemail.com> wrote in message
news:1147795129.542753.311950@g10g2000cwb.googlegr oups.com...
>I have been messing with the above all afternoon.
>
> I must just be thick
>
> I am using an AccessDataSource
>
> I have tried modifying the select but I can not find the right syntax
> to concatenate the LIKE part of the test by surrounding my textbox
> field value with a leading and closing %.
>
> I have tried the filter property but that is a bit of a mystery, I
> can't work out how to create Filter Expression.
>
> What is the quickest and best method. People must do this hundreds of
> times a day.
>
> DataSource SELECT Select * from glossary where name like '%' +
> '@PARAM' + '%' >>>FAILS (SQL ERROR)
>
> DataSource SELECT Select * from glossary where name like '%' + 'word'
> + '%' >>>>WORKS
>
> Please help and thanks in advance.
>
> Tim
>




mosscliffe 05-17-2006 08:28 AM

Re: Trying to filter an Access database with a Field LIKE "%" + "@PARAM" + "%"
 
Thank you for replying.

Is Caption a fieldname in the table as it appears to be saying
parameternamecontents = parameter contents or am I just being thick.

The implication is the parameter name has to be the same as the field
name ?

I accept this works, but overimpressed with the syntax.

Next problem: How do I link this parameter [caption] to a textbox on my
form ?

Thanks again

Tim


Ken Cox [Microsoft MVP] 05-17-2006 07:21 PM

Re: Trying to filter an Access database with a Field LIKE "%" + "@PARAM" + "%"
 
Hi Tim,

Use the controlid and propertyname attribiutes and set them to the control
name and property respectively:

<selectparameters>
<asp:controlparameter controlid="TextBox1" name="Caption"
propertyname="Text" type="String" />
</selectparameters>

Ken


<%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
System.EventArgs)

End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:textbox id="TextBox1" runat="server"></asp:textbox><br />
<asp:accessdatasource id="AccessDataSource1" runat="server"
datafile="~/App_Data/blrp.mdb" selectcommand="SELECT * FROM [Documents]
WHERE ([Caption] LIKE '%' + ? + '%')">
<selectparameters>
<asp:controlparameter controlid="TextBox1" name="Caption"
propertyname="Text" type="String" />
</selectparameters>
</asp:accessdatasource>
<br />
<asp:button id="Button1" runat="server" onclick="Button1_Click"
text="Button" /><br />
&nbsp;<asp:gridview id="GridView1" runat="server"
autogeneratecolumns="False" datakeynames="Id"
datasourceid="AccessDataSource1">
<columns>
<asp:boundfield datafield="FileName" headertext="FileName"
sortexpression="FileName" />
<asp:boundfield datafield="Caption" headertext="Caption"
sortexpression="Caption" />
<asp:boundfield datafield="Description"
headertext="Description" sortexpression="Description" />
<asp:boundfield datafield="Id" headertext="Id"
insertvisible="False" readonly="True" sortexpression="Id" />
<asp:boundfield datafield="DateAdded" headertext="DateAdded"
sortexpression="DateAdded" />
</columns>
</asp:gridview>

</div>
</form>
</body>
</html>

"mosscliffe" <page77.office@googlemail.com> wrote in message
news:1147854509.185809.103950@j73g2000cwa.googlegr oups.com...
> Thank you for replying.
>
> Is Caption a fieldname in the table as it appears to be saying
> parameternamecontents = parameter contents or am I just being thick.
>
> The implication is the parameter name has to be the same as the field
> name ?
>
> I accept this works, but overimpressed with the syntax.
>
> Next problem: How do I link this parameter [caption] to a textbox on my
> form ?
>
> Thanks again
>
> Tim
>




mosscliffe 05-18-2006 09:13 PM

Re: Trying to filter an Access database with a Field LIKE "%" + "@PARAM" + "%"
 
Thank you very much - most helpful.

I am getting there but rather slower, than I had hoped.

Now I only have to worry about when controls get bound. I am about to
attempt some sort of state engine testing to see what order events get
executed in. I am surprised to discover PageLoad is executed before a
button press. I think I need to understand where PageRender fits in
with the scheme of things.

Thanks again

Tim



All times are GMT. The time now is 10:42 PM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.