Nishan Fernando Nishan Fernando - 1 month ago 6
Vb.net Question

Displaying Access Database With vb

This is a database driven currency converter. What the program is suppose to do is display the records on the textboxes when the date time picker is changed accordingly.

This is what I've done so far.

Dim cnnOLEDB As New OleDbConnection
Dim strConnectionString = "provider=microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "/dbExchangeRates.accdb"


Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

cnnOLEDB.ConnectionString = strConnectionString
cnnOLEDB.Open()


End Sub

Private Sub DateTimePicker1_ValueChanged(sender As Object, e As EventArgs) Handles DateTimePicker1.ValueChanged

Dim da As OleDb.OleDbDataAdapter
Dim ds As New DataSet
Dim sql As String

sql = "SELECT USD,EUR,GBP FROM dbexchangeRates WHERE D/T='" DateTimePicker.Text.ToString
da = New OleDb.OleDbDataAdapter(sql, cnnOLEDB)
da.Fill(ds, "rates")
txtUSD = ds.Tables("rates").Rows(1).Item(1)
txtGBP = ds.Tables("rates").Rows(2).Item(2)
txtEUR = ds.Tables("rates").Rows(3).Item(3)
End Sub


View of the database

Design View of the program

Thanks in advance.

Answer

It is better to set [D/T] as a primary Key or create an unique index on It. OR to show result in a DataGridView because every date has many rows as a result:

but for your code try this:

Dim cnnOLEDB As New OleDbConnection
Dim strConnectionString = "provider=microsoft.ACE.OLEDB.12.0;Data Source=" &  Application.StartupPath & "/dbExchangeRates.accdb"


Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

cnnOLEDB.ConnectionString = strConnectionString
cnnOLEDB.Open()


End Sub

Private Sub DateTimePicker1_ValueChanged(sender As Object, e As EventArgs) Handles DateTimePicker1.ValueChanged

Dim da As OleDb.OleDbDataAdapter
Dim ds As New DataSet
Dim sql As String

sql = "SELECT TOP 1 USD,EUR,GBP FROM dbexchangeRates WHERE [D/T]='" & DateTimePicker.Text.ToString & "'"
da = New OleDb.OleDbDataAdapter(sql, cnnOLEDB)
da.Fill(ds, "rates")

If ds.Tables("rates").Rows.Count > 0

 txtUSD.Text = ds.Tables("rates").Rows(0).Item(0).ToString
 txtGBP.Text = ds.Tables("rates").Rows(0).Item(1).ToString
 txtEUR.Text = ds.Tables("rates").Rows(0).Item(2).ToString

End IF


End Sub
Comments