troubleshooting Question

a doozy of an asp recordset question :/

Avatar of jpve
jpve asked on
ASPMicrosoft SQL Server 2005
5 Comments1 Solution302 ViewsLast Modified:
Ok, this is one hell of a question, so here goes:

At work, a former employee created a site - it's using an SQL Server 2005 db.  He pulled the data using a recordset, which is the way I used to access db data years ago when I used to do asp, then I switched to php for a few years, but now I'm back to asp, so I need to take over the maintenance of this site he created, which is fine enough but the problem comes up in that where he pulled out data using a recordset, my understanding is that it's only doing a query on the data and pulling all of it out; what I need to do is alter how that data is being pulled out, but I don't see an actual sql query being run anywhere, so I don't see where I can use new queries to pull out specific information.

I've tried to Google the answer for how to run queries on data that has been pulled out using a recordset but to no avail, so I've come to the experts.  Basically the data is pulled out and separated into columns with each column containing one of the columns in the db - what I need to do is make the header link of each category a link so that when you click it, it will reload the page but sort the data in that column you clicked.  I planned on making it so that when you click one of those links it will reload the page with a querystring and the page will run the sorting query based on the variable in that querystring, which is easy enough to do but I just don't know where to actually apply that new query since like I said, I just can't see a query running anywhere in the first place...is that what recordsets do - bypass the whole sql query part and just give you access to the data in the db by default?

Anyway, here's the code - sorry if it's a bit long...

----------------------------------------------------------------------------------------------------------------
<!--#include file="includes/header.asp" -->

<script language="Javascript">
      function refreshPage() {
            window.document.frmMain.submit();
      }
</script>
      
<span class="Text_PageTitle">Issue List</span>
<br/><br/>

<%
Dim cmdCommand, rstData, strShowAll
Set cmdCommand = Server.CreateObject("ADODB.Command")
cmdCommand.ActiveConnection = strConnectionString
cmdCommand.CommandType = 4
If Request.Form("chkShowAll") = "on" Then
      cmdCommand.CommandText = "uspGetAllEntries"
Else
      cmdCommand.CommandText = "uspGetAllOpenEntries"
End If

Set rstData = cmdCommand.Execute

'Write the "Add New" link
Response.Write "<table width=""100%"" border=""0"" cellpadding=""2"" cellspacing=""2"">" & vbCrLf & _
                        "<form name=""frmMain"" method=""post"" action="""">" & vbCrLf & _
                        "<tr>" & vbCrLf & _
                        "<td><input type=""checkbox"" name=""chkShowAll"" class=""CheckBox"" onclick=""refreshPage();""" & IIF(Request.Form("chkShowAll") = "on", " CHECKED", "") & ">&nbsp;<span class=""Text_Normal"">Show All</span></td>" & vbCrLf & _
                        "<td align=""right""><a href=""entry_addedit.asp?EID=0"" class=""Link_Normal"">Add New</a></td>" & vbCrLf & _
                        "</tr>" & vbCrLf & _
                        "</form>" & vbCrLf & _
                        "</table>" & vbCrLf

'Write the table header
Response.Write "<table width=""100%"" border=""0"" cellpadding=""2"" cellspacing=""2"" class=""TableBorder"">" & vbCrLf & _
                        "<tr class=""TableHeaderRow"">" & vbCrLf & _
                        "<td width=""5%""><a href='default.asp?sort=id' class='Link_DefaultHeader' title='Sort by ID'>ID</a></td>" & vbCrLf & _
                        "<td width=""10%""><a href='default.asp?sort=type' class='Link_DefaultHeader' title='Sort by Entry Type'>Entry Type</a></td>" & vbCrLf & _
                        "<td width=""10%"">Entered By</td>" & vbCrLf & _
                        "<td width=""10%"">Date Entered</td>" & vbCrLf & _
                        "<td width=""40%"">Entry</td>" & vbCrLf & _
                        "<td width=""10%""><a href='default.asp?sort=status' class='Link_DefaultHeader' title='Sort by Status'>Status</a></td>" & vbCrLf & _
                        "<td width=""5%""><a href='default.asp?sort=priority' class='Link_DefaultHeader' title='Sort by Priority'>Priority</a></td>" & vbCrLf & _
                        "<td width=""10%"">Action</td>" & vbCrLf & _
                        "</tr>" & vbCrLf

