Odd ADO.Resultset Buffer Problem?

I am working with code I did not write for a web app that used to run
on an Access database. Due to performance problems I'm trying to move
the app to a different server running SQL Server 2000 SP4 and IIS 6
running on Server 2003 with MDAC 2.8 SP2.

On the new server, one of the queries is failing in an odd way. This is
old fashioned ASP code using ADO.Recordsets. (Code is below.) One of
the columns I should be getting from the query is called
"Number_Sections". However, cRS("Number_Sections") is returning NULL
even though the column in the database is not null. Other columns such
as "Class_Name" are being retrieved correctly. If I run the query using
the SQL Analyer on the server itself, "Number_Sections" is indeed
populated as I expect.

I tried cRS.Fields("Number_Sections") and
cRS.Fields.Item("Number_Sections"), but they also return NULL. The
only think I haven't done is print out each column to see if I can tell
where the column data stops. However, many of the columns are in fact
NULL, so that would probably end up being inconclusive anyway.

However, while trying to debug I threw in this code just to see what
the Recordset object had in it.

For zz = 0 to cRS.Fields.Count-1
response.write cRS.Fields.Item(zz).Name & " = " &
cRS.Fields.Item(zz).value & "
"
Next

After running through this loop, which prints out all 47 column names
and values *correctly*, cRS("Number_Sections") suddenly works. Take out
the loop and it stops working again.

This sounds like some kind of buffering problem to me, but I'm not
familiar enough with Microsoft products to know where I should be
looking for some setting to fix the problem. Is it ASP and IIS? Is it
SQL Server? This code continues to work on the old server, so I'm
inclinded to believe it's a configuration problem. I haven't been able
to find any settings that look like they address this issue and
searching the MS Knowledge base hasn't turned up anything helpful
either.

Has anyone else seen this behavior? Can anyone point me in the right
direction to find a more graceful solution that leaving the for loop in
and setting the string to some dummy variable instead of sending it to
the response object?

I asked the folks over in the sqlserver group but I was told to ask
over here. Also, let me reiterate, this is not my code. Criticisms of
the code design are not helpful. I have plenty of my own. If I had time
to rewrite the whole app (which is what it would take) I would.

Thanks.

-Sean



Session("DSN") = "DSN=Healthpoint50;Description=TWI;"'UID=guest;PWD="


Set Conn = Server.createobject("ADODB.Connection")
Conn.open session("DSN")

cSQL = "select * from Classes,Course_Definition where
Course_Definition.Course_Def_Number = Classes.Course_Number" & " AND
Classes.Class_Number=" & Session("ClassNumber")
Set cRS = Conn.Execute(cSQL)

'this is the kludge loop
For zz = 0 to cRS.Fields.Count-1
dummy = cRS.Fields.Item(zz).Name & " = " &
cRS.Fields.Item(zz).Value & "
"
Next

Session("Course_Def_Number")=cRS("Course_Number")
if Session("Course_Title")= "" then
Session("Course_Title")=cRS("Class_Name")
else
Session("Course_Title")=cRS("Class_Name")
end if

Session("numberGuideSections") = cRS("Number_Guide_Sections")
Session("thisClassNews")= cRS("Discussion_Group")
Session("Class_Start_Date")= cRS("Class_Start_Date")
Session("Syllabus_Address")=cRS("Syllabus_Address")
Session("Instructor_Email")=cRS("Instructor_Email")
Session("CourseGuideFile") = cRS("Course_Guide_File")
Session("Eval_Number") = cRS("Eval_Number")

'This is the offending column
'Response.write "
numSections = " & cRS("Number_Sections") & "
"
Session("numSections")=cRS("Number_Sections")
Session("Course_Library") = cRS("Course_Library")
Session("numAssignments")=cRS("Number_Assignments")
Session("numTests")=cRS("Number_Tests")
if cRS("course_completion_page") & "" <> "" Then
Session("course_completion_page") = "custom/certificates/" &
cRS("course_completion_page")
else
Session("course_completion_page") = ""
end if
Session("display_pre_page")=cRS("Section_Message_Page")

If (Session("Instructor_Email") & "") = "" Then
Session("Instructor_YN") ="No"
Else
Session("Instructor_YN") ="Yes"
End If

cRS.Close

RE: Odd ADO.Resultset Buffer Problem?

Sena,

First, make sure you are running the latest versions of MDAC. There are
some known issues with older versions of MDAC. Since you are running IIS 6.0
chances are you are current. Just double check to make sure.

Then, it is very bad form to use SELECT * in production code. If you
actually need all the fields in a table or query, list them all in your
SELECT Statement. Or, better yet, if you are converting to SQL, wrap the
whole thing in a stored procedure and just call the stored procedure from
your ASP page. You might be surprised at the performance improvement.

HAve you verified there are no conflicts in column names between your two
tables -- it looks like you are doing a join. In this case, I would do a
specifc join instead of the implied join.

YOUR CODE:
cSQL = "select * from Classes,Course_Definition where
Course_Definition.Course_Def_Number = Classes.Course_Number" & " AND
Classes.Class_Number=" & Session("ClassNumber")

CHANGE TO:
cSQL = "select * from Classes LEFT JOIN Course_Definition ON
Course_Definition.Course_Def_Number = Classes.Course_Number" &
" WHERE Classes.Class_Number=" & Session("ClassNumber")


HTH,
John






"Sean H." wrote:

