Solved

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

Posted on 2007-03-25
4
194 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
ID: 18791101
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
ID: 18791118
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
ID: 18792044
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
ID: 18792095
Most Welcome :o)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

920 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

12 Experts available now in Live!

Get 1:1 Help Now