Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ADO Recordset must read integer fields before bit fields?

Posted on 2004-04-12
17
Medium Priority
?
323 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
ID: 10807618
Have you tried using:
rs.Fields(<FieldName>).value
instead of
rs(<FieldName>)
0
 
LVL 6

Author Comment

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

Expert Comment

by:Anthony Perkins
ID: 10808075
And you don't have On Error Resume Next, right?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 6

Author Comment

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

Expert Comment

by:Anthony Perkins
ID: 10808635
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
ID: 10812485
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
ID: 10812795
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
ID: 10812950
And this would be using SQL Server 7, perhaps?
0
 
LVL 6

Author Comment

by:sforcier
ID: 10813859
SQL Server 2000
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10814248
Than I am out of ideas.
0
 
LVL 28

Expert Comment

by:sybe
ID: 10814479
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
ID: 10814607
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 200 total points
ID: 10814705
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
ID: 10814713
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
ID: 10814720
To put it more clearly in both cases the Select statement is the same.
0
 
LVL 28

Expert Comment

by:sybe
ID: 10814761
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
ID: 10815330
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Screencast - Getting to Know the Pipeline

810 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