Data retrieving Problem of SQL Server 7
am 05.03.2005 02:37:26 von Wilton Yuan
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 "
width=700>"
Response.write ""
Response.write "" & Ucase(rs(0).Name) & " | "
Response.write "" & Ucase(rs(1).Name) & " | "
Response.write "
"
Do While Not rs.EOF
Response.write ""
Response.write "" & rs(0).Value &" | "
Response.write "" & rs(1).Value &" |
rs.MoveNext
Response.write "
"
Loop
Response.write ""
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.
Some of page I didn't add "Set rs = nothing" to close recordset. Is it the
problem?
Where is the problem from? Code or Bandwidth of Internet connection or
Server configuration or SQL Server 7?
Thanks in advance,
Wilton
Re: Data retrieving Problem of SQL Server 7
am 05.03.2005 14:23:28 von reb01501
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 "
> cellspacing=0 width=700>"
> Response.write ""
> Response.write "" & Ucase(rs(0).Name) & " | "
> Response.write "" & Ucase(rs(1).Name) & " | "
> Response.write "
"
>
> Do While Not rs.EOF
> Response.write ""
> Response.write "" & rs(0).Value &" | "
> Response.write "" & rs(1).Value &" |
>
> rs.MoveNext
> Response.write "
"
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 ""
>
> 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 "
"cellspacing=0 width=700>"
Response.write ""
Response.write "" & Ucase(rs(0).Name) & " | "
Response.write "" & Ucase(rs(1).Name) & " | "
Response.write "
"
dim sHTML
if not rs.eof then
sHTML="" & _
rs.GetString(2,," | ", _
" |
" )
else
sHTML = " | No records were returned |
"
end if
rs.close: set rs=nothing
conn.close: set conn=nothing
If right(sHTML,3) = "" Then
sHTML = Left(sHTML,len(sHTML) - 21)
End if
Response.Write sHTML & ""
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"
Re: Data retrieving Problem of SQL Server 7
am 08.03.2005 08:22:18 von Wilton Yuan
Bob Barrows,
Thank you very much.
The page does not have "On Error Resume Next" on it. When the problem
happens, I check its source code, all closing tags are fine.
Wilton
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Re: Data retrieving Problem of SQL Server 7
am 08.03.2005 12:18:58 von reb01501
Please quote some of the text from the message to which you are replying.
Wilton Yuan wrote:
> Bob Barrows,
>
> Thank you very much.
>
> The page does not have "On Error Resume Next" on it. When the problem
> happens, I check its source code, all closing tags are fine.
>
> Wilton
>
Have you tried my alternative code suggestion? If so, did you get the same
symptoms?
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"