• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

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

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
Kripos56
Asked:
Kripos56
  • 3
1 Solution
 
DrAskeCommented:
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
 
DrAskeCommented:
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
 
Kripos56Author Commented:
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
 
DrAskeCommented:
Most Welcome :o)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now