We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

ADO Recordset must read integer fields before bit fields?

sforcier
sforcier asked
on
Medium Priority
347 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! ;)
Comment
Watch Question

Have you tried using:
rs.Fields(<FieldName>).value
instead of
rs(<FieldName>)

Author

Commented:
Thanks for the quick response. Unfortunately that does not resolve the issue.
CERTIFIED EXPERT
Top Expert 2012

Commented:
And you don't have On Error Resume Next, right?

Author

Commented:
Correct, there is no "On Error Resume Next".
CERTIFIED EXPERT
Top Expert 2012

Commented:
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.

Commented:
I never met this problem. I agree with acperkins to post your code, esp the stored procedure, because that is what makes this different.

Author

Commented:
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.
CERTIFIED EXPERT
Top Expert 2012

Commented:
And this would be using SQL Server 7, perhaps?

Author

Commented:
SQL Server 2000
CERTIFIED EXPERT
Top Expert 2012

Commented:
Than I am out of ideas.

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

Author

Commented:
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?
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Top Expert 2012

Commented:
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.
CERTIFIED EXPERT
Top Expert 2012

Commented:
To put it more clearly in both cases the Select statement is the same.

Commented:
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.

Author

Commented:
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)?
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.