Retrieving values from access database to a combobox in visual basic

Hi folks. Hope everyone is doing good. This is my first time posting in this thread so here I go.

I'm developing a program to run a Registry for a hospice. I connected the database with the data source wizard in Visual Basic. I ran lookup wizard in access database stablishing the values I want to illustrate in the tables. I want to populate them in combobox instead of a textbox, because these are records that will be stored later. However I cannot get my combobox populated and I googled it many times in different ways. I tried many codes and them seemed fime but when it comes to run the program it comes out with and unexpected handle exception and I don't get it work.

If anybody had this issue and cand aid me in this it will be appreciated. Include some of the codes I have used:

Public myConnToAccess As OleDbConnection
Dim mySQLCommand As OleDbCommand
Dim mySQLStrg As String
Dim ds As DataSet
Dim da As OleDbDataAdapter
Dim tables As DataTableCollection
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
myConnToAccess = New OleDbConnection()
myConnToAccess.Open()
ds = New DataSet
tables = ds.Tables
da = New OleDbDataAdapter("SELECT *FROM table", myConnToAccess)
da.Fill(ds, "....")
Dim view1 As New DataView(tables(0))
With GenderComboBox
.DataSource = ds.Tables("....")
.DisplayMember = "...."
.ValueMember = "...."
.SelectedIndex = 0
.AutoCompleteMode = AutoCompleteMode.SuggestAppend
.AutoCompleteSource = AutoCompleteSource.ListItems
End With
End Sub

This

Dim cn As New OleDbConnection()
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cn.Open()
Dim cm As New OleDbCommand("select * From Record", cn)
Dim dr As OleDbDataReader = cm.ExecuteReader
While dr.Read
GenderComboBox.Items.Add(dr(1).ToString)
End While
dr.Close()
cn.Close()
End Sub

This

Dim cn As New OleDbConnection()
Dim da As New OleDbDataAdapter()
Dim dt As New DataTable()
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cn.Open()
da.SelectCommand = New OleDbCommand("select * from Record", cn)
da.Fill(dt)

Dim r As DataRow
For Each r In dt.Rows
GenderComboBox.Items.Add(r("Gender").ToString)
Next
cn.Close()
End Sub
Here the give me error and I don't know what goes there

Thanks for the assistance

 

Alright with this one the program runs but the combobox does not come out with the assigned values from database. Anyone with any clue on this?

Public Class NewPatient
Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Alex\documents\visual studio 2015\Projects\WindowsApplication5\WindowsApplication5\Medical Registry database.mdb")
Dim da As New OleDbDataAdapter()
Dim dt As New DataTable()
Private Sub NewPatient_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'Medical_Registry_databaseDataSet.Record' table. You can move, or remove it, as needed.
Me.RecordTableAdapter.Fill(Me.Medical_Registry_databaseDataSet.Record)
cn.Open()
Dim cm As New OleDbCommand("select * From Record", cn)
Dim dr As OleDbDataReader = cm.ExecuteReader
While dr.Read
GenderComboBox.Items.Add(dr(1).ToString)
End While
dr.Close()
cn.Close()

Also, tried this one but the combobox keeps coming out empty in the debbugger

Public myConnToAccess As OleDbConnection
Dim mySQLCommand As OleDbCommand
Dim mySQLStrg As String
Dim ds As DataSet
Dim da As OleDbDataAdapter
Dim tables As DataTableCollection
Private Sub NewPatient_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'Medical_Registry_databaseDataSet.Record' table. You can move, or remove it, as needed.
Me.RecordTableAdapter.Fill(Me.Medical_Registry_databaseDataSet.Record)
myConnToAccess = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Alex\documents\visual studio 2015\Projects\WindowsApplication5\WindowsApplication5\Medical Registry database.mdb")
myConnToAccess.Open()
ds = New DataSet
tables = ds.Tables
da = New OleDbDataAdapter("SELECT Gender from Record", myConnToAccess)
da.Fill(ds, "Gender")
Dim view1 As New DataView(tables(0))
With GenderComboBox
.DataSource = ds.Tables("Gender")
.DisplayMember = "Gender"
.ValueMember = "Gender"
.SelectedIndex = 0
.AutoCompleteMode = AutoCompleteMode.SuggestAppend
.AutoCompleteSource = AutoCompleteSource.ListItems

 

I tried this one but comes out with a syntax error from clause.
myConnToAccess = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\Alex\documents\visual studio 2015\Projects\WindowsApplication5\WindowsApplication5\Medical Registry database.mdb")
Dim READER As OleDbDataReader
Try
myConnToAccess.Open()
Dim Query As String
Query = "select * from Medical Registry.Record"
mySQLCommand = New OleDbCommand(Query, myConnToAccess)
READER = mySQLCommand.ExecuteReader
While READER.Read
Dim M = READER.GetString("Gender")
GenderComboBox.Items.Add(M)
End While
myConnToAccess.Close()
Catch ex As OleDbException
MsgBox(ex.Message)
Finally
myConnToAccess.Dispose()
End Try
Any clue?

 

When you write SQL query, clause FROM expects you to list tables (in database). So if table is called either "Medical" or "MedicalRegistry" you should write either "select * from Medical" or "select * from MedicalRegistry". I suspect the part ".Record" is illegal for a table name.

Edit: In case "MedicalRegistry" is the name of database and "Record" is the name of the table you probably should omit the name of database.

 

Medical Registry database is the name of the database. However I omit it and it came out with

The SELECT statement includes a reserved word or an argument name thas is misspelled or missing, or the puntcuation is incorrect

I am lost in it

 

If database name has space character then you should enclose it with quotes or double quotes - that depends on SQL implementation.
And as for using a reserved word as a name for table (assuming 'Record' is the one) you should find the syntax for that case. For example, in MS SQL server you should enclose such name in '[' and ']' - like "select * from [Record]". How it is done in Access you should search in its documentation.

Edit: In MS SQL server:

Edit: Access 2007 SQL reference
https://msdn.microsoft.com/en-us/library/bb208875(v=office.12).aspx

Edit: Try and paste current code here again.

 

I fix the issue creating one to many relations in access with tables having the values that i want and displaying them in another table. There were no need of any code anymore. Thank you man