• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

ADO Recordset must read integer fields before bit fields?

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
sforcier
Asked:
sforcier
  • 6
  • 6
  • 4
  • +1
1 Solution
 
Mike_MetroCommented:
Have you tried using:
rs.Fields(<FieldName>).value
instead of
rs(<FieldName>)
0
 
sforcierAuthor Commented:
Thanks for the quick response. Unfortunately that does not resolve the issue.
0
 
Anthony PerkinsCommented:
And you don't have On Error Resume Next, right?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sforcierAuthor Commented:
Correct, there is no "On Error Resume Next".
0
 
Anthony PerkinsCommented:
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
 
sybeCommented:
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
 
sforcierAuthor 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.
0
 
Anthony PerkinsCommented:
And this would be using SQL Server 7, perhaps?
0
 
sforcierAuthor Commented:
SQL Server 2000
0
 
Anthony PerkinsCommented:
Than I am out of ideas.
0
 
sybeCommented:
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
 
sforcierAuthor 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?
0
 
sybeCommented:
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
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
To put it more clearly in both cases the Select statement is the same.
0
 
sybeCommented:
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
 
sforcierAuthor 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)?
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 6
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now