Here is my particular situation, in case anyone cares. We receive Excel spreadsheets from a customer, that has data for an order. I then connect to these spreadsheets using Oledb to populate a couple columns that are blank that need to be filled in. I was using the code below for several months, which worked fine, until one day it didn't.
I discovered that I now need to use single quotes around my 2nd parameter. ('{1}'). Something in the format of the spreadsheet must have changed. I wanted to come up with code that can handle either situation, in case it changes again. Here is what I'm doing now, using nested Try...Catches.Code:oleCommand.CommandText = String.Format("UPDATE [Sheet1$] SET [F9] = '{0}' WHERE [F7] = {1}", FGnum, ppiOrder) oleCommand.ExecuteNonQuery() oleCommand.CommandText = String.Format("UPDATE [Sheet1$] SET [F11] = '{0}' WHERE [F7] = {1}", runNum, ppiOrder) oleCommand.ExecuteNonQuery()
It first tries to update the sheet using single quotes. If that errors out then it tries it without the quotes. The error text I'm getting is "Data type mismatch in criteria expression." I don't think there's a specific exception you can catch for this. Is there a better way to handle this situation? If there's one thing I've learned over the years, it's that you can't count on a customer to provide consistant data.Code:Try oleCommand.CommandText = String.Format("UPDATE [Sheet1$] SET [F9] = '{0}' WHERE [F7] = '{1}'", FGnum, ppiOrder) oleCommand.ExecuteNonQuery() oleCommand.CommandText = String.Format("UPDATE [Sheet1$] SET [F11] = '{0}' WHERE [F7] = '{1}'", runNum, ppiOrder) oleCommand.ExecuteNonQuery() Catch ex As Exception Try oleCommand.CommandText = String.Format("UPDATE [Sheet1$] SET [F9] = '{0}' WHERE [F7] = {1}", FGnum, ppiOrder) oleCommand.ExecuteNonQuery() oleCommand.CommandText = String.Format("UPDATE [Sheet1$] SET [F11] = '{0}' WHERE [F7] = {1}", runNum, ppiOrder) oleCommand.ExecuteNonQuery() Catch ex2 As Exception MessageBox.Show(ex2.Message) End Try End Try




Reply With Quote