While loop keeps returning duplicate information

Question

I have a function that is meant to get values from a SQL database table (called ProductProfiles) and display them inside a .csv document that users can download and view. The results are based on a drop down list's selection (called ddlClassification). The function works almost perfectly, but the while loop used to write the sql data to a .csv file keeps repeating data. For example, if 3 entries exist (we will call them A, B and C), the .csv file should only show 3 entries. However, with things as they are now, these 3 entries are repeated multiple times in the .csv file, which is incorrect.

I am not sure what I'm missing from the function to end this repeating issue.

This is the function

private void WriteProductProfile(string ProfileID, ref TextWriter tw)
{
    string CatList = GetCatList();

    string sqlQuery = @"SELECT [ProfileID], [Name], [Description], [SpeciesLink],
          [LineDraw], [LineDrawThumbnail], [ProfileThumbnail], [ComponentThickness],
          [ComponentWidth], [ComponentFactor], [FinishedThickness], [FinishedWidth],
          ISNULL([ClassificationID],-1) as ClassificationID, [StockOrCust],
          [Visibility], [Notes], [OrderBy]
        FROM ProductProfile
        WHERE ClassificationID = @ClassificationID";
    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["AbleCommerce"].ToString()))
    {
        SqlCommand cmd = new SqlCommand(sqlQuery, cn);
        cmd.Parameters.Add(new SqlParameter("@ClassificationID", ddlClassification.SelectedValue));
        cmd.CommandType = CommandType.Text;
        cn.Open();
        using (IDataReader reader = cmd.ExecuteReader())
        {
            while(reader.Read())
            {
                string myLine = reader["ProfileID"] + "\t"
                    + ImportHelper.CleanHTMLText(reader["Name"].ToString()) + "\t"
                    + ImportHelper.CleanHTMLText(reader["Description"].ToString()) + "\t"
                    + reader["SpeciesLink"] + "\t"
                    + reader["LineDraw"] + "\t"
                    + reader["LineDrawThumbnail"] + "\t"
                    + reader["ProfileThumbnail"] + "\t"
                    + reader["ComponentThickness"] + "\t"
                    + reader["ComponentWidth"] + "\t"
                    + reader["ComponentFactor"] + "\t"
                    + reader["FinishedThickness"] + "\t"
                    + reader["FinishedWidth"] + "\t"
                    + reader["ClassificationID"] + "\t"
                    + reader["StockOrCust"] + "\t"
                    + reader["Visibility"] + "\t"
                    + ImportHelper.CleanHTMLText(reader["Notes"].ToString()) + "\t"
                    + reader["OrderBy"] + "\t";
                tw.WriteLine(myLine);
            }
        }
        cn.Close();
    }
}

This is the code calling the WriteProductProfile function

private void ProcessCategory(Category MainCat, ref TextWriter tw, ref string ProdUseList)
{
    string sqlQuery = "SELECT [ProfileID], [ClassificationID] FROM baird_ProductProfile WHERE ClassificationID = @ClassificationID";
    string profId = "";
    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["AbleCommerce"].ToString()))
    {
        SqlCommand cmd = new SqlCommand(sqlQuery, cn);
        cmd.Parameters.Add(new SqlParameter("@ClassificationID", ddlClassification.SelectedValue));
        cmd.CommandType = CommandType.Text;
        cn.Open();
        using (IDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                profId = reader["ProfileID"].ToString();
            }
        }
        cn.Close();
    }

    foreach (CatalogNode subCat in MainCat.CatalogNodes)
    {
        //process the children items
        switch (subCat.CatalogNodeType)
        {
            case CatalogNodeType.Category:
                ProcessCategory((Category)subCat.ChildObject, ref tw, ref ProdUseList);
                break;
            case CatalogNodeType.Product:
                if (AddProduct(profId, ref ProdUseList))
                    WriteProductProfile(profId, ref tw);
                break;
        }
    }
}

"While loop keeps repeating" - well that's the sort of thing we use it for... — The Paramagnetic Croissant, Jun 19 '14 at 20:35
When you set your breakpoint within the while loop, what happens when you expect it to end? — Jack Marchetti, Jun 19 '14 at 20:37
What you get if run the query in SQL Server Management Studio? — , Jun 19 '14 at 20:37
Your loop looks fine. This sounds like more of a query issue than a while loop issue. Are you sure your data doesn't repeat in the query results? If it's the query the quickest fix would be to change your query to `"Select Distinct [ProfileID] ..."` — Daniel, Jun 19 '14 at 20:37
I checked the query in SQL Management Studio and the results don't repeat--I see every entry only once. — Dejsa Cocan, Jun 19 '14 at 20:40
Are duplicate records allowed in your table? Maybe use DISTINCT? — Kevin Cook, Jun 19 '14 at 20:41
There are no duplicate records allowed. And when I test the sql query in Sql Management Studio, the results are 100% normal. No duplicates. — Dejsa Cocan, Jun 19 '14 at 20:42
Is it possible WriteProductProfile is being called multiple times? — Kevin Cook, Jun 19 '14 at 20:44
Add reader.Close(); http://msdn.microsoft.com/en-us/library/system.data.idatareader.aspx — , Jun 19 '14 at 20:46
In a debugger, have you put a breakpoint on myline and see how many times the write is hit? — Kevin Cook, Jun 19 '14 at 20:46
@Love: The `using`-statement will close the reader automatically. — Olivier Jacot-Descombes, Jun 19 '14 at 20:49
I updated my post to include the function that calls the WriteProductProfile function... — Dejsa Cocan, Jun 19 '14 at 20:50
It is called in a `foreach`, so it can be called several times if `MainCat` has several `CatalogNodes`! — Olivier Jacot-Descombes, Jun 19 '14 at 20:53
Okay, sorry for stupid. Can you post the snapshot of the result in SQL Management Studio? — , Jun 19 '14 at 20:53
@OlivierJacot-Descombes Okay, I see that...I will have to try and fix that... — Dejsa Cocan, Jun 19 '14 at 20:55
in MainCat.CatalogNodes if you have several CatalogNodeType.Product, it will duplicate the profId for each one. You might want a break in there (or some sort of bool) to only have the file written once. — Kevin Cook, Jun 19 '14 at 20:56
Assigning a value to `profId` in a `while`-loop ist strage as well, since the variable will keep only the last vallue assigned to it. — Olivier Jacot-Descombes, Jun 19 '14 at 20:57
@OlivierJacot-Descombes Ok, that foreach from giving me the trouble. I took out the line of code that called the WriteProductProfile() function and that fixed the issue. Thanks! :) — Dejsa Cocan, Jun 20 '14 at 13:10
We don't see your creation of the TextWriter tw object ... are you creating it with the option to append by any chance? If so, could that explain the behaviour you are experiencing? — Scott Prokopetz, Jul 18 '14 at 20:41

While loop keeps returning duplicate information

0 Answers0