How can I preempt a “Specified cast is not valid”

问题:

In querying an MS Access database in a Web API app, I get a "Specified cast is not valid" exception if I'm trying to assign an empty string to a variable. IOW, when this code:

var accountID = oleDbD8aReader.GetInt16(0); 
var platypusName = oleDbD8aReader.GetString(1); 

...reaches a record where the second column in the result set contains an empty/null string, it bombs.

So, I thought I could head that off at the pass like this:

string platypusName;
var accountID = oleDbD8aReader.GetInt16(0); 
if (!string.IsNullOrEmpty(oleDbD8aReader.GetString(1)))
{
    platypusName = oleDbD8aReader.GetString(1); 
}
else
{
    platypusName = string.Empty;
}

...but it doesn't work - I still get a "Specified cast is not valid" exception.

How can I safely check for an empty string/null value there and ignore that pass through the result set so that it will get subsequent records?

Or can I preclude this by changing the SQL statement to exclude empty/null strings from the result set? If so, how? The query is in this format:

SELECT td_duckbill_accounts.platypus_no, t_accounts.name 
FROM t_accounts 
INNER JOIN td_duckbill_accounts ON t_accounts.account_no = td_duckbill_accounts.account_no      
ORDER BY td_duckbill_accounts.platypus_no

回答1:

I think having the query return empty string is easy solution:

SELECT td_duckbill_accounts.platypus_no, 
       IIF(ISNULL(t_accounts.name),'',t_accounts.name) AS name
FROM t_accounts 
INNER JOIN td_duckbill_accounts ON t_accounts.account_no = td_duckbill_accounts.account_no      
ORDER BY td_duckbill_accounts.platypus_no

This should also work but I can't test it right now:

SELECT td_duckbill_accounts.platypus_no, 
       Nz(t_accounts.name,'') AS name
FROM t_accounts 
INNER JOIN td_duckbill_accounts ON t_accounts.account_no = td_duckbill_accounts.account_no      
ORDER BY td_duckbill_accounts.platypus_no

回答2:

Sometimes what is needed is to change the data type method used in OleDbDataReader, as this code and the comments show:

while (oleDbD8aReader != null && oleDbD8aReader.Read())
{
    string accountId = oleDbD8aReader.GetString(0);
    string accountName = oleDbD8aReader.GetString(1);
    //int useOnItems = oleDbD8aReader.GetInt32(2); <= get "specified cast not valid" with Int32
    int useOnItems = oleDbD8aReader.GetInt16(2); // This works
    expenses.Add(new Expense { account_id = accountId, name = accountName, use_on_items = useOnItems });
}

The data type in the underlying table is Integer, so I guess for LongInteger (another Access integer type) GetInt32() would be the OleDbDataReader method to use.