> I am working with code I did not write for a web app that used to run
> on an Access database. Due to performance problems I'm trying to move
> the app to a different server running SQL Server 2000 SP4 and IIS 6
> running on Server 2003 with MDAC 2.8 SP2.
>
> On the new server, one of the queries is failing in an odd way. This is
> old fashioned ASP code using ADO.Recordsets. (Code is below.) One of
> the columns I should be getting from the query is called
> "Number_Sections". However, cRS("Number_Sections") is returning NULL
> even though the column in the database is not null. Other columns such
> as "Class_Name" are being retrieved correctly. If I run the query using
> the SQL Analyer on the server itself, "Number_Sections" is indeed
> populated as I expect.
>
> I tried cRS.Fields("Number_Sections") and
> cRS.Fields.Item("Number_Sections"), but they also return NULL. The
> only think I haven't done is print out each column to see if I can tell
> where the column data stops. However, many of the columns are in fact
> NULL, so that would probably end up being inconclusive anyway.
>
> However, while trying to debug I threw in this code just to see what
> the Recordset object had in it.
>
> For zz = 0 to cRS.Fields.Count-1
> response.write cRS.Fields.Item(zz).Name & " = " &
> cRS.Fields.Item(zz).value & "
"
> Next
>
> After running through this loop, which prints out all 47 column names
> and values *correctly*, cRS("Number_Sections") suddenly works. Take out
> the loop and it stops working again.
>
> This sounds like some kind of buffering problem to me, but I'm not
> familiar enough with Microsoft products to know where I should be
> looking for some setting to fix the problem. Is it ASP and IIS? Is it
> SQL Server? This code continues to work on the old server, so I'm
> inclinded to believe it's a configuration problem. I haven't been able
> to find any settings that look like they address this issue and
> searching the MS Knowledge base hasn't turned up anything helpful
> either.
>
> Has anyone else seen this behavior? Can anyone point me in the right
> direction to find a more graceful solution that leaving the for loop in
> and setting the string to some dummy variable instead of sending it to
> the response object?
>
> I asked the folks over in the sqlserver group but I was told to ask
> over here. Also, let me reiterate, this is not my code. Criticisms of
> the code design are not helpful. I have plenty of my own. If I had time
> to rewrite the whole app (which is what it would take) I would.
>
> Thanks.
>
> -Sean
>
>
>
> Session("DSN") = "DSN=Healthpoint50;Description=TWI;"'UID=guest;PWD="
>
>
> Set Conn = Server.createobject("ADODB.Connection")
> Conn.open session("DSN")
>
> cSQL = "select * from Classes,Course_Definition where
> Course_Definition.Course_Def_Number = Classes.Course_Number" & " AND
> Classes.Class_Number=" & Session("ClassNumber")
> Set cRS = Conn.Execute(cSQL)
>
> 'this is the kludge loop
> For zz = 0 to cRS.Fields.Count-1
> dummy = cRS.Fields.Item(zz).Name & " = " &
> cRS.Fields.Item(zz).Value & "
"
> Next
>
> Session("Course_Def_Number")=cRS("Course_Number")
> if Session("Course_Title")= "" then
> Session("Course_Title")=cRS("Class_Name")
> else
> Session("Course_Title")=cRS("Class_Name")
> end if
>
> Session("numberGuideSections") = cRS("Number_Guide_Sections")
> Session("thisClassNews")= cRS("Discussion_Group")
> Session("Class_Start_Date")= cRS("Class_Start_Date")
> Session("Syllabus_Address")=cRS("Syllabus_Address")
> Session("Instructor_Email")=cRS("Instructor_Email")
> Session("CourseGuideFile") = cRS("Course_Guide_File")
> Session("Eval_Number") = cRS("Eval_Number")
>
> 'This is the offending column
> 'Response.write "
numSections = " & cRS("Number_Sections") & "
"
> Session("numSections")=cRS("Number_Sections")
> Session("Course_Library") = cRS("Course_Library")
> Session("numAssignments")=cRS("Number_Assignments")
> Session("numTests")=cRS("Number_Tests")
> if cRS("course_completion_page") & "" <> "" Then
> Session("course_completion_page") = "custom/certificates/" &
> cRS("course_completion_page")
> else
> Session("course_completion_page") = ""
> end if
> Session("display_pre_page")=cRS("Section_Message_Page")
>
> If (Session("Instructor_Email") & "") = "" Then
> Session("Instructor_YN") ="No"
> Else
> Session("Instructor_YN") ="Yes"
> End If
>
> cRS.Close
>
>

Re: Odd ADO.Resultset Buffer Problem?

Sean H. wrote:
>>
>
>
> Session("DSN") = "DSN=Healthpoint50;Description=TWI;"'UID=guest;PWD="
>

I suspect you have a Text column in your query's SELECT list. There is an
old bug associated with using ODBC to retrieve data that includes Text data.
See:
http://www.aspfaq.com/show.asp?id=2188

The best solution is to stop using ODBC and switch to using the native SQL
Server OLE DB provider. See:
http://www.aspfaq.com/show.asp?id=2126

If this does not apply to your problem, you will need to provide more
details about the table structure.

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Odd ADO.Resultset Buffer Problem?

Bob,

Thanks for jumping in. I recall discussions about the issues with text
columns, but I thought that was due to a bug in MDAC, not ODBC. Thanks for
the clarification.





"Bob Barrows [MVP]" wrote:

> Sean H. wrote:
> >>
> >
> >
> > Session("DSN") = "DSN=Healthpoint50;Description=TWI;"'UID=guest;PWD="
> >
>
> I suspect you have a Text column in your query's SELECT list. There is an
> old bug associated with using ODBC to retrieve data that includes Text data.
> See:
> http://www.aspfaq.com/show.asp?id=2188
>
> The best solution is to stop using ODBC and switch to using the native SQL
> Server OLE DB provider. See:
> http://www.aspfaq.com/show.asp?id=2126
>
> If this does not apply to your problem, you will need to provide more
> details about the table structure.
>
> HTH,
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>