473,287 Members | 1,868 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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

May 16 '06 #1
4 1931
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: BCS | last post by:
I have a web site in which the site administrator can input information to a database through a web form. The information then gets displayed on ASP pages. One field is a large text field. Of...
0
by: Pato Secruza | last post by:
Hi everyone! I have a really frustrating error and need help. I’m trying to do a function that gets the properties and names of the fields in a MS Access database using ASP. I haven’t...
15
by: philip | last post by:
On a form, I have a datagridview. This datagridview is constructed on a dataset filled by a tableadapter. The table adapter do very well what it must do when filling dataset. Insertions,...
3
by: zlf | last post by:
Hi, I am trying to access private field by code below. But it runs with error. Please tell me what's the problem or any method that may allow me to access private field. Thank you class Love...
1
by: EricRogers | last post by:
Thanks for any help in advance! Here 5 different cmdStrings that I have tried but they all have a syntax error in it that I cannot spot: (cmdString is dimmed as a String) cmdString = "ALTER TABLE...
10
by: C# Beginner | last post by:
Hi there, I'm currently trying to create a MS access file at runtime, but I stumble into some problems. 1. When I use Datatype adUnsignedInt I get an error (invalid type). 2. Which datatype...
1
by: roger2282 | last post by:
help, I am trying to update a field on my database and cant seem to get the right value stored eg on reading the database field i get the values I need such as 31.24, 3.15 and 16.00 and I...
3
by: Mcoleman | last post by:
Hi guys, First post here, so please be gentle. I'm currently writing an Access 2000 Database, and as part of that I need some reports to make use of the information contained in the db. The...
10
by: dlrider | last post by:
Hello, I don't know what this would be called, and searches for the subject above are not useful. FAQ did not seem to provide anything either. My inherited application already uses Javascript for...
0
by: =?ISO-8859-1?Q?Josep_M=AA_Puigdollers?= | last post by:
I'm trying to save a Date into an Access database. I'm using a dataset with only one table, and that table has only two fields: a string and a date field. My code is similar to this: strValue...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.