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

Discussion in 'ASP .Net' started by mosscliffe, May 16, 2006.

  1. mosscliffe

    mosscliffe Guest

    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, May 16, 2006
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    >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], May 17, 2006
    #2
    1. Advertising

  3. mosscliffe

    mosscliffe Guest

    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
    mosscliffe, May 17, 2006
    #3
  4. 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" <> wrote in message
    news:...
    > 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], May 17, 2006
    #4
  5. mosscliffe

    mosscliffe Guest

    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
    mosscliffe, May 18, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Geathaa
    Replies:
    2
    Views:
    681
    Geathaa
    Jul 30, 2003
  2. ectoplasm
    Replies:
    12
    Views:
    624
    Zorro
    Jul 28, 2005
  3. Mark B
    Replies:
    0
    Views:
    365
    Mark B
    Jan 12, 2009
  4. Dave
    Replies:
    5
    Views:
    619
    John Bokma
    Apr 26, 2011
  5. Sound
    Replies:
    2
    Views:
    428
    Randy Webb
    Sep 28, 2006
Loading...

Share This Page