Solved

Urgent : problem looping through dataset and merging data in the 2 x loops.

Posted on 2007-03-25
4
193 Views
Last Modified: 2010-04-16
Hi there..

I have some trouble with the "loop logic" in this specific case, and would really appreciate some code-help on this...

The problem here.. is that i need to get only _ONE_ from each productgroup_name and get _ALL_ the linked metadata for each productgroup_name

Here goes :

The challenge now.. is that i'm running this sql-query :
----------------------------------------------
sql.Append("SELECT ");
        sql.Append("    tbl_productgroup.productgroup_name, tbl_metakey.metakey_key ");
        sql.Append("FROM ");
        sql.Append("    tbl_productgroup, tbl_metakey ");
        sql.Append("WHERE ");
        sql.Append("    tbl_productgroup.catalogtype_pk = tbl_metakey.catalogtype_pk ");
        sql.Append("AND ");
        sql.Append("    relorg_pk ");
        sql.Append("    = @CompanyRelorgPk ");

The database data will look like this :

<productgroup_name>    <metakey_key>
Server                     CPU
Server                     Memory
Server                     Harddisk
Server                     Network
Printer                      Paper
Printer                      Size
Printer                      Ports
Monitor                    Size
Monitor                    DPI

The result i want it this :

string1 = "Server"
string2 = "CPU, Memory, Harddisk, Network"

string1 = "Printer"
string2 = "Paper, Size, Ports"

string1 : "Monitor"
string2 : "Size, DPI"

This is because i'm calling a function like this :
myFunction(string1, string2)

Here is an example on how i try to do this.. :

protected void chooseRelorg_SelectedIndexChanged(object sender, EventArgs e)
    {
        //hent evt. produktgrupper og metadata registrert på valgt kunde (basert på relorg_pk)
       
        //clear dataset.
        iceAH.pgd.productGroups.Clear();
       
        SqlCommand cmd = new SqlCommand();
        StringBuilder sql = new StringBuilder();

        sql.Append("SELECT ");
        sql.Append("    tbl_productgroup.productgroup_name, tbl_metakey.metakey_key ");
        sql.Append("FROM ");
        sql.Append("    tbl_productgroup, tbl_metakey ");
        sql.Append("WHERE ");
        sql.Append("    tbl_productgroup.catalogtype_pk = tbl_metakey.catalogtype_pk ");
        sql.Append("AND ");
        sql.Append("    relorg_pk ");
        sql.Append("    = @CompanyRelorgPk ");

        cmd.Parameters.Add("@CompanyRelorgPk", SqlDbType.Int).Value = chooseRelorg.SelectedValue;
       
        cmd.CommandText = sql.ToString();

        DataHandler dh = new DataHandler();
        cmd.Connection = dh.IceConnection;

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
       
        if (cmd.Connection.State == ConnectionState.Closed)
        {
            cmd.Connection.Open();
        }

        da.Fill(ds);

        DataTable dt = ds.Tables[0];

        gridViewProductGroups.DataSource = ds;
        gridViewProductGroups.DataBind();

        //populate the checkboxlist with a costumers existing productgroups and metadata (from dataset).

        ArrayList col1 = new ArrayList();
        ArrayList col2 = new ArrayList();
       
        foreach (DataRow row1 in dt.Rows)
        {
            col1.Add(row1["productgroup_name"]);

            string metakey_key = "";

            foreach (DataRow row2 in dt.Rows)
            {
                if (row1["productgroup_name"] == row2["productgroup_name"])
                {
                    metakey_key += row1["metakey_key"].ToString() + ",";
                }
            }

            iceAH.addProductGroupAndMetaData(row1["productgroup_name"].ToString(), metakey_key);
        }

        cmd.Connection.Close();
        sourceChkProd.DataBind();
    }
0
Comment
Question by:Kripos56
  • 3
4 Comments
 
LVL 9

Accepted Solution

by:
DrAske earned 500 total points
Comment Utility
You would replace foreach loops with this:

String productGroup = dt.Rows[0]["productgroup_name"].ToString();
String metakey_key = dt.Rows[0]["metakey_key"].ToString();
for(int i = 1; i<dt.Rows.Count; ++i){
    if(dt.Rows[i]["productgroup_name"].ToString() == productGroup){
            metakey_key +=","+dt.Rows[i]["metakey_key"].ToString();
    }else{
         iceAH.addProductGroupAndMetaData(productgroup, metakey_key);
         productgroup = dt.Rows[i]["productgroup_name"].ToString();
         metakey_key = dt.Rows[i["metakey_key"].ToString();
    }
}
if(dt.Rows.Count > 1){
    iceAH.addProductGroupAndMetaData(productgroup, metakey_key);
}

hope that helps,
regards,
0
 
LVL 9

Expert Comment

by:DrAske
Comment Utility
add *col1.Add(productgroup);* after the first line of my code ..
and after the second line in *else* block, I didn't add it to my code cause it seems there is no use for it.

regards,
0
 

Author Comment

by:Kripos56
Comment Utility
Thank you SOO much for.. i'd have to say.. the best answer i've _ever_ had on Experts Exchange !..

It worked like a dream !

Terje.
0
 
LVL 9

Expert Comment

by:DrAske
Comment Utility
Most Welcome :o)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Article by: Ivo
Anonymous Types in C# by Ivo Stoykov Anonymous Types are useful when  we do not need to follow usual work-flow -- creating object of some type, assign some read-only values and then doing something with them. Instead we can encapsulate this read…
We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now