Phillip Tan

Phillip Tan

  • NA
  • 8
  • 0

Need help with combining 2 datasets of information

Mar 24 2008 12:29 AM
Hi there, I have a question that I need to ask. I'm trying to do a search through 2 different tables in my database:

1. Content table (contains all the dynamic information that can be managed from an admin console).

2. Product table (contains information of all products stored in the database).

I need to do a keyword search into each table and at the end of it, display it in a gridview. The only challenge is that I do not know how to combine 2 datasets into 1. I have a sample code that I copied and pasted below:

private void RetrieveSearchResults ( string [ ] keywords )
    {
        _sql = "Select p.ProductID As 'ID', p.ProductName As 'LinkContent', 'product_details.aspx?id=' As 'contentUrl', ";
        _sql += "p.FeaturesContent As 'Content' From tblproduct p, tblprofile pr Where p.ProfileID = pr.ProfileID ";

        int i = 0;

        foreach ( string keyword in keywords )
        {
            if ( i == 0 )
            {
                _sql += " And p.FeaturesContent Like '%" + keyword + "%' Or p.ProductName Like '%" + keyword + "%'";
            }
            else
            {
                _sql += " Or p.FeaturesContent Like '%" + keyword + "%' Or p.ProductName Like '%" + keyword + "%'";
            }

            i++;
        }

        _sql += " Group By p.ProductID";

        _connection = new MySqlConnection ( _connectionString );

        _adapter = new MySqlDataAdapter ( _sql, _connection );

        dsResults = new DataSet ( );

        _adapter.Fill ( dsResults );

        // Retrieve all content results
        _sql = "Select contentCode As 'ID', contentTitle As 'LinkContent', 'whatsnew_details.aspx?code=' As 'contentUrl', contentBody As 'Content' From tblcontent Where 1 = 1 ";

        i = 0;

        foreach ( string keyword in keywords )
        {
            if ( i == 0 )
            {
                _sql += " And (contentTitle Like '%" + keyword + "%' Or contentBody Like '%" + keyword + "%')";
            }
            else
            {
                _sql += " Or (contentBody Like '%" + keyword + "%' Or contentBody Like '%" + keyword + "%')";
            }

            i++;
        }

        _adapter = new MySqlDataAdapter ( _sql, _connection );

        _adapter.Fill ( dsResults );

        gvSearchResults.DataSource = dsResults;
        gvSearchResults.DataBind ( );
    }