Nishan Fernando Nishan Fernando - 1 month ago 5
SQL Question

Invalid Operation Exception when filling DataSet

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.



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") '


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= ?"

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

        connection.Open()

        adapter.SelectCommand.Parameters.Add("@dt", OleDb.OleDbType.Date).Value = DateTimePicker1.Value
        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.