Retriving 2 data into 1 textbox

[cromz]

Hey guys I have a problem. I am using mysql as my database. I want to get the 3 most repeated values into 1 textbox
And my problem is how do i get them?

here is the table:

pen
book
book
book
pen
pencil
pencil
paper

i want to get the value of pen book and pencil into 1 textbox.
starting from book,pen,pencil
how can i achieve this. Any help would be appreciated. Thanks

[BhuvanRam]

Do you a need a SQL query or VB.NET CODE?

[azareth]

retrieve all the items, then store them in an array, insert a condition that if the retrieved item is equal to one of the array values then increment that array element.
after all the items are retrieved just add a condition whether that particular item repeated 3 times.

[Begginnerdev]

Query side, you can try this:
Replace Type with the column that holds your values.

SELECT * From Table ORDER BY Type ASC

For list based, try this:

Dim lstString As New List(of String)

'Add the items to your list

lstString.Sort()

'The default sort function sorts Ascending - for sorting of another type - write your sort function and pass it into the sort's parameters.

Then to load them into the textbox, you can do this:

For i = 0 to lstString.Count - 1
    If i < lstString.Count - 1 Then
        TextBox1.Text &= lstString(i) & ", "
    Else
        TextBox1.Text &= lstString(i)
    End If
Next

[Reverend Jim]

On the following table

Supplies
  ID
  Name

Containing the following rows

1   paper
2   book
3   book
4   book
5   pen
6   pencil
7   pencil
8   pen

The following query

select count(*) as count,Name from supplies group by Name order by count desc

returns

3   book
2   pen
2   pencil
1   paper

Then you can pick the three most frequent items from most to least frequent by stepping through the returned recordset until you have three items or you run out of records.

[cromz]

Sorry for being a total noob. but i still can't get the right thing
Here is my code

If db.State = ConnectionState.Closed Then
            db.Open()
        End If
        Try
            strSql = "select type from tools as bd where location ='" & ComboBox2.Text & "' group by type asc"
            cmd.CommandText = strSql
            cmd.Connection = db
            dadapter.SelectCommand = cmd
            datardr = cmd.ExecuteReader
            If datardr.HasRows Then
                datardr.Read()
                Dim lstString As New List(Of String)
                lstString = datardr("bd")
                lstString.Sort()
                For i = 0 To lstString.Count - 1
                    If i < lstString.Count - 1 Then
                        TextBox3.Text &= lstString(i) & ", "
                    Else
                        TextBox3.Text &= lstString(i)
                    End If
                Next
            End If
        Catch ex As Exception
        End Try

Thanks everyone for ur big help.

[Reverend Jim]

For the following code you need to create ListView1 in details view with two columns. It will add up to three rows to the listview with the most frequent items appearing first.

ListView1.Items.Clear()

Dim con As New SqlConnection("Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")
Dim cmd As New SqlCommand

con.Open()

cmd.Connection = con
cmd.CommandText = "select count(*) as count,Name from supplies group by Name order by count desc"

Dim rdr As SqlDataReader = cmd.ExecuteReader

Do While rdr.Read() And ListView1.Items.Count < 3
    ListView1.Items.Add(New ListViewItem({rdr("count"), rdr("Name")}))
Loop

rdr.Close()
con.Close()

[cromz]

Can i get the value in the textbox rather than in a listview?
So there will be pen,book,pencil in a textbox.

I only need the 3 most repeated values appear in that textbox. I dont need to know how many of them are they in that table.

thanks for your help

[azareth]

won't omitting rdr("count") from ListView1.Items.Add(New ListViewItem({rdr("count"), rdr("Name")}))be enough ?

[Reverend Jim]

In that case

TextBox1.Text = ""

Dim con As New SqlConnection("Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")
Dim cmd As New SqlCommand

con.Open()

cmd.Connection = con
cmd.CommandText = "select count(*) as count,Name from supplies group by Name order by count desc"

Dim rdr As SqlDataReader = cmd.ExecuteReader

Do While rdr.Read() And ListView1.Items.Count < 3
    TextBox1.Text &= rdr("Name") & " "
Loop

rdr.Close()
con.Close()

[azareth]

or you may want to use
TextBox1.Text &= Environment.NewLine & "New line" or TextBox1.AppendText(Environment.NewLine & "New line")
to make it look like a list

[cromz]

Great! It's working
Thanks everyone for ur big help :)

PROBLEM SOLVED