SQL - displaying records

[SubProf]

Hello everyone,

I am querying an MS Access database to find duplicate records and list the results in descending order. I am using a 'while' loop to read through the records but I'm finding it difficult to store the results in separate variables, i.e. the query finds three results where the first duplicate record is found 5 times, the second is found 3 times and the third is found once. I would like to display these 3 records on a form in descending order however I connot display them separately - they display as one string. Can anyone help me to display them separately? Here is my code:

OleDbCommand QueryString1 = new OleDbCommand("SELECT User, text2, Count(User) AS CountOfUser FROM Table1 GROUP BY User, text2 HAVING (((User)='Ann') AND ((text2) Like '%Manager%')) ORDER BY Count(User) DESC", QueryCon1);


OleDbDataReader readerQ1;
try
{
    QueryCon1.Open();
    readerQ1 = QueryString1.ExecuteReader();

    while (readerQ1.Read())
    {

txtResult.Text += (readerQ1["text2"].ToString()) + " ";

}

Thanks in advance,

SubProf

[Ramy Mahrous]

Tell me what the result of QueryString1... write it here please

[LizR]

In part you dont get 3 lines because your txtResult.Txt is never told to separate the lines!

[SubProf]

LizR,

The result is displayed in a textbox and i know that i didn't write them on separate lines because that is not what I want. I want to be able to find the first duplicate and store that result in a variable, I then want to find the second duplicate and store that in another variable etc. I want to continue to store each duplicate in a different variable and I will not know how many duplicate records will be found as the database is constantly changing.

RamyMahrous,

the result is 'Area Manager Regional Manager Line Manager'. I have this result displayed in a textbox but I would like to have 'Area Manager' stored in a separate variable, 'Regional Manager' stored in a separate variable and 'Line Manager' stored in a separate variable.

Thanks for any help you can offer

SubProf

[LizR]

OK, but thats nothing like what your code does.
Why not just use a datagrid and attach your sql results to it? Thats the kind of thing its good for.

The resulting data is already there, in a table like manner, but just in a dataset, its a good way to store it depending on what you need it for later

[Ramy Mahrous]

Split it by Manager :D really I am not kidding YourString.Split(new string[] { "Manager" }, StringSplitOptions.RemoveEmptyEntries);

[BlackSun]

i think that too RamyMahrous
use "Manager" to split ur Qresult

[cVz]

ok i guess you will have to split them first , then remove where

text.Contains("The duplicate")

, then add them all together ...

Should be fine ??

[LizR]

Would not something like

List<String> dups = new List<String>();
while (readerQ1.Read())
{
dups.Add(readerQ1["text2"].ToString());
}

Give you all the answers the way you want?

[SubProf]

Thanks for the help - I will try out your ideas

SubProf