Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2004
    Posts
    6

    Unhappy Unanswered: ADO/SQL empty date column error. Please help

    This is my first post on this forum, so hopefully this is the right forum to post in.

    I'm using ADO in ASP to access a DSN I've set up. However, when I try to get the value of the date column in a record, it gives me this error:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

    Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

    I've come to the conclusion this only happens when the date column is empty, since my code works for all the records with a non-empty date column.

    This a sample of my code.
    do until oRS.EOF
    for each x in oRS.Fields
    Response.Write("name:" & x.name & " = " & x.value & "<br>")
    next
    oRS.MoveNext
    loop

    How do I work around this problem? I've googled this and nobody really has a solid answer. Is there a way to detect an empty date field with ADO and skip over it? I've tried storing the problem date record column into a var, but that also produces the same error.

    Any help with my problem would be great. Thanks

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    to check for a null value you should use...

    isnull(x.value)

    which will return true is x.value == null.

    you can check for an empty field using x.value=""

    I suspect your problem is something slightly different to what you think though as (in my experience) the code you have shown is unlikely to raise that error.

  3. #3
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    I hate that error. I usually see it when I have ON ERROR RESUME NEXT enabled, and errors start backing up. If you have that, turn it off temporarily.

    If you don't do the loop entirely, do you avoid the error, or does it still come up?

    Do you want to skip the whole record, or just that column of the record?
    That which does not kill me postpones the inevitable.

  4. #4
    Join Date
    Aug 2004
    Posts
    6
    Quote Originally Posted by Seppuku
    I hate that error. I usually see it when I have ON ERROR RESUME NEXT enabled, and errors start backing up. If you have that, turn it off temporarily.

    If you don't do the loop entirely, do you avoid the error, or does it still come up?

    Do you want to skip the whole record, or just that column of the record?
    How do I disable "ON ERROR RESUME NEXT"? I'm not very familiar with asp or ado. I only work with sample code and what I can dig up from my own web searches. The error only occurs when it goes on a column that is a DATE and is empty. If the row/record has all the DATE columns filled in, that error will never show up. So, it'll go until it encounters an empty DATE column, then the error appears.

    I want to be able to skip the column of the record. I need to be able to display the rest of the record.

  5. #5
    Join Date
    Aug 2004
    Posts
    6
    Quote Originally Posted by rokslide
    to check for a null value you should use...

    isnull(x.value)

    which will return true is x.value == null.

    you can check for an empty field using x.value=""

    I suspect your problem is something slightly different to what you think though as (in my experience) the code you have shown is unlikely to raise that error.
    I wish it was my code. However, it works as long as all the DATE columns are filled in. If there is an empty DATE column, the error will show up right at that column.

    I tried both of your suggestions earlier today. It basically gives me an error when I try to do anything that involves retrieving the empty DATE column. For the other columns, it will give me the correct result for isNull().

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    what is the actual column name??

    how are you executing your query to get your recordset? what sort of cursor, locking are you using??

    The error you are reporting doesn't really sound like something that should come from that part of your code. It would more normally come from some sort of sub querying, although exactly why I'm ot sure. I have seen the error before, but only once and only when I was doing something slightly unusual.

    ON ERROR RESUME NEXT and be stopped be ON ERROR GOTO 0 in asp but ON ERROR RESUME NEXT will only be "active" if you have specifically coded for it.

    You could get around this problem by using and ON ERROR RESUME NEXT before this line and then trapping it on the other side something like...
    Code:
    on error resume next
    do until oRS.EOF
      for each x in oRS.Fields
        Response.Write("name:" & x.name & " = " & x.value & "<br>")
        if err.number <> 0 then 
          Response.Write("name:" & x.name & " = null<br>")
        end if
      next
    oRS.MoveNext
    loop

  7. #7
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Yeah.. but this may be related to the Response.Write writting out a NULL value. Try the code rokslide mentioned to skip it.

    Code:
    do until oRS.EOF
      for each x in oRS.Fields
        Response.Write "name: " & x.name
        Response.Write "value: "
        if NOT IsNull(x.value) Then
          Response.Write x.value
        else
          Response.Write "&nbsp;"
        end if
      next
    oRS.MoveNext
    That which does not kill me postpones the inevitable.

  8. #8
    Join Date
    Aug 2004
    Posts
    6

    Thanks guys. I got it working.

    I've got it working. Here is a portion of the code I used,
    Code:
       Set oConn = Server.CreateObject("ADODB.Connection")
       sConnString = "DSN=Data;"
       oConn.Open(sConnString)
    
       sSQL = "SELECT number, date_scheduled, time_scheduled, date_finish, "
       sSql = sSQL & " time_finish, status, notes FROM work" 
       Set oRS = oConn.Execute(sSQL)
    
       Response.Write("<table border=1>")
    
       on error resume next
       do until oRS.EOF
          Response.Write("<tr>")
          for each x in oRS.Fields
            current_errors = oRS.ActiveConnection.Errors.Count
            Response.Write("<td>" & x.value & "&nbsp;" & "</td>")
            if current_errors < oRS.activeconnection.errors.Count then
              Response.Write("<td>&nbsp;</td>")
            end if
          next
       oRS.MoveNext
       Response.Write("</tr>")
       Loop
    
       Response.Write("</table><br>")
    This lets me skip over the columns that caused errors. This code will stop on any errors once I get rid of the 'on error resume next' line.

    Unfortunately the error description doesn't help me to resolve the problem. But as long as I can skip over it, it's have to do.

    Thanks for all the help rokslide and Seppuku.

  9. #9
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Response.write shouldn't have a problem with nulls and the error that is raised is coming from the Database connection somewhere.

    I suspect it has something to do with the way the database is set up or the dsn is set up... it could be that the database doesn't fully support ADO in some why, I really don't know...

    What happens if you specify the field names instead of looping through the fields??

  10. #10
    Join Date
    Aug 2004
    Posts
    6
    Quote Originally Posted by rokslide
    Response.write shouldn't have a problem with nulls and the error that is raised is coming from the Database connection somewhere.

    I suspect it has something to do with the way the database is set up or the dsn is set up... it could be that the database doesn't fully support ADO in some why, I really don't know...

    What happens if you specify the field names instead of looping through the fields??
    Yes, I was suspecting that the database might not be fully compatible early on. It's a bit of a proprietary database, so that's probably why.

    As for using field names, yup, I'm done that before and it caused the same problems. I actually changed to using the looping method after reading some of the replies to this thread. Besides fixing my problem, I learned a few things in this thread. I've very grateful for new knowledge.

Posting Permissions

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