none
Search multiple table and display results in relevant form RRS feed

  • Question

  • Hi,

    We have a database with multiple tables. Eg: Process updates and Knowledge bases. We are able to perform individual search but now needs to have a global search option which shows results from both the tables based on input on a Grid View and when user select the desired value the results should open on its related form. Both the tables have a field name "Type" to distinguish if its a Process (P) or KB (K).

    Eg: If user types news it pulls details from both process and Knowledge base and when he taps on the result if should open with the relavant details on the related form.

    Any suggestions on how this can be accomplished  ?

    Below is the DB structure and forms we have

    Process_Updates

    KB_Articles

    And below form displays results from Process updates similary we have another form for KB articles.


    Coderv9

    Saturday, August 18, 2018 10:59 AM

Answers

  • One thought is to alias table and fields (un-tested as I'm not setup to try it right now). From there select the columns in the resulting DataTable you want to place into a form.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Saturday, August 18, 2018 4:51 PM
    Moderator

All replies

  • You should try a UNION e.g.

    SELECT *
    FROM [Process_Updates]
    WHERE [Process_Updates].[Type] LIKE "Andrew*"
    
    UNION 
    
    SELECT * 
    FROM KB_Articles
    WHERE KB_Articles.[Type] LIKE "Andrew*"
    
    Where you replace SELECT * with SELECT [fields to return], replace WHERE [Type] LIKE "Andrew*" with a parameter e.g. cmd.Parameters.AddWithValue etc.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, August 18, 2018 12:05 PM
    Moderator
  • One thought is to alias table and fields (un-tested as I'm not setup to try it right now). From there select the columns in the resulting DataTable you want to place into a form.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Saturday, August 18, 2018 4:51 PM
    Moderator
  • Thank You Karen. 

    Coderv9

    Wednesday, August 22, 2018 3:01 AM