Error with datareader when value from timestamp column is null

1

As a follow up to this question, when I use that code and one of the results has a NULL value for a DateTime column, I get the following error:

ERROR [22007] [Cache ODBC][State : 22007][Native Code 22007] [E:!work\projects\dotnet_feed\dotnetFeed] Invalid datetime format

Here is the full code, adapted from the question above:

   public void doQuery(string id, string sql, string ds)
    {
        string connectionString = "DSN=" + ds + ";";
        string queryString = sql;

        payload result = new payload();
        var resultList = new List<Dictionary<string, dynamic>>();
        result.id = id;
        result.timestmap = DateTime.Now;

        using (OdbcConnection connection = new OdbcConnection(connectionString))
        {
            OdbcCommand command = new OdbcCommand(queryString, connection);

            try
            {
                connection.Open();
                OdbcDataReader reader = command.ExecuteReader();
                if (reader.HasRows) //not working when no results.
                {
                    while (reader.Read())
                    {
                        var t = new Dictionary<string, dynamic>();
                        for (var i = 0; i < reader.FieldCount; i++)
                        {
                            t[reader.GetName(i)] = reader[i];
                        }
                        resultList.Add(t);
                    }
                }
                else
                {
                    log("NO RESULTS");
                }
                reader.Close();
                result.data = resultList;
                string output = JsonConvert.SerializeObject(result);
            }
            catch (Exception ex)
            {
                log(ex.Message);
            }
        }
    }

Again, if the result set has no nulls in a datetime column it works fine. It also works fine with nulls in other columns. How can I fix this?

ex.ToString()

"System.Data.Odbc.OdbcException (0x80131937): ERROR [22007] [Cache ODBC][State : 22007][Native Code 22007]\r\n[E:\!work\projects\dotnet_feed\dotnetFeed\]\r\nInvalid datetime format\r\n at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)\r\n at System.Data.Odbc.OdbcDataReader.GetData(Int32 i, SQL_C sqlctype, Int32 cb, Int32& cbLengthOrIndicator)\r\n at System.Data.Odbc.OdbcDataReader.GetData(Int32 i, SQL_C sqlctype)\r\n
at System.Data.Odbc.OdbcDataReader.internalGetDateTime(Int32 i)\r\n
at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i, TypeMap typemap)\r\n at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i)\r\n at System.Data.Odbc.OdbcDataReader.get_Item(Int32 i)\r\n at dotnet_feed.Form1.doQuery(queryObject qo) in E:\!work\projects\dotnet_feed\dotnetFeed\dotnet_feed\Form1.cs:line 92" string

Line 92: t[reader.GetName(i)] = reader[i];

datetime
null
odbc
datareader
intersystems-cache
asked on Stack Overflow Sep 8, 2016 by JOATMON • edited May 23, 2017 by Community

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0