ASP wierdness

[justsomeguy]

If anyone likes brain teasers, read on. I've got a problem happening and I have no clue what could be the reason, so I thought I would post everything I know about it and if anyone can think of any reason at all please spit it out!The server is a Windows 2003 server running on a dual-Xeon 3.2GHz machine, and the system properties are reporting 3.81GB of RAM. The server is running IIS6, and is connecting to a remote database server running SQL Server 2000.The problem is that I am getting data from the database, but the data is not consistent with regard to whether or not it is available on the page. Here is the code to set up the connection:

set FORMS_RECORDSET = server.createobject("adodb.recordset")FORMS_RECORDSET.activeconnection = mm_dbcon_stringFORMS_RECORDSET.open("select * from content where cid = '" & fromget("cid") & "'")

Data is retrieved from the recordset immediately after that code with code that looks like this:

cid		   = fromrs("cid")title		 = fromrs("title")description   = fromrs("description")tid		   = fromrs("tid")

The fromrs function basically just gets the value from FORMS_RECORDSET.Fields.Item(name).value. Immediately after getting everything from the recordset, I can display the values like this:

Response.Write(vbcrlf & "description=" & description & vbcrlf)Response.Write(vbcrlf & "url=" & url & vbcrlf)

So, in all, the code looks like this:

set FORMS_RECORDSET = server.createobject("adodb.recordset")FORMS_RECORDSET.activeconnection = mm_dbcon_stringFORMS_RECORDSET.open("select * from content where cid = '" & fromget("cid") & "'")cid		   = fromrs("cid")title		 = fromrs("title")description   = fromrs("description")tid		   = fromrs("tid")visible	   = cb_fromrs("visible")disp_order	= fromrs("disp_order")url		   = fromrs("url")launch_params = fromrs("launch_params")disp_order	= fromrs("disp_order")open_type	 = fromrs("open_type")window_name   = fromrs("window_name")width		 = fromrs("width")height		= fromrs("height")launchcount   = fromrs("launchcount")timetocomplete= fromrs("timetocomplete")resizable	 = cb_fromrs("resizable")scrollbars	= cb_fromrs("scrollbars")menubar	   = cb_fromrs("menubar")toolbar	   = cb_fromrs("toolbar")directories   = cb_fromrs("directories")location	  = cb_fromrs("location")status		= cb_fromrs("status")instacomplete = cb_fromrs("instacomplete")Response.Write(vbcrlf & "description=" & description & vbcrlf)Response.Write(vbcrlf & "url=" & url & vbcrlf)' and others

It all seems pretty simple. When I showed up this morning, the problem was that the URL field and a few others (some text, some int) were not displaying, trying to write them out would write out an empty string. So, I added a loop to write out everything immediately after the call to the database:

set FORMS_RECORDSET = server.createobject("adodb.recordset")FORMS_RECORDSET.activeconnection = mm_dbcon_stringFORMS_RECORDSET.open("select * from content where cid = '" & fromget("cid") & "'")for each val in FORMS_RECORDSET.Fields  Response.Write(val.name & "=" & val.value & vbcrlf)next

When I added that loop, it would successfully write out all data, all fields had their correct values. However, even then, when it would assign the variables and then try writing out those variables, the previous fields that were all blank were now filled, but now the description field is blank. This is sticking point #1: how is it even possible for a loop like the one above, which simply iterates through the collection and displays everything, have any effect on anything else that happens on the page? This is also consistent and repeatable - if I remove the loop, the description field has a value, and several other fields are empty. If I add the loop again, the description field is blank and the other fields have their values.I have a workaround in place right now to at least write everything out. The solution there was to include the loop (writing into a comment), and then get the description field from the database again, and save the value. The second time I get the description, the value is correctly retrieved. This code retrieves all values:

