By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 1,009 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

Trying to filter an Access database with a Field LIKE "%" + "@PARAM" + "%"

P: n/a
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

May 16 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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" <pa***********@googlemail.com> wrote in message
news:11**********************@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

May 17 '06 #2

P: n/a
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

May 17 '06 #3

P: n/a
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" <pa***********@googlemail.com> wrote in message
news:11**********************@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

May 17 '06 #4

P: n/a
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

May 18 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.