If Not (rstData.BOF And rstData.EOF) Then
      'Write the rows
      Dim strPriorityClass
      Do Until rstData.EOF
            'Determine the color to show for the Priority
            'Select Case CInt(rstData("iPriority"))
            '      Case 1: strPriorityClass = " class=""PriorityRed"""
            '      Case 2: strPriorityClass = " class=""PriorityOrange"""
            '      Case 3: strPriorityClass = " class=""PriorityYellow"""
            '      Case 4: strPriorityClass = " class=""PriorityGreen"""
            '      Case 5: strPriorityClass = " class=""PriorityBlue"""
            '      Case Else : strPriorityClass = ""
            'End Select
            
            
            Response.Write "<tr class=""TableDataRow"" valign=""top"">" & vbCrLf & _
                                    "<td>" & rstData("iEntryID") & "</td>" & vbCrLf & _
                                    "<td>" & rstData("sEntryType") & "</td>" & vbCrLf & _
                                    "<td>" & rstData("sFirstName") & " " & rstData("sLastName") & "</td>" & vbCrLf & _
                                    "<td>" & rstData("dDateTimeEntered") & "</td>" & vbCrLf & _
                                    "<td>" & rstData("sEntryText") & "</td>" & vbCrLf & _
                                    "<td>" & rstData("sStatus") & "</td>" & vbCrLf & _
                                    "<td>" & rstData("iPriority") & "</td>" & vbCrLf & _
                                    "<td>"

            If Session("User_Type") = "Admin" Then
                  Response.Write "<a href=""entry_addedit.asp?EID=" & rstData("iEntryID") & """ class=""Link_Normal"">Edit</a>&nbsp;|&nbsp;" & _
                                          "<a href=""entry_details.asp?EID=" & rstData("iEntryID") & """ class=""Link_Normal"">View</a>&nbsp;|&nbsp;" & vbCrLf & _
                                          "<a href=""entry_delete.asp?EID=" & rstData("iEntryID") & """ class=""Link_Normal"" onclick=""return confirm('Are you sure you wish to delete this Issue? \n\nThis will also remove all notes associated with the Entry.');"">Delete</a>" & vbCrLf
            Else
                  Response.Write "<a href=""entry_details.asp?EID=" & rstData("iEntryID") & """ class=""Link_Normal"">View</a>" & vbCrLf
            End If
            
            Response.Write "</td>" & vbCrLf & _
                                    "</tr>" & vbCrLf
            rstData.MoveNext
      Loop
Else
      'Write the empty row
      Response.Write "<tr class=""TableDataRow"">" & vbCrLf & _
                              "<td colspan=""8"">There are no entries available.</td>" & vbCrLf & _
                              "</tr>" & vbCrLf
End If

'Close the table
Response.Write "</table>" & vbCrLf

rstData.Close
Set rstData = Nothing
Set cmdCommand = Nothing
%>

<!--#include file="includes/footer.asp" -->
----------------------------------------------------------------------------------------------------------------

Anyway like I said, I'm baffled on this one; I'm using Dreamweaver and I did a Find for uspGetAllEntries and uspGetAllOpenEntries throughout all pages within the website, as well as searching for "select", "where", etc but the results were constrained to only this page, so I'm a loss here.  I can recreate the whole page using the current way I access db info but obviously I'd like to know if there's a way to simply query the existing recordset since I don't want to restructure the way the page has already been running if at all possible.

Any help you could provide would be greatly appreciated.
ASKER CERTIFIED SOLUTION
TimCottee
Applications and Integrations Consultan

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros