ADO/SQL empty date column error. Please help

jim_h · #1 (**permalink**) 08-16-04, 16:49

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

rokslide · #2 (**permalink**) 08-16-04, 19:24

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.

Seppuku · #3 (**permalink**) 08-16-04, 19:24

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?

jim_h · #4 (**permalink**) 08-16-04, 21:33

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.

jim_h · #5 (**permalink**) 08-16-04, 21:45

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().

rokslide · #6 (**permalink**) 08-16-04, 22:47

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

Seppuku · #7 (**permalink**) 08-17-04, 10:26

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

jim_h · #8 (**permalink**) 08-17-04, 15:45

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.

rokslide · #9 (**permalink**) 08-17-04, 19:39

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??

jim_h · #10 (**permalink**) 08-18-04, 12:10

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech