Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  recordset data is disappearing!

Author  Topic  wengang Starting Member 4 Posts Posted - 2005-08-11 : 21:29:35 HI all. I am new to your forum. I usually post at vbforums.com, but I think I will post my sql server questions here in the future. Well, I used a stored procedure that I modified from a couple of different ones I saw by Graz(?) on this website. I got them to work with no problem. But I'm having a very odd problem now. In my ASP page, I execute the procedure to get a recordset: set rs4 = server.createobject("adodb.recordset") rs4.Open "SearchEngine " & Page & "," & RecPerPage & ", '" & tKeywords & "', " & tFuzzy & ", " & tType & ", " & JE("2","1"), ConnSS You might guess this is a serach engine. Also it returns the records in pages. Well, the odd behavior is, I have one field coming back from the query 'Text1'. As I loop thru the RS, I display this field. In some cases, the first row will show its text1 contents, and all subsequent rows will retun blank (though there are no blank text1 values). in other cases, the whole page will return blank values. In one bizarre case, I found that by typing: response.write "1. " & rs4("text1") & "<br>2. " & rs4("text1") the result was: 1. dsfdsfdsfdsfsd 2. is that not bizarre? I can't pinpoint the problem, but it is regularly-occurring. So I didn't create confusion, the behavior is in fact consistent. I didn't mean it is different one time to the next. But I use similar procedures on various pages of the site, and within each page, the behavior like this, will be similar to the other pages' odd behavior, but consistent within itself. So, if I refresh the page, I get exactly the same thing. And, this never happened before I converted my search engine to stored procedure. Well, here is the shared portion of all the procedures like the searchengine that use paged results, pulling from a temp table: DECLARE @FirstRec int, @LastRec int -- Find out the first and last record we want SELECT @FirstRec = (@Page - 1) * @RecsPerPage SELECT @LastRec = (@Page * @RecsPerPage + 1) -- Now, return paged records, plus the recordcount SELECT TI.ArticleID, TI.PublishDate, AllRecords = (SELECT COUNT(*) FROM #TempItems TI) FROM #TempItems TI WHERE ID > @FirstRec AND ID < @LastRec Well, as you can see, Text1 is not in this recordset. I have removed it to try a workaround. So, the workaround goes: 'exec storedprocedure do until rs4.eof rs5. select text1 from tblArticles where.... rs4.movenext loop so now i go thru the recordset and one by one make a call to the db for that field (just to see if it would work) and, surprise, it also returns blank. Has anybody heard of anything like this? I can run the search engine in query analyzer and it returns a perfect recordset (including text1). And as I said, i've even captured events where the rs("Text1") held value the first time it was referenced and was blank the second time. Also, I'm not using any sort of error trapping that might hide an error happening. So.... do you have any ideas? Thanks Wengang