Nishan Fernando Nishan Fernando - 1 year ago 102
SQL Question

Sql/ - Invalid Operation Exception

The is a database driven currency converter, where the program has to fetch the exchange rates from the database and display them. I created the database using Microsoft Access all the data types are set to "Currency" except the Date field where it is "Date/Time".

This is what I've done so far.

sql = "SELECT TOP 1 USD,EUR,GBP FROM dbexchangeRates WHERE Date='" & DateTimePicker1.Text.ToString & "'"

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


adapter.Fill(ds, "rates")


End Using

This "An exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll but was not handled in user code. Additional information: Data type mismatch in criteria expression." shows up when i run.
I was told to run data validation but I'm unsure what validations I'm suppose to use.

Thank You.

Answer Source

The problem is likely that DateTimePicker1.Text.ToString is not returning a valid date, and this is making the query fail. Possible causes:

  • It is not a date (I'm assuming the picker enforces a date value, but still there could be an empty value or something). In this case, you would need to alter DateTimePicker1_ValueChanged to handle this case and not call that query.
  • The date format from Excel doesn't match what Access expects. In this case, you could change the format of the date in either Excel or Access (you can find lots of help about this in other questions, etc.).

To figure this out, use the debugger or a simple print statement to view the contents of sql.

The problem may be obvious when you see it; if not, you can also try manually running that sql statement in Access and see what happens.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download