December 10, 2003 at 1:48 am
Hello,
When I search data from one table using ASP, sometime I only can retrieve first record. Do anybody know if the problem is from ASP coding or SQL Server?
Thanks in advance
December 10, 2003 at 2:20 am
How many rows should your search return?
Can you provide more information?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 10, 2003 at 6:46 am
Yes, this is hard to say without some sort of code sample as the problem could occur at both places. It will be a coding issue most likely, though.
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
December 11, 2003 at 7:05 am
Check the sql for "select top 1 ..."
Inner joins can do some interesting things to data sets if not properly applied. You may have an inner join where what you really needed was an outer join.
Also check the underlying tables to verify there should be more than one row returning.
It would definately help if you could post the code. Without the code as reference, I'm just speculating as to what the problem is.
On the ADO side, if you are not looping through the returned recordset, then you will only get the first record.
IE
Dim obj as Recordset
...
obj("field1")
...
This would return just the first records field1 value where as:
...
While not obj.EOF
obj("field1")
obj.MoveNext() 'Edited because I forgot to add it the first time. =)
LOOP
...
will return the value of field1 for each record.
Edited by - cliffb on 12/11/2003 09:19:38 AM
December 11, 2003 at 2:14 pm
The best approach is to test the SQL code out separately from the ASP code to get a feel for the number of records you should see.
My current employment position is in web application development and after completing several projects, I have gotten into the regular practice of testing my SQL code thouroughly before starting any work on the ASP, Coldfusion, PHP or Java code that will present the data.
If I don't know what the data will look like, then I will likely spend more time tweaking the presentation code than I want because of possible unexpected data that I may not be aware of or knowledgeable about. Also, it is important to make sure the SQL code actually works and provides accurate results.
And yes, I do this even on the simplest of queries. I like to think of the practice akin to the carpenters rule of "Measure twice, cut once". Well.. it is in a similar vein anyway.
-Al
Funny thing about people and their computers...
The computer is completing millions of processes every second, but the user still thinks it is too slow.
"I will not be taken alive!" - S. Hussein
December 21, 2003 at 2:06 am
Thank you all for your replying.
Here is the sample code:
<%
'Connect the Database
Set Conn=Server.CreateObject("ADODB.Connection")
Conn.Open "DSN=Test;UID=sa;PWD=;database=Northwind"
'SQL Command
SQL="Select CategoryName, CategoryID from Categories order by CategoryID"
'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>"
Loop
Response.write "</table>"
Set Conn = nothing
%>
when the result only shows one row, I click the refresh button, it will show all rows. After that I click refresh again, it shows one row again.
This problem started after we starting to use a new server. Before that, the same code never has problem.
Wilton
December 21, 2003 at 5:07 pm
The SQL Query itself shouldn't cause this problem, nor technically should the ASP code you've given. Have you tried to turn buffering on to see if that makes a difference? (Response.Buffer = True in the ASP code)
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply