[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

ADO Recordset must read integer fields before bit fields?

Posted on 2004-04-12
17
Medium Priority
?
322 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
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 informatio…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

656 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