Get specific row col values from mysql using vb 2012

Question

I have one button retrieve I just want to get the database table "account" value by its row and col and display it in a textbox. I keep on getting errors on the datafill line

 Imports MySql.Data.MySqlClient
Public Class Form1
Dim dataset As DataSet
Dim datatable As DataTable
Dim sqlcon As MySqlConnection
Dim dataadapter As MySqlDataAdapter
Dim sqlcommand As MySqlCommand
Dim sql As String

Private Sub retrieve_Click(sender As Object, e As EventArgs) Handles retrieve.Click

    sqlcon = New MySqlConnection("Data Source=localhost;Database=database;User ID=root;Password=;")

        sqlcon.Open()

    sql = "select * from account"
    dataadapter = New MySqlDataAdapter(sql, sqlcon)
    dataadapter.Fill(dataset)
    TextBox2.Text = dataset.Tables(0).Rows(0).Item(0).ToString()
End Sub
End Class

Answer 1

You need to instantiate the dataset that you pass to the Fill method.

....
dataset = new DataSet()
dataadapter.Fill(dataset)
...

Do not forget to close the connection when finished. It is a resource very costly to keep open when you not need it

Using sqlcon = New MySqlConnection("Data Source=localhost;Database=database;User ID=root;Password=;")

    sqlcon.Open()
    sql = "select * from account"
    dataadapter = New MySqlDataAdapter(sql, sqlcon)
    dataset = new DataSet()
    dataadapter.Fill(dataset)
    TextBox2.Text = dataset.Tables(0).Rows(0).Item(0).ToString()
End Using

See the Using Statement

However if you need only one row it is better to refine the query applying a WHERE clause to limit the results returned by the database.

    sql = "select * from account WHERE AccountName = @name"
    dataadapter = New MySqlDataAdapter(sql, sqlcon)
    dataadapter.SelectCommand.Parameters.AddWWithValue("@name", inputNameBox.Text)
    dataset = new DataSet()
    dataadapter.Fill(dataset, "Account")
    if dataset.Tables("Account").Rows.Count > 0 then 
        TextBox2.Text = dataset.Tables("Account").Rows(0).Item(0).ToString()
    End If

this hopefully will return just the row needed