Fill a ComboBox From MySQL Database & Dynamic Query (Visual Basic .NET)

[wirejp]

Hello,


I am using a MySQL database which is linked to a Visual Studio Community 2017 software. I am using a WinForm and the code is written in VB.net. I have a combobox on a Form1.vb and I am trying to fill a combobox from the mySQL database using a dynamic query. I have set up a class form shown below: -

Imports MySql.Data.MySqlClient

Public Class DBControl
Private DBCon As New MySqlConnection("Server=YOURSERVERorIP;Database=YOURDATABASE;Uid=YOURUSERNAME;Pwd=YOURPASSWORD;")

' PREPARE DB COMMAND
Private DBCmd As MySqlCommand

' DB DATA
Public DBDA As MySqlDataAdapter
Public DBDT As DataTable

' QUERY PARAMETERS
Public Params As New List(Of MySqlParameter)

' QUERY STATISTICS
Public RecordCount As Integer
Public Exception As String

' USE DEFAULT CONNECTION STRING
Public Sub New()
End Sub

' OVERRIDE DEFAULT CONNECTION STRING
Public Sub New(ConnectionString As String)
DBCon = New MySqlConnection(ConnectionString)
End Sub

Public Sub ExecQuery(Query As String)
' RESET QUERY STATS
RecordCount = 0
Exception = ""

Try
' OPEN A CONNECTION
DBCon.Open()

' CREATE DB COMMAND
DBCmd = New MySqlCommand(Query, DBCon)

' LOAD PARAMS INTO DB COMMAND
Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))

' CLEAR PARAMS LIST
Params.Clear()

' EXECUTE COMMAND & FILL DATATABLE
DBDT = New DataTable
DBDA = New MySqlDataAdapter(DBCmd)
RecordCount = DBDA.Fill(DBDT)
Catch ex As Exception
Exception = ex.Message
Finally
' CLOSE YOUR CONNECTION
If DBCon.State = ConnectionState.Open Then DBCon.Close()
End Try
End Sub

Public Sub AddParam(Name As String, Value As Object)
Dim NewParam As New MySqlParameter(Name, Value)
Params.Add(NewParam)
End Sub

' ERROR CHECKING
Public Function HasException(Optional Report As Boolean = False) As Boolean
If String.IsNullOrEmpty(Exception) Then Return False
If Report = True Then MsgBox(Exception, MsgBoxStyle.Critical, "Exception:")
Return True
End Function
End Class


In my Form1.vb, I wrote some vb.net code to fill the combobox. If I use the code shown below, I get an error message: "Table is not a member of DataTable" at the line: For Each r As DataRow In DB.DBDT.Table(0).Rows:-

Private Sub GetDATABASECOLUMN()
'QUERY TABLENAME
DB.ExecQuery("SELECT COLUMN NAME FROM MYDATABASE.TABLENAME")

'IF RECORDS ARE FOUND, ADD THEM TO THE COMBOBOX
If DB.RecordCount > 0 Then
For Each r As DataRow In DB.DBDT.Table(0).Rows
cboComboBoxName.Items.Add(r("COLUMN NAME"))
Next

'SET THE COMBOBOX TO THE FIRST RECORD
cboComboBoxName.SelectedIndex = 0
ElseIf DB.Exception <> "" Then
'REPORT ERRORS
MsgBox(DB.Exception)
End If
End Sub

Continue reading...