?
Solved

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

Posted on 2007-03-25
4
Medium Priority
?
203 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 9

Accepted Solution

by:
DrAske earned 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month9 days, 17 hours left to enroll

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