ADO Recordset - vbscript IsNull() function evaluat

Results 1 to 3 of 3

Thread: ADO Recordset - vbscript IsNull() function evaluat

  1. #1
    Andrew Hirst Guest

    Default ADO Recordset - vbscript IsNull() function evaluat

    Hi and thanks for reading. This has me stumped.<BR>I&#039;m populating an ADO Recordset, via VBScript in an ASP page, by querying a Microsoft Excel spreadsheet. The sheet contains a named range. In ASP, I then iterate through the recordset, writing the contents out to an HTML table. Before writing the contents, I am testing the value held in each field to see if it is null (so that I can write an HTML break element into the corresponding HTML &#060;td&#062; element). e.g.<BR>if isnull(rs(0)) then<BR> strHTML = &#039;html break element&#039;<BR>else<BR> strHTML = rs(0)<BR>end if <BR><BR>the problem is that for indeterminable cells in the spreadsheet/fields in the ADO recordset, the IsNull function evaluates to true, even when a cell contains a non-null value (i.e. cell S8 might contain the text string "020ABJ") but IsNull thinks it is NULL !!!!! So, I check the spreadsheet again and the value is there. I edit the cell and type the same value in, save the spreadsheet and run the query again. This time it pickes it up. Any ideas?<BR>regards<BR>

  2. #2
    Robert Stewart Guest

    Default RE: ADO Recordset - vbscript IsNull() function eva

    personally because of the ADO translations for using a file based DSN for excel i try to avoid XLS pages, try running it as a true CSV format that for starters should help .. if you need to stick the excel formatting, then try it the otherway around instead of looking at it easy as a IsNull(value) then try it as <BR><BR>if rs(0) &#060;&#062; "" OR IsNumeric(rs(0)) OR Len(rs(0)) &#062; 0 then<BR>blah blah<BR>xls files have been an issue in alot of the dsnless connectivity issues i&#039;ve been dealing with , and it may be an extra step to open it in excel then convert it to a txt or csv format but it saves alot of headaches, if this is for clients to access normally if you give them the requirements most all of them will be happy to do that 1 step on their side.

  3. #3
    Andrew Hirst Guest

    Default RE: ADO Recordset - vbscript IsNull() function eva

    Thanks for the reply. That would probably work. I&#039;ve found out exactly what is happening. See:<BR>Q194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset <BR>That article relates to DAO but the same &#039;feature&#039; exists with ADO. In summary, the cause is that the Excel ISAM driver attempts to determine a default datatype for a column, but it only checks a number of cells, that number being determined by a registry entry (see the article). If the remaining cells in the column contain a variety of types, however, (in my case, text and numbers), then the cells that don&#039;t fit in with ISAM&#039;s choice of default datatype are returned to ADO as NULL values. The fix is to format the cells as text, THEN re-enter the current values for each cell (F2 -&#062; enter), but you can only do one cell at a time!!!! (unless anybody else knows better). It may be better to export the spreadsheet to .csv, as suggested in a follow-up, particularly if there are a lot cells to change.<BR>regards<BR><BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts