Generic search box - InfoPath Dev
in

InfoPath Dev

Having trouble finding a blog or post that answers your question? Check out our Custom Search Page

Generic search box

Last post 02-02-2012 11:20 AM by Ilyssa. 2 replies.
Page 1 of 1 (3 items)
Sort Posts: Previous Next
  • 10-24-2008 03:55 AM

    Generic search box

    HI All I am trying to migrate an internal staff lookup form from ASPX to SharePoint 2007. I have created the form and I can run a query to bring back all records, however I cant seem to implement a text box where I can search across all fields, I can only bind a text box to a certain field.   What i would like is one text box where I can search the entire query for the information (similar in vein to the following ASPX):

    SELECT [EENo], [TLA], [ACTIVE], [FORENAME], [SURNAME], [SITE], [DEPT], [TELEPHONE], [COMPANYMOBILE], [CARREG], [CARMODEL], [CARCOLOUR], [CARREG2], [CARMODEL2], [CARCOLOUR2], [KEY1], [KEY2], [KEY3], [KEY4], [DESKNUMBER] FROM [qryCarRegistrationDetails] WHERE (([EENo] LIKE '%' + @Search + '%') OR ([TLA] LIKE '%' + @Search + '%') OR ([FORENAME] LIKE '%' + @Search + '%') OR ([SURNAME] LIKE '%' + @Search + '%') OR ([SITE] LIKE '%' + @Search + '%') OR ([DEPT] LIKE '%' + @Search + '%') OR ([TELEPHONE] LIKE '%' + @Search + '%') OR ([COMPANYMOBILE] LIKE '%' + @Search + '%') OR ([CARREG] LIKE '%' + @Search + '%') OR ([CARMODEL] LIKE '%' + @Search + '%') OR ([CARCOLOUR] LIKE '%' + @Search + '%') OR ([CARREG2] LIKE '%' + @Search + '%') OR ([CARMODEL2] LIKE '%' + @Search + '%') OR ([CARCOLOUR2] LIKE '%' + @Search + '%') OR ([KEYNTC] LIKE '%' + @Search + '%') OR ([KEYNBO] LIKE '%' + @Search + '%') OR ([KEYNBO2] LIKE '%' + @Search + '%') OR ([KEYBC] LIKE '%' + @Search + '%') OR ([DESKNUMBER] LIKE '%' + @Search + '%')) AND ([ACTIVE] = '1') ORDER BY [SURNAME]

     Regards

    Mark

  • 10-24-2008 06:55 AM In reply to

     If this is a query to an SQL database, you can dynamically generate the query with a little bit of code.

    For example, I have a form with a data connection to an SQL Database table "TestTable1".  The data connection itself also happens to be called TestTable1.  I have a text box on my form bound to "/my:myFields/my:field1" in the main data source.  I have the data connection's data fields shown in a repeating table on the form.  I add the following code to the event handler for a button on the form (C# IP2007 code):

     // a format string for the query. The {0}s will be replaced by field1's value
    const string queryString = "select * from [TestTable1] where [OrderNum] = '{0}' OR [ID] = '{0}'";

    // retrieve field1's value and store it in a string
    string field1 = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:field1", NamespaceManager).Value;

    // make sure that the search string isn't blank
    if (!string.IsNullOrEmpty(field1))
    {
         // instantiate an object to access the data connection. TestTable1 is the name of the data connection
         AdoQueryConnection dc = (AdoQueryConnection)DataConnections["TestTable1"];

         // insert field1's value into the query and assign it to the data connection's query
         dc.Command = string.Format(queryString, field1);

         // run the query
         dc.Execute();
    }
    else
    {
         // show an error when field1 is blank
         MessageBox.Show("The search field cannot be blank!");
    }

     When I type a value into the text box and click the button, the repeating table shows me all rows where OrderNum or ID is equal to the value in the textbox.

    The following thread may also interest you:
    http://www.infopathdev.com/forums/p/8940/31780.aspx#31780


    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 02-02-2012 11:20 AM In reply to

    • Ilyssa
    • Not Ranked
    • Joined on 01-24-2012
    • Posts 3

     Hi Jimmy:

     

    I put this code into the function for the event handler search button, and I see that the search is returning the correct query after dc.Execute();

    but what do I need to do to make the repeating table show the results from the query?

    Thanks very much,

     

Page 1 of 1 (3 items)
Copyright © 2003-2017 Qdabra Software. All rights reserved.
View our Terms of Use.