Retrieving values from access database to a combobox in visual basic

Discussion in 'Programming/Html' started by Alexis444, Nov 1, 2016.

  1. Alexis444

    Alexis444 Member Guru

    Messages:
    177
    Likes Received:
    0
    GPU:
    EVGA GTX 950 SSC 2GB
    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
     
  2. Alexis444

    Alexis444 Member Guru

    Messages:
    177
    Likes Received:
    0
    GPU:
    EVGA GTX 950 SSC 2GB
    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
     
    Last edited: Nov 2, 2016
  3. Alexis444

    Alexis444 Member Guru

    Messages:
    177
    Likes Received:
    0
    GPU:
    EVGA GTX 950 SSC 2GB
    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?
     
  4. mbk1969

    mbk1969 Ancient Guru

    Messages:
    3,569
    Likes Received:
    13
    GPU:
    GeForce GTX 970
    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.
     
    Last edited: Nov 3, 2016

  5. Alexis444

    Alexis444 Member Guru

    Messages:
    177
    Likes Received:
    0
    GPU:
    EVGA GTX 950 SSC 2GB

    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
     
  6. mbk1969

    mbk1969 Ancient Guru

    Messages:
    3,569
    Likes Received:
    13
    GPU:
    GeForce GTX 970
    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.
     
    Last edited: Nov 8, 2016
  7. Alexis444

    Alexis444 Member Guru

    Messages:
    177
    Likes Received:
    0
    GPU:
    EVGA GTX 950 SSC 2GB
    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
     

Share This Page