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