How to use data reader in vb.net

Question

Please help, how do I really use data reader in vb.net. I'm using odbc to connect mysql and vb.net.
Function I declared on a module:

Public Function form2search(ByVal drugname As String) As OdbcDataReader

        cmd.CommandText = "SELECT *  FROM drug WHERE Drug_name LIKE'%" & drugname & "' "
        Return cmd.ExecuteReader

    End Function

text_changed event:

con.drugname=textBoxdrugname.text
     Dim rdr As Odbc.OdbcDataReader
            rdr = con.form2search(drugname)
        if rdr.hasrows=true then
        rdr.read()

        TextBoxdrugname.Text = rdr("Drug_name").ToString
                        TextBoxdrugcode.Text = rdr("Drug_code").ToString
                        drugtype.Text = rdr("Drug_type").ToString

        end if

I see a result, but it only loads the first item on the database. I've put this code in the text_changed event. What's the proper way of doing this? And what's wrong with the 2nd code, why is it only loading the first data

As you can see the con is the module where I declared the function. Then I created an object of it in the form.

Answer 1

The DataReader is a low level implementation that does not support navigation and only reads a single row every time you call

reader.Read()

For a Windows Forms app you probably should use a DataSet / DataTable approach or a ORM. And you should consider using the mysql connector net over the odbc driver. It is available at mysql.com.

Here is a little demo code:

dim table as new DataTable("table1")

' Create a Connection
using conn as new MysqlConnection("...connectionstring")
    conn.Open() ' Open it

    ' Create a new Command Object
    using cmd as new MysqlCommand("SELECT * FROM table", conn)

        ' Create a DataAdapter
        ' A DataAdapter can fill a DataSet or DataTable
        ' and if you use it with a CommandBuilder it also
        ' can persist the changes back to the DB with da.Update(...)
        using da as new MysqlDataAdapter(cmd)
            da.Fill(table) ' Fill the table
        end using

    end using
end using

' A Binding Source allows record navigation
dim bs as new BindingSource(table, nothing)

' You can bind virtually every property (most common are "text" "checked" or "visible"
' of a windows.forms control to a DataSource
' like a DataTable or even plain objects
textBox1.DataBindings.Add("Text", bs, "columnName")

' Now you can navigate your data
bs.MoveNext()

' Even a ComboBox can be bound to a List and display the related value
' of your current row
comboBox1.DataSource = table2
comboBox1.DisplayMember = "name"
comboBox1.ValueMember = "id"

comboBox1.DataBindings.Add("SelectedValue", bs, "id")

Answer 2

You have to just put the object of data reader in while loop.Here is the sample code:

      dr = myCommand.ExecuteReader()
      While dr.Read()
      'reading from the datareader
       MessageBox.Show("colname1" & dr(0).ToString())
       MessageBox.Show("colname2" & dr(1).ToString())
       MessageBox.Show("colname3" & dr(2).ToString())
       MessageBox.Show("colname4" & dr(3).ToString())
       MessageBox.Show("colname5" & dr(4).ToString())
      'displaying the data from the table
       End While
       dr.Close()