"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 & "<br>"
> 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
>
>
> <db_string.inc>
> Session("DSN") = "DSN=Healthpoint50;Description=TWI;"'UID=guest;PWD="
>
> <menu.asp>
> 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 & "<BR>"
> 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 "<BR>numSections = " & cRS("Number_Sections") & "<BR>"
> 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
>