473,322 Members | 1,644 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,322 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 1933
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: 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: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
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: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.