Solved

ADO Recordset must read integer fields before bit fields?

Posted on 2004-04-12
17
307 Views
Last Modified: 2008-03-06
Ok, don't spend too much on this, as I have a workaround, but I'd like to know the real deal with this. I have code that returns an ADODB.Recordset object from a stored procedure. The problem is that when I tried to get data from a particular integer field it would return an empty object (and the field was *not* empty). To make a long story short I isolated the problem to my getting a boolean (bit in SQL Serverland) field before I returned my integer field, so the following code works:

intTimeUnits = rs("number_of_time_units")
If rs("before_flag") Then
      intDirection = -1
Else
      intDirection = 1
End If

And the following code fails (no error, just returns an empty object for intTimeUnits)
If rs("before_flag") Then
      intDirection = -1
Else
      intDirection = 1
End If
intTimeUnits = rs("number_of_time_units")

If you know why this is, please let me know. I will accept "ADO sucks" if no other acceptable answer is found! ;)
0
Comment
Question by:sforcier
  • 6
  • 6
  • 4
  • +1
17 Comments
 
LVL 6

Expert Comment

by:Mike_Metro
Comment Utility
Have you tried using:
rs.Fields(<FieldName>).value
instead of
rs(<FieldName>)
0
 
LVL 6

Author Comment

by:sforcier
Comment Utility
Thanks for the quick response. Unfortunately that does not resolve the issue.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
And you don't have On Error Resume Next, right?
0
 
LVL 6

Author Comment

by:sforcier
Comment Utility
Correct, there is no "On Error Resume Next".
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Than post all the relevant ASP and Stored Procedure (asuming it is only one) code.  That may give us a better idea what is going on.
0
 
LVL 28

Expert Comment

by:sybe
Comment Utility
I never met this problem. I agree with acperkins to post your code, esp the stored procedure, because that is what makes this different.
0
 
LVL 6

Author Comment

by:sforcier
Comment Utility
Here's the stored procedure (straight forward many-to-many style table joining):

CREATE PROCEDURE dbo.DistributedAssessmentGetAllActiveAutoRemindersNoSecurity
AS
SELECT da.DistributedAssessment_oid, da.due_date, da.distribution_date, da.complete_date, ar.*
FROM
      GCSRepository.DistributedAssessment da
      , GCSRepository.DistributedAssessment_reminder_list ar_list
      , GCSRepository.AutoReminder ar
WHERE
      da.DistributedAssessment_oid = ar_list.DistributedAssessment_oid
      and ar_list.ArrayAutoReminder_reminder_list__AutoReminder_oid = ar.AutoReminder_oid
      and
-- Only querying incomplete distributed assessments
      (
            da.complete_date IS NULL
            or da.complete_date > GetDate()
      )


And the ASP code (this fails by putting an empty into the intTimeUnits variable, will work if intTimeUnits is assigned before the 'if' clause):

      Dim con, rs, strSql, intDirection, x, foo
      Dim datTargetDate, datStartDate, intTimeUnits
      Set con = Server.CreateObject("ADODB.Connection")
      Set rs = Server.CreateObject("ADODB.Recordset")
      
      con.Open "componentdb", "GCSRepository", "Genesys"
      
      strSql = "exec dbo.DistributedAssessmentGetAllActiveAutoRemindersNoSecurity"
      Set rs = con.Execute(strSql)

'Iterate through the records and determine which rows need to have reminders sent out
      While Not(rs.EOF)
      
            'First determine the "direction" of the reminder
            If rs("before_flag") Then
                  intDirection = -1
            Else
                  intDirection = 1
            End If

            intTimeUnits = rs.Fields("number_of_time_units").Value

                                ...

                                rs.MoveNext()
                 Wend


It fails on *all* iterations of the while loop with the above code.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
And this would be using SQL Server 7, perhaps?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 6

Author Comment

by:sforcier
Comment Utility
SQL Server 2000
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Than I am out of ideas.
0
 
LVL 28

Expert Comment

by:sybe
Comment Utility
the only thing that comes to my mind is that fields with type="text" should be last in the field-order, because reading them out sometimes makes the other fields to be null.

Do you get any text fields in your recordset?
0
 
LVL 6

Author Comment

by:sforcier
Comment Utility
That's suspiciously similar to what's happening here. It is *pehaps* possible that the rs("before_flag") is being return in ASP as a string (despite the fact that it's a bit in SQL Server). Let me check...

Nope, rs("before_flag") is returning a boolean. It's certainly possible that text isn't the only type that will nullify the other fields. Any ideas on why text behaves that way?
0
 
LVL 28

Accepted Solution

by:
sybe earned 50 total points
Comment Utility
with type="text" i mean the SQL Server field-type.

It was a bug in ADO, i haven't met it for a long time though.

sSQL = "SELECT id, name, descr, somenumber FROM table"
' where "descr" is of type "text", there is a another field selected *after* the text field
Set oRS = oConn.Execute(sSQL)
Do While Not oRS.EOF
    sDescription = oRS("descr")
    iNumber = oRS("somenumber")  ' <=== iNumber would turn out to be empty if selected and (?) read out after the "text" field
    oRS.MoveNext
Loop


That is why I am asking you in you select statement ("da.DistributedAssessment_oid, da.due_date, da.distribution_date, da.complete_date, ar.*" there are any fields of type = "text" present. Maybe you should not do "ar.*", but only name the fields that you really need.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
But what sybe is referring to is making text (or blob data types) last in the Select statement or they (and only they) will show up as null. This has no bearing IMHO in this case when you are changing the way code accesses that recordset.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
To put it more clearly in both cases the Select statement is the same.
0
 
LVL 28

Expert Comment

by:sybe
Comment Utility
I try to remember what was the exact problem. I am not sure if it was only the text/blob fields that showed up as empty, i recall that it were all fields that were mentioned after the text/blob fields, and it also had a relation to the order in which the fields were being read out in ASP.

But to be honest, I am not sure about this. And it *is* something different then what the problem is in this case. However, there is a similarity. And I was pointing to that similarity, not saying that this is the same thing.
0
 
LVL 6

Author Comment

by:sforcier
Comment Utility
Unfortunately, I do have an 'ntext' field in the ar.* table. Sybe, is your suggestion to try something like

SELECT blah..., ar.number_of_time_units, ar.message

instead of

SELECT blah..., ar.message, ar.number_of_time_units

in the stored procedure? Or is your point that if a text field is anywhere in the select at all then the ASP is going to be screwy (if the fields are processed in a particular order)?
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now