last record doesn't display

[middletree]

http://www.middletree.net/debug/DisplaySortableTickets.html

Doing an Intranet-based app in ASP. I put the view source version (that is,
no ASP code) out on my personal site at the above URL to show you what I am
looking at. Style sheet isn't there, but shouldn't matter in this case.

My code says to show some things from the recordset based on certain
criteria selected from the search page. In this scase, the search page is
actually this same page, and you just click the checkbox to show or hide it.
It's a loop, and this page shows everything just fine, except once it gets
to a certain point, it won't show any more. In other words, you can scroll
down and see the info on Ticket number 15129, but then it doesn't show other
records which came up in the query, and should be displayed. It's like it
just gave up.

But what's wierd is that the other records, the ones now being displayed,
are in the source code.

I have a link at the top which allows you to get my actual ASP code. It's
zipped. One ASP file is an include inside the other one.

I imagine I have something wrong in my loop, but I have been looking at this
all day and can't find anything. Any help would be appreciated.

 

[Aaron Bertrand - MVP]

I see do while not rs.eof but no rs.movenext / loop.

 

[Aaron Bertrand - MVP]

A few suggestions.

(1) why are you using "strFullTIMESTMP" as a primary key? You realize it's
possible, due to the precision of datetime data types, to have multiple

(2) why are you using a bunch of nested recordsets to achieve what a single
inner join should be able to do?

(3) why do you constantly set rs = createobject("ADODB.Recordset") but never
destroy any of them?

(4) why are you allowing values from request.querystring into your SQL
statements unchecked? Have you tried something like...

DisplaySortableTickets.asp?strStatus=a';DELETE%20TKT_STATUS;SELECT%20'b

?

(5) why are you using ADODB.Recordset at all? These all seem to be
forward-only, static recordsets. Also, what's with SELECT * to retrieve one
column?

Here is a rewrite of the first portion. I'm still trying to figure out what
all your joins are doing in the bottom portion, and then why you need to go
out multiple more times to the same tables...


<!-- #INCLUDE FILE="includes/functions.asp" -->
<!-- #INCLUDE FILE="includes/argodbinc.asp" -->
<!-- #INCLUDE FILE="includes/colors.inc" -->

<%
function fixVal(s)
s = replace(request.QueryString(s), "'", "''"))
end function

strSort = fixVal("Sort")
strDate1 = fixVal("Date1")
strDate2 = fixVal("Date2")
strTSE = fixVal("selectTSE")
strStatus = fixVal("Status")
strCustomerCode = fixVal("CustomerCode")

If strSort = "" then
strSort = "Orig_TimeStamp"
strSortName = "Original Open Date"
Else
strSortName = "Customer"
If strSort = "AssignedEmployee" then strSortName = "Assigned Employee"
End if

If strStatus = "" then
strStatusName = "Open"
Else
strSQL = "SELECT Description FROM TKT_Status" & _
" WHERE StatusID = '" & strStatus & "'"
set rs = objConnection.Execute(strSQL)
strStatusName = rs("Description")
RS.close: set rs = nothing
End if

If strTSE = "" then
strTSEName = ""
Else
strSQL = "SELECT FirstName + LastName AS TSEName FROM Employee "
strSQL = strSQL & "WHERE EmployeeID = '" & strTSE & "'"
set rs = objConnection.Execute(strSQL)
strTSEName = rs("TSEName")
RS.close: set rs = nothing
End if

If strCustomerCode = "" then
strCustomerName = ""
Else
strSQL = "SELECT CustomerName FROM Customer "
strSQL = strSQL & "WHERE CustomerCode = '" & strCustomerCode & "'"
set rs = objConnection.Execute(strSQL)
strCustomerName = rs("CustomerName")
RS.close: set rs = nothing
End if
%>

 

[Aaron Bertrand - MVP]

Sorry, a couple of clarifications:

(1) why are you using "strFullTIMESTMP" as a primary key? You realize it's
possible, due to the precision of datetime data types, to have multiple

identical values in the same table?

function fixVal(s)
fixVal = replace(request.QueryString(s), "'", "''"))
end function

 

[middletree]

Good questions. The short answer is that I have a lot to learn. As for the
multiple queries when a more complex single query with Joins, well, I just
built this thing piece by piece, no real planning, didn't intend anyone but
me to use it, then the boss heard about it and I started doing things to
make him happy and put the thing on the Intranet for all employees to use.

I will look into all your suggestions and see what I can do. Thing is, this
is a side project, my customers take up most of my time (I work in a
Customer Care group at my company), so most of the improvement I can do to
this app is during lunch or at home (thankfully, my work machine is a
laptop).

I agree with you that if this were planned out and designed appropriately,
it would be much improved.

For the record, I don't use the timestamp field as my primary key. Can you
point me to which line of code you are referring to?

 

[Aaron Bertrand - MVP]

For the record, I don't use the timestamp field as my primary key. Can you

point me to which line of code you are referring to?

I didn't mean to say you were using is as your primary key, but you are
definitely treating it like one. The following query may return multiple,
or the wrong row(s), yet your code assumes it could only return exactly one
row:

strSQL = "SELECT InternalDesc FROM TKT_History " & _
"WHERE TIMESTMP = '" & strFullTIMESTMP &"'"

(line 549-550, in my editor)

 

[middletree]

You are correct; I need to change that. It's extremely unlikely, since only
8 people use this app, that two entries will be made at exactly the same
time, but why take the chance?

BTW, last Friday, I had a Replace issue that you were helping me on. Just
thought I'd let you know that I didn't kow that the replace function is
case-sensitive. That little revelation allowed me to make the replace thing
work.

thanks,

James W