Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Trying to filter an Access database with a Field LIKE "%" + "@PARAM" + "%"

Reply
Thread Tools

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

 
 
mosscliffe
Guest
Posts: n/a
 
      05-16-2006
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

 
Reply With Quote
 
 
 
 
Ken Cox [Microsoft MVP]
Guest
Posts: n/a
 
      05-17-2006
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>
<asparameter defaultvalue="open" name="Caption"
type="String" />
</selectparameters>
</asp:accessdatasource>

"mosscliffe" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) 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
>



 
Reply With Quote
 
 
 
 
mosscliffe
Guest
Posts: n/a
 
      05-17-2006
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

 
Reply With Quote
 
Ken Cox [Microsoft MVP]
Guest
Posts: n/a
 
      05-17-2006
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) 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
>



 
Reply With Quote
 
mosscliffe
Guest
Posts: n/a
 
      05-18-2006
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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Database Database Database scott93727@gmail.com Computer Information 0 09-27-2012 02:43 AM
DataBase DataBase DataBase DataBase scott93727@gmail.com Computer Information 0 09-26-2012 09:40 AM
javascript validation for a not required field, field is onlyrequired if another field has a value jr Javascript 3 07-08-2010 10:33 AM
Gridview doesn't edit field after filter LIKE find Mark B ASP .Net 0 01-12-2009 11:23 AM
1.Enter space bar for field names and save the field.The field shoud not get saved and an alert should be there as"Space bars are not allowed" Sound Javascript 2 09-28-2006 02:43 PM



Advertisments