Search multiple results from database and display in single textbox

Question

guys i want to build an efficient searching tool in vb to search data from my database in mysql where i have stored paragraphs of some information. i want the search to return multiple results like google does but in a textbox in the form of 2-3 paragraphs of the same concept.Also to make the search more efficient i want to include the substring feature that is the % sign in the select query. can anyone tell me how to implement these two features ? here is my basic search code that returns just a single paragraph stored in the table into my result textbox that i hide first and then show when the results appear.

 If TextBox1.Text = "" Then
        MsgBox("Please Enter a Keyword")
    Else

        Dim conn As MySqlConnection
        conn = New MySqlConnection
        conn.ConnectionString = "Server=localhost;UserID=root;Password=admin674;Database=db1"
        Dim myadapter As New MySqlDataAdapter
        conn.Open()
        Dim sqlquery = "select text from text where name like '" & TextBox1.Text & "'"
        Dim mycommand As New MySqlCommand
        mycommand.Connection = conn
        mycommand.CommandText = sqlquery
        myadapter.SelectCommand = mycommand
        Dim mydata As MySqlDataReader
        mydata = mycommand.ExecuteReader
        If mydata.HasRows = 0 Then
            MsgBox("Data Not Found")
            TextBox1.Clear()
            TextBox2.Clear()

        Else
            mydata.Read()
            TextBox2.Text = mydata.Item("text")
            TextBox2.Show()


        End If

are you asking how to display multiple results in ONE textbox? That's just txtResults.Text &= mydata.Item("text") as you loop thru results (a loop for multiple returns seems not to be implemented). I dont follow what you want regarding substring and %, sorry — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Oct 4, 2013 at 17:16
for the substring what i meant was that i had to type the exact keyword to search from the database so to remove that problem i needed a query to search substring so that the user doesnt have to type the exact keywords that are present in the database — The Rookie
– The Rookie, Commented Oct 5, 2013 at 3:24

Comfortably Numb · Accepted Answer · 2013-10-05 04:30:03Z

1

You already answered one question yourself - how to do a substring search, simple add % to your query:

Dim sqlquery = "select text from text where name like '%" & TextBox1.Text & "%'"

(ideally, instead of supplying search value in-line you would use parametrized query, which, among other things would help avoid SQL Injection.

As for the second part - you are already using DataReader, all you have to do is instead using a single mydata.Read() command - loop thru all its results. Replace

mydata.Read()
TextBox2.Text = mydata.Item("text")
TextBox2.Show()

with

Dim sb as New StringBuilder()

While mydata.Read()
   sb.AppendLine(mydata("text"))
End While

TextBox2.Text = sb.ToString()
TextBox2.Show()

This approach uses StringBuilder class which is an efficient way to concatenate multiple strings.

P.S. Don't forget to close your DataReader and Connection after use.

edited Oct 5, 2013 at 4:30

answered Oct 4, 2013 at 17:58

Comfortably Numb

39.9k17 gold badges82 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

The Rookie Over a year ago

thanks alot man it worked now the only thing is how can i add spaces between those paragraphs that is they are all coming in a line so cant distinguish which one is which ? also can i use the same code to reutrn multiple images in a picture box too ?can you help with that thankx in advance

Comfortably Numb Over a year ago

Yes you can add an extra space, just add an empty sb.AppendLine() command after the real one. And you can use similar code to return multiple images, but since a single picture box can show only one image - you will have to create picture boxes dynamically, add them to the form and populate with images in the loop.

Collectives™ on Stack Overflow

Search multiple results from database and display in single textbox

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related