Mysterious disappearing records

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)

Mysterious disappearing records

Author

Topic

mzwebbie
Starting Member

15 Posts

Posted - 2003-06-16 : 16:52:15

Hi,
I'm really really stumped on a tricky little ADO question. I have an ASP page that is supposed to select certain records from the database and display them on the page. I know there are 3 records in the database that match, but only 1 displays. I've tried response.write-ing the sql, then pasting it into Query Analyzer and it returns those 3 rows, every time. But within the ASP if I do a Rs.recordcount it shows 1 and only displays the one row. HELP! I've tried everything I could find but no luck. I'm pretty sure its not my looping code,because recordcount only returns 1 also. (not -1, 1). Any wild guesses on what might cause this?

ANyone have a clue? I sure don't.

Mary

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-16 : 21:05:46

Without posting your ASP/ADO and any stored procedure that might be used, any response would be a WAG !

Sam

Edited by - SamC on 06/16/2003 21:06:05

mzwebbie
Starting Member

15 Posts

Posted - 2003-06-17 : 09:05:12

This is the line calling the function:
Call GetDocument(objClient, "D")

Here is the beginning of get document:

Sub GetDocument(objClient, strDocType)

strSQL = ""
strSQL = strSQL & "Select description, fileName From WEB_DB.dbo.tblclientDocument Where clientID = " & objClient(c_WEBID) & " "
strSQL = strSQL & "and documentType = '" & strDocType & "' And documentActive = 1 AND (ONorPN = '1' OR ONorPN = '2') Order By documentOrder"

Set RS = GetRS(strSQL)

Temp = RS.RecordCount
Response.WRite "<br><font color=white>" & Temp &"</font>"

Temp above shows up as 1.
And here is GetRS

Function GetRS(strSQL)

Set conCli=Server.CreateObject("ADODB.Connection")
conCli.Open objDatabase
Set GetRS = server.createobject("ADODB.Recordset")
Set GetRS.ActiveConnection = conCli

GetRS.CursorLocation=3
GetRS.CursorType=3
GetRS.open strSQL
Set GetRS.ActiveConnection = Nothing

End Function

It appears to be connecting fine, since I am getting a real record, its just not getting *all* the records. I'm dealing also with a lot of code that was here before I came, and GetRS (and doing it this way) is one of those pieces. I just added a few new documents to the database, but they don't always show up. Another thought...could any characters in the description field break my recordset somehow..or should it be fine since its a text field? Hope that helps...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-17 : 09:51:34

You gotta loop through the rows using the movenext method of the recordset.

i.e., something like:

r.movefirst
do while not r.eof
response.write r("FieldName") & "<BR>"
r.movenext
loop
r.close

depending on what type of recordset you open, you may not get an accurate record count until you have moved to the last record.

- Jeff

mzwebbie
Starting Member

15 Posts

Posted - 2003-06-17 : 09:58:23

Shouldn't rs.recordcount work no matter where you are? I am looping through the rows later in the code. It was just a very long loops so I didn't want to paste it..

While Not RS.EOF And Not RS.BOF
'...........lots of stuff that writes out the links............

RS.MoveNext
Wend

----------
But sense then I discovered another big clue. I created an interdev project locally, copied those files to mylocal machine, and kept the connection going to the same sql server. So now I've got a copy (didn't change the code at all) running locally from my IIS..same SQL server. And guess what....locally, I get all three files every time!! WOrks perfectly. Weird huh?

So I need a guru..what could cause it to work fine running locally, and bomb running live..with the same database? Different ADO versions? Some weird caching thing or a patch I need on IIS on the live server?? If it was a weird cache thing wouldn't it have gone away by now...those new files were added last Friday. Also, I already have my IE set up to check for new files every time (some of the users don't) ..and I still get mysterious disappearing links/records problem when I view the site running from the live server.

Help! I'm in over my head on this part.

mzwebbie
Starting Member

15 Posts

Posted - 2003-06-24 : 15:15:17

In case anyone is still reading the older stuff..I'm still very very stuck on this problem. I finally hard coded the names of the files I'm trying to display in my broken loop. Definately not a good long term solution and I still need a fix. I've looked everywhere, posted on multiple lists, but no answers. I would really really appreciate any help anyone has

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-25 : 03:19:28

Try using the getrows method instead of fetching the rows one by one. You can then get the count from ubound (yourarray,2).

noresults = false
rs.open (etc..)
if not rs.eof
yourarray = rs.getrows()
rs.close
else
noresults = true
end if
set rs= nothing

if noresults = false then
'.. process your results
for yourloop = 0 to ubound (yourarray,2)
'.. for each result
next
else
response.write ("nothing returned")
end if

...

-------
Moo.

nickjones
Starting Member

16 Posts

Posted - 2003-06-25 : 10:02:37

Try switching your cursortype to 1.

/stab-in-the-dark

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-06-25 : 22:50:01

Changing the cursortype doesn't matter. Besides, cursortype of 1 (Keyset) is not available for cursorlocation of 3 (Client). The only cursortype available for client is Static (3). And client cursors always have accurate recordcounts.

Also, this doesn't seem like a caching problem. Not if you restarted the machine since you got the problem which would flush the memory taken by the Client Cursor Engine for the local recordset. Have you ever restarted the live server since you noticed this problem?

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources