Guru3D.com Forums

Go Back   Guru3D.com Forums > General > Programming/Html
Programming/Html C++,Visual Basic,HTML,Flash,OpenGL,Modelling,Java.



Reply
 
Thread Tools Display Modes
Retrieving values from access database to a combobox in visual basic
Old
  (#1)
Alexis444
Master Guru
 
Alexis444's Avatar
 
Videocard: EVGA GTX 950 SSC 2GB
Processor: AMD FX 8350 Blk Ed
Mainboard: ASRock 970 Extreme4
Memory: Balistix 12Gb DDR3
Soundcard: VIA HD Audio (onboard)
PSU: Thermal TR2700w
Default Retrieving values from access database to a combobox in visual basic - 11-01-2016, 20:14 | posts: 177 | Location: P.R.

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 [b] give me error and I don't know what goes there

Thanks for the assistance
   
Reply With Quote
 
Old
  (#2)
Alexis444
Master Guru
 
Alexis444's Avatar
 
Videocard: EVGA GTX 950 SSC 2GB
Processor: AMD FX 8350 Blk Ed
Mainboard: ASRock 970 Extreme4
Memory: Balistix 12Gb DDR3
Soundcard: VIA HD Audio (onboard)
PSU: Thermal TR2700w
Default 11-02-2016, 12:08 | posts: 177 | Location: P.R.

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\WindowsApplicati on5\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_dat abaseDataSet.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_dat abaseDataSet.Record)
myConnToAccess = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\Alex\documents\visual studio 2015\Projects\WindowsApplication5\WindowsApplicati on5\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 by Alexis444; 11-02-2016 at 14:00.
   
Reply With Quote
Old
  (#3)
Alexis444
Master Guru
 
Alexis444's Avatar
 
Videocard: EVGA GTX 950 SSC 2GB
Processor: AMD FX 8350 Blk Ed
Mainboard: ASRock 970 Extreme4
Memory: Balistix 12Gb DDR3
Soundcard: VIA HD Audio (onboard)
PSU: Thermal TR2700w
Default 11-02-2016, 15:20 | posts: 177 | Location: P.R.

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\WindowsApplicati on5\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?
   
Reply With Quote
Old
  (#4)
mbk1969
Ancient Guru
 
mbk1969's Avatar
 
Videocard: GeForce GTX 970
Processor: I7-4930
Mainboard: Asus p9x79
Memory: G Skill RipjawZ, 16GB
Soundcard: Onboard + FiiO E17
PSU: 1000 W
Default 11-03-2016, 19:29 | posts: 3,509 | Location: Moscow, Russia

Quote:
Originally Posted by Alexis444 View Post
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\WindowsApplicati on5\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.

Last edited by mbk1969; 11-03-2016 at 19:32.
   
Reply With Quote
 
Old
  (#5)
Alexis444
Master Guru
 
Alexis444's Avatar
 
Videocard: EVGA GTX 950 SSC 2GB
Processor: AMD FX 8350 Blk Ed
Mainboard: ASRock 970 Extreme4
Memory: Balistix 12Gb DDR3
Soundcard: VIA HD Audio (onboard)
PSU: Thermal TR2700w
Default 11-08-2016, 17:59 | posts: 177 | Location: P.R.

Quote:
Originally Posted by mbk1969 View Post
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
   
Reply With Quote
Old
  (#6)
mbk1969
Ancient Guru
 
mbk1969's Avatar
 
Videocard: GeForce GTX 970
Processor: I7-4930
Mainboard: Asus p9x79
Memory: G Skill RipjawZ, 16GB
Soundcard: Onboard + FiiO E17
PSU: 1000 W
Default 11-08-2016, 21:08 | posts: 3,509 | Location: Moscow, Russia

Quote:
Originally Posted by Alexis444 View Post
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:
Quote:
Delimited identifiers
Are enclosed in double quotation marks (") or brackets ([ ]). Identifiers that comply with the rules for the format of identifiers might not be delimited. For example:
Code:
SELECT *  
FROM [TableX]         --Delimiter is optional.  
WHERE [KeyCol] = 124  --Delimiter is optional.
Identifiers that do not comply with all the rules for identifiers must be delimited in a Transact-SQL statement. For example:
Code:
SELECT *  
FROM [My Table]      --Identifier contains a space and uses a reserved keyword.  
WHERE [order] = 10   --Identifier is a reserved keyword.
Edit: Access 2007 SQL reference
https://msdn.microsoft.com/en-us/lib...ffice.12).aspx

Edit: Try and paste current code here again.

Last edited by mbk1969; 11-08-2016 at 21:17.
   
Reply With Quote
Old
  (#7)
Alexis444
Master Guru
 
Alexis444's Avatar
 
Videocard: EVGA GTX 950 SSC 2GB
Processor: AMD FX 8350 Blk Ed
Mainboard: ASRock 970 Extreme4
Memory: Balistix 12Gb DDR3
Soundcard: VIA HD Audio (onboard)
PSU: Thermal TR2700w
Default 11-16-2016, 15:13 | posts: 177 | Location: P.R.

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
   
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump



Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
vBulletin Skin developed by: vBStyles.com
Copyright (c) 2017, All Rights Reserved. The Guru of 3D, the Hardware Guru, and 3D Guru are trademarks owned by Hilbert Hagedoorn.