Invalid Cast exception when dealing with Integers

Question

I need some help with an issue I'm having.

I am currently building an export program to pull data from an Excel document and push it to a FoxPro database (I know, old tech), however I have hit a snag. The code attempts to pull data from a column containing rents for a month.

 importCommand.Parameters["PRICEASK"].Value = exportReader.IsDBNull(14)
                    ? (object) DBNull.Value
                    : exportReader.GetInt32(14);

However, I get this error:

Additional information: Specified cast is not valid.

I am quite frankly stumped as to why I am getting this error

I should mention that the rest of the code works fine. I'm using parameterised queries to pull and push the data. — Joshua Cameron-Mackintosh, Jan 11 '17 at 16:32
something like this might `work int i = Convert.ToInt32(exportReader.GetValue(14));` — cyboashu, Jan 11 '17 at 16:35
That doesn't mean anything, 349 might appear to be a number but it could be a string. Check the excel file, click the column and see what format it is — Ian Murray, Jan 11 '17 at 16:37
It is not something related to VFP really. Why would you cast to an object, simply cast to an int? and you shoud be good to go (assuming your parameter definition is correct). In regard to VFP, since you are trying to read from Excel and write to VFP, it would be much easier if you have used ExecScript and directly coded some script that would read from excel via ODBC or OLEDB and write to VFP. (and please pay some attention to closing your open threads) — Cetin Basoz, Jan 11 '17 at 17:35
Unfortunately someone has disabled adding answers to this post. Then I am adding the correct answer here: It simply should be: importCommand.Parameters["PRICEASK"].Value = exportReader[14].Value; — Cetin Basoz, Jan 15 '17 at 19:29

Ian Murray · Accepted Answer · 2017-01-11T16:39:54.197

1

It's been a while since I was working with Excel in a similar capacity but ran into a very similar problem. The issue was coming from the read, you're telling it that you're getting an Int and it doesn't like it, probably because the column/cell isn't formatted as an integer. There should be an option to read to cell value to a string. Something like:

importCommand.Parameters["PRICEASK"].Value = exportReader.IsDBNull(14)
                ? (object) DBNull.Value
                : exportReader.GetString(14);

Get the string and cast once you have that to store it in your database. Try that. Good luck.

edited Jan 11 '17 at 16:39

answered Jan 11 '17 at 16:34

Ian Murray

339
1
12

Hi Mate, I've checked the excel to make sure the formatting is correct, and the column in question is formatted as number. I've also previously tried converting to a string and parsing back to an into when I do the push but to no avail – Joshua Cameron-Mackintosh Jan 11 '17 at 16:43
1

@JoshuaCameron-Mackintosh "I've also previously tried converting to a string" - Can you output that string and paste it in here? There can be hidden/zero width characters in a string. E.g., This parses fine: `Int32.Parse("99");`, and this doesn't: `Int32.Parse("99‍");` -- the second one has a [`Zero Width Joiner`](http://www.fileformat.info/info/unicode/char/200d/index.htm) in it. Because I pasted the hidden character at the end, this returns `true`: `string.Equals("99‍", "99\u200d")` (try it, SO does not strip the character so it's here in the comment too). – Quantic Jan 11 '17 at 16:52

Invalid Cast exception when dealing with Integers

1 Answers1