Re: Data retrieving Problem of SQL Server 7

• Previous message: Bob Barrows [MVP]: "Re: Is this join valid?"
• In reply to: Wilton Yuan: "Data retrieving Problem of SQL Server 7"
• Next in thread: Wilton Yuan: "Re: Data retrieving Problem of SQL Server 7"
• Reply: Wilton Yuan: "Re: Data retrieving Problem of SQL Server 7"
• Messages sorted by: [ date ] [ thread ]

Date: Sat, 5 Mar 2005 08:23:28 -0500

Wilton Yuan wrote:
> Hello,
>
> I developed ASP web application for small company. The company only
> has one computer acting as Server. Windows 2000 Server and SQL Server
> 7 are installed in the same computer,also it act as a web server.
> Server's CPU speed is 600mHz and RAM is 1GB. We are using ADSL
> connection.
>
> The symptom of this problem is that all ASP pages cannot retrieve
> full data. This problem only randomly happens few times per day and
> every time it lasts few minutes . The code of one ASP page is
>
> <%
>
> 'Connect the Database
> Set Conn=Server.CreateObject("ADODB.Connection")
> Conn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User
> ID=User;Password=passowrd;Initial Catalog=databasename;Data
> Source=server"
>
> 'SQL Command
> SQL="select Branch, Branchid from Branch order by Branchid"
> 'run SQL Command
> Set RS=Conn.Execute(SQL)
>
>
> 'Appear result
>
> Response.write "<table Align=center border=1 cellpadding=0
> cellspacing=0 width=700>"
> Response.write "<tr >"
> Response.write "<td colspan=2><B>" & Ucase(rs(0).Name) & "</B></td>"
> Response.write "<td colspan=2><B>" & Ucase(rs(1).Name) & "</B></td>"
> Response.write "</tr>"
>
> Do While Not rs.EOF
> Response.write "<tr>"
> Response.write "<td colspan=2><b>" & rs(0).Value &"</B></td>"
> Response.write "<td colspan=2><b>" & rs(1).Value &"</B></td>
>
> rs.MoveNext
> Response.write "</tr>"

Maybe the answer to your issue is as simple as putting this response-write
statement before the movenext statement ... But see below.

> Loop
> Response.write "</table>"
>
> set rs = nothing
> Set Conn = nothing
> %>
>
> It is supposed to show 28 branches, however, the result only shows
> the top 13 branches(Before I updated MDAC to latest version, it only
> shows the first record). At this moment, all pages that have "Select
> columnName from table" query will show half results. The
> response.Buffer is set true.

If you look at the page source (View | Source in the browser) when this
result appears, do you see the closing tags for the table and tr elements?
This would indicate that your loop is ending abnormally.

>
> Some of page I didn't add "Set rs = nothing" to close recordset. Is
> it the problem?
>
It could be, but I doubt it. Other symptoms usually result from this mistake
(memory leaks forcing reboots in extreme cases)

Does your page have "On Error Resume Next" in it? If so, you may not be
seeing error messages that could help identify your problem.

Looking at your code, I see no need for the expensive recordset loop you've
chosen to use. Here is How I would have written it:

'open the recordset as above - nothing wrong there.
'Then (why use "colspan=2"?):

Response.write "<table Align=center border=1 cellpadding=0 " & _
"cellspacing=0 width=700>"
Response.write "<tr >"
Response.write "<td colspan=2><B>" & Ucase(rs(0).Name) & "</B></td>"
Response.write "<td colspan=2><B>" & Ucase(rs(1).Name) & "</B></td>"
Response.write "</tr><tr>"
dim sHTML
if not rs.eof then
     sHTML="<td colspan=2><B>" & _
                    rs.GetString(2,,"</td><td colspan=2><B>", _
                    "</td></tr><tr><td colspan=2><B>" )
else
    sHTML = "<td colspan=4>No records were returned</td></tr>"
end if
rs.close: set rs=nothing
conn.close: set conn=nothing

If right(sHTML,3) = "<B>" Then
    sHTML = Left(sHTML,len(sHTML) - 21)
End if
Response.Write sHTML & "</table>"

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" 

• Previous message: Bob Barrows [MVP]: "Re: Is this join valid?"
• In reply to: Wilton Yuan: "Data retrieving Problem of SQL Server 7"
• Next in thread: Wilton Yuan: "Re: Data retrieving Problem of SQL Server 7"
• Reply: Wilton Yuan: "Re: Data retrieving Problem of SQL Server 7"
• Messages sorted by: [ date ] [ thread ]