set FORMS_RECORDSET = server.createobject("adodb.recordset")FORMS_RECORDSET.activeconnection = mm_dbcon_stringFORMS_RECORDSET.open("select * from content where cid = '" & fromget("cid") & "'")Response.Write("<!--" & vbcrlf)for each val in FORMS_RECORDSET.Fields  Response.Write(val.name & "=" & val.value & vbcrlf)nextResponse.Write("-->" & vbcrlf)cid		   = fromrs("cid")title		 = fromrs("title")description   = fromrs("description")tid		   = fromrs("tid")visible	   = cb_fromrs("visible")disp_order	= fromrs("disp_order")url		   = fromrs("url")launch_params = fromrs("launch_params")disp_order	= fromrs("disp_order")open_type	 = fromrs("open_type")window_name   = fromrs("window_name")width		 = fromrs("width")height		= fromrs("height")launchcount   = fromrs("launchcount")timetocomplete= fromrs("timetocomplete")resizable	 = cb_fromrs("resizable")scrollbars	= cb_fromrs("scrollbars")menubar	   = cb_fromrs("menubar")toolbar	   = cb_fromrs("toolbar")directories   = cb_fromrs("directories")location	  = cb_fromrs("location")status		= cb_fromrs("status")instacomplete = cb_fromrs("instacomplete")FORMS_RECORDSET.close()FORMS_RECORDSET.open("select description from content where cid = '" & fromget("cid") & "'")description = FORMS_RECORDSET.Fields.Item("description").Value

After that code runs, all of the variables have the correct values. Removing the last part makes description empty (even though the description is still being assigned with the other variables), and removing the loop on top makes several other fields empty.So, to review:

• Initially, several values were not able to be read from the database recordset
• After adding the loop, those values were now present, but another value disappeared
• The same behavior occurs regardless of if the fromrs function is used, or FORMS_RECORDSET.Fields.Item... is used
• The behavior occurs on several pages, not just limited to a single page

So, now that people may have their theories what may be happening, let me destroy those. First, we have this code running on many many other servers in various environments, and it has been running for years and years. Not a single other person has reported issues like this. Because of that, I have to assume that the ASP code is not the problem. If it were, we would be seeing this in at least one other place.Second, the client who is running this is not running it on one server, they have two servers, production and live. Two separate web servers connecting to two separate database servers are showing the exact same behavior. Because of that, I have to assume that it is not a hardware problem, such as bad memory, or only one of the servers would be affected.The web server I was testing on had minimal software installed. Windows appeared to be fully updated, and apart from IIS running about the only other things on the web server were several HP utilities that probably came with the server, a virus scanner, and a utility for SQL Server called Networker that is used for SQL Server backup. I'm not sure why that was installed on the web server and not the database server, but I do know that it can do network backups, so it might just be there for redundancy. I also saw these entries in add/remove programs, in addition to all the Windows updates:KB887606 - Hotfix for MSXML 2 & 4KB912944KB928388KB929120 The three above are labelled hotfixes for Windows Server 2003.ASP.NET ValidatePath moduleMDAC update KB870669KB925672 - MSXML 4.0 SP2KB972978 - MSXML 4.0 SP2I also wrote down about 100 codes for other Windows updates and security fixes. If anyone wants to ask if a specific KB# was installed, I can tell you. But these servers are located in a government contractor's building, so I don't have direct access to any of them.If anyone has any clue, even if you think it's probably wrong, please let me know. If you have other questions or need clarification on anything, hopefully I can answer.

[Yahweh]

I've had strange experiences with ASP as well: in one case, the code was perfectly fine, but it wouldn't retrive values from the database. I fixed the problem by saving the data into a new file, and mysteriously the identical code would work just fine.For your example, I don't see anything out of the ordinary with the code, unless you've misspelled a variable somewhere (I would imagine that you are like me, and use <% Option Explicit %> at the top of every page).

This is sticking point #1: how is it even possible for a loop like the one above, which simply iterates through the collection and displays everything, have any effect on anything else that happens on the page?

I think the obvious difference between the loop and your code is scope: the loop uses the recordset in global scope, and the variables are assigned from functions which make a reference to your recordset (I assume fromrs and cb_fromrs are functions). You can check if that's the case by assigning your variables directly from your recordset:

FORMS_RECORDSET.open("select * from content where cid = '" & fromget("cid") & "'")cid		   = FORMS_RECORDSET("cid")	 'don't need to use fully qualified rs.fields(something).valuetitle		 = FORMS_RECORDSET("title")description   = FORMS_RECORDSET("description")tid		   = FORMS_RECORDSET("tid")'...

That's just my guess, but if that works correctly, then maybe the error in your code is a variable scope issue.

[justsomeguy]

I think the obvious difference between the loop and your code is scope: the loop uses the recordset in global scope, and the variables are assigned from functions which make a reference to your recordset (I assume fromrs and cb_fromrs are functions). You can check if that's the case by assigning your variables directly from your recordset:

FORMS_RECORDSET.open("select * from content where cid = '" & fromget("cid") & "'")cid		   = FORMS_RECORDSET("cid")	 'don't need to use fully qualified rs.fields(something).valuetitle		 = FORMS_RECORDSET("title")description   = FORMS_RECORDSET("description")tid		   = FORMS_RECORDSET("tid")'...

That's just my guess, but if that works correctly, then maybe the error in your code is a variable scope issue.

I did try that, it did exactly the same thing. This code works fine in every other situation we've used it in, so I have to assume that the code doesn't have any problems with it. That may not be a good assumption, but if the code had problems then we should be seeing errant behavior in other places as well, but we aren't. Not only that, but some of the values are retrieved, and some are not. If scope were the issue, then it would be all or none.

[jesh]

I like me some puzzles and brain teasers so I thought I'd give this one a try. I found this post that might help:http://groups.google.com/group/microsoft.p...9641f04834ad4bbThe poster suggests that adding SET ROWCOUNT 0 before the query helps prevent inconsistent recordsets. I'll keep poking around unless/until you post a solution.EDIT: I found this knowledgebase article as well. It talks about the MDAC and using Text or Blob for the data types in SQL Server. Is your "description" field a Text or Blob? Here's the link:http://support.microsoft.com/default.aspx/kb/175239

[justsomeguy]

Hmm.. the rowcount is not an issue, because I'm only selecting 1 row from the database. Some specific fields in the row are being left blank, not the entire row. I would suspect the rowcount could be the issue if I were selecting all rows that match and I keep getting back a lower number then how many there actually were.The other article is a little different. Off the bat I would say that's not the issue because we aren't getting any error messages from OLEDB. The description field is a text field though, and in the table structure it is the 4th field out of maybe 20 or 25. But the SQL statement says select *, so I assume that the description field is being placed fourth in the recordset. Maybe specifying that it goes last would make it show up, but then it would be out of order with the table definition. I might have to look into this one a little more, the problem this article talks about might just be rediculous enough to explain the rediculous symptoms. I might try changing the order of the columns and see if that makes any difference at all.Once again, that article leaves me amazed at how things get done around Microsoft.

When dealing with BLOB fields from Microsoft SQL Server, you must put them to the right of non-BLOB columns in the resultset. To be safe, you should also read the columns in left-to-right order, so if you have two BLOB columns as the last two columns in your resultset, read the first one and then the second. Do not read them in the reverse order.

It is rediculous that column order should matter to the programmer. This detail should be taken care of by the DBMS and the driver, it should be considered a bug if the programmer is the one who is left to deal with this. The programmer might not even know the schema of the database he's working with.

This behavior is by design.

Yeah, right, Microsoft, like ###### it is. Maybe what you mean is "This behavior is the result of a bad decision early in the design process".

However, it does not occur when using Mdac 2.1sp2 or later with the 3.7 driver or later for SQL Server.

Oh, really? So if this behavior is by design, then why would something need to fix it?Thanks for the reply though, that helps out.