Nishan Fernando Nishan Fernando - 1 month ago 4
SQL Question

Invalid Operation Exception vb.net/ sql

The is a database driven currency converter, where the program has to fetch the exchange rates from the database and display them.

This is what I've done so far.



Imports System.Data.OleDb

Public Class MainMenu

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 Date='" & DateTimePicker1.Text.ToString & "'"
da = New OleDb.OleDbDataAdapter(sql, cnnOLEDB)
da.Fill(ds, "rates")

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

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

End Class


I recieve an Invalid operation exception on the

a.Fill(ds, "rates") '


Preview of the database
Screen grab of the error (EDITED)

What am i doing wrong?

Thank you for your time.

Answer

Try this:

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

    Dim ds As New DataSet

    Dim sql As String = "SELECT TOP 1 USD,EUR,GBP FROM dbexchangeRates WHERE Date=" & DateTimePicker1.Value & ""

    Using connection As New OleDb.OleDbConnection(strConnectionString),
          command As New OleDb.OleDbCommand(sql, connection),
          adapter As New OleDb.OleDbDataAdapter(command)

        connection.Open()

        adapter.Fill(ds, "rates")

        connection.Close()

    End Using

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

        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

I haven't checked but I wonder if the method DateTimePicker1.ValueChanged is being called before cnnOLEDB has been initialised on MyBase.Load. This way the connection, command and adapter are all initialised at the same time. By implementing Using they will also be disposed.

You probably also want to do some data validation to ensure a valid date is set before calling the database with a query otherwise other exceptions could occur.