none
Invalid use of Null?!? RRS feed

  • Question

  • I am connecting to a Macro-enabled Excel Spreadsheet. I am using the following code:

    strSQLQuery =
                        "SELECT * "+
                        "FROM (SELECT * " +
                        "FROM (SELECT * FROM [7 AM$A14:O168] UNION " +
                        "SELECT * FROM [7.30 AM$A14:O168] UNION " +
                        "SELECT * FROM [8 AM$A14:O168] UNION " +
                        "SELECT * FROM [9 AM$A14:O168] UNION " +
                        "SELECT * FROM [10 AM$A14:O168] UNION " +
                        "SELECT * FROM [11 AM$A14:O168] UNION " +
                        "SELECT * FROM [12 PM$A14:O168] UNION " +
                        "SELECT * FROM [12.30 PM$A14:O168] UNION " +
                        "SELECT * FROM [1-1.30 PM$A14:O168] UNION " +
                        "SELECT * FROM [2 PM$A14:O168] UNION " +
                        "SELECT * FROM [4 PM$A14:O168] UNION " +
                        "SELECT * FROM [5 PM$A14:O168] UNION " +
                        "SELECT * FROM [6-7 PM$A14:O168]) " +
                        "WHERE [Scheduled Time In] IS NOT NULL " +
                        "AND [Scheduled End Time] IS NOT NULL) AS M " +
                        "WHERE (M.[Status] = 'Absent' OR M.[Status] = 'Vacation' OR M.[Status] = 'Acknowledged' OR M.[Status] = 'Sick Day') " +
                        "AND CDate(M.[Scheduled Time In]) >= @TimeLastUpdate " +
                        "AND CDate(M.[Scheduled End Time]) <= @TimeLastUpdate " +
                        "ORDER BY M.[Status]";
                    daAASelection.SelectCommand = new OleDbCommand(strSQLQuery, oleConnection);
                    daAASelection.SelectCommand.Parameters.Add(new OleDbParameter("@TimeLastUpdate", DateTime.Parse(DateTime.Now.ToString("h:mm tt"))));
                    daAASelection.Fill(dsHeadcount, "Absent");
    The final line generates the exception: Invalid use of Null. If I remove the CDate, the query will run, but it will find no results. I know that there are around 30 rows that match the criteria. The data in [Scheduled Time In] and [Scheduled End Time] is in Excel time format. Any idea what the error means? Where is the "Null" coming from?

    Monday, November 7, 2016 5:16 PM

Answers

  • I figured it out. I was not converting my time correctly for comparison. This worked:

    strSQLQuery =
                        "SELECT * "+
                        "FROM (SELECT * FROM [7 AM$A14:O168] UNION " +
                        "SELECT * FROM [7.30 AM$A14:O168] UNION " +
                        "SELECT * FROM [8 AM$A14:O168] UNION " +
                        "SELECT * FROM [9 AM$A14:O168] UNION " +
                        "SELECT * FROM [10 AM$A14:O168] UNION " +
                        "SELECT * FROM [11 AM$A14:O168] UNION " +
                        "SELECT * FROM [12 PM$A14:O168] UNION " +
                        "SELECT * FROM [12.30 PM$A14:O168] UNION " +
                        "SELECT * FROM [1-1.30 PM$A14:O168] UNION " +
                        "SELECT * FROM [2 PM$A14:O168] UNION " +
                        "SELECT * FROM [4 PM$A14:O168] UNION " +
                        "SELECT * FROM [5 PM$A14:O168] UNION " +
                        "SELECT * FROM [6-7 PM$A14:O168]) AS M " +
                        "WHERE (M.[Status] = 'Absent' OR M.[Status] = 'Vacation' OR M.[Status] = 'Acknowledged' OR M.[Status] = 'Sick Day') " +
                        "AND M.[Scheduled Time In]<=@TimeLastUpdate " +
                        "AND M.[Scheduled End Time]>= @TimeLastUpdate " +
                        "ORDER BY M.[Status]";
                    daAASelection.SelectCommand = new OleDbCommand(strSQLQuery, oleConnection);
                    daAASelection.SelectCommand.Parameters.Add(new OleDbParameter("@TimeLastUpdate", OleDbType.Date));
                    daAASelection.SelectCommand.Parameters["@TimeLastUpdate"].Value = DateTime.FromOADate(DateTime.Now.ToOADate() - DateTime.Parse(DateTime.Now.ToString("MM/dd/yyyy")).ToOADate());


    Monday, November 7, 2016 8:54 PM

All replies

  • If it helps, here is the exact error:

    Unhandled Exception: System.Data.OleDb.OleDbException: Invalid use of Null
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
       at Headcount.Program.Main(String[] args)

    Monday, November 7, 2016 7:39 PM
  • Hi,

    Mainly this is not a relevant question for this forum. Though I can give you some idea on this issue from my expecrience in past:

    It happens because you can not CDate with NULL values in SQL (as far as I know). Can you try the following.. it may help..

    WHERE CDATE(Nz( [Scheduled Time In]) IS NOT NULL

    Vish Mishra

    Monday, November 7, 2016 7:57 PM
  • I am already making sure that the value is not null. NZ is not a function in Excel. That is an Access-only function (according to the research I have done).
    Monday, November 7, 2016 8:04 PM
  • I figured it out. I was not converting my time correctly for comparison. This worked:

    strSQLQuery =
                        "SELECT * "+
                        "FROM (SELECT * FROM [7 AM$A14:O168] UNION " +
                        "SELECT * FROM [7.30 AM$A14:O168] UNION " +
                        "SELECT * FROM [8 AM$A14:O168] UNION " +
                        "SELECT * FROM [9 AM$A14:O168] UNION " +
                        "SELECT * FROM [10 AM$A14:O168] UNION " +
                        "SELECT * FROM [11 AM$A14:O168] UNION " +
                        "SELECT * FROM [12 PM$A14:O168] UNION " +
                        "SELECT * FROM [12.30 PM$A14:O168] UNION " +
                        "SELECT * FROM [1-1.30 PM$A14:O168] UNION " +
                        "SELECT * FROM [2 PM$A14:O168] UNION " +
                        "SELECT * FROM [4 PM$A14:O168] UNION " +
                        "SELECT * FROM [5 PM$A14:O168] UNION " +
                        "SELECT * FROM [6-7 PM$A14:O168]) AS M " +
                        "WHERE (M.[Status] = 'Absent' OR M.[Status] = 'Vacation' OR M.[Status] = 'Acknowledged' OR M.[Status] = 'Sick Day') " +
                        "AND M.[Scheduled Time In]<=@TimeLastUpdate " +
                        "AND M.[Scheduled End Time]>= @TimeLastUpdate " +
                        "ORDER BY M.[Status]";
                    daAASelection.SelectCommand = new OleDbCommand(strSQLQuery, oleConnection);
                    daAASelection.SelectCommand.Parameters.Add(new OleDbParameter("@TimeLastUpdate", OleDbType.Date));
                    daAASelection.SelectCommand.Parameters["@TimeLastUpdate"].Value = DateTime.FromOADate(DateTime.Now.ToOADate() - DateTime.Parse(DateTime.Now.ToString("MM/dd/yyyy")).ToOADate());


    Monday, November 7, 2016 8:54 PM