We help IT Professionals succeed at work.

a doozy of an asp recordset question :/

jpve
jpve asked
on
288 Views
Last Modified: 2010-03-19
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.
Comment
Watch Question

TimCotteeApplications and Integrations Consultan

Commented:
If Request.Form("chkShowAll") = "on" Then
      cmdCommand.CommandText = "uspGetAllEntries"
Else
      cmdCommand.CommandText = "uspGetAllOpenEntries"
End If

This is the point, the recordset returned is based on one of these two stored procedures. If you need to change what is returned then you need to modify these rather than specify the query in plain text.

Author

Commented:
That's Guru - I'm just wondering, where would those stored procedures be found though - within SQL Server somewhere?  Because like I said, the page(s) are using Option Explicit so I tried to find where both of them were Dim'ed but I couldn't find anything?

Author

Commented:
That's Guru - I'm just wondering, where would those stored procedures be found though - within SQL Server somewhere?  Because like I said, the page(s) are using Option Explicit so I tried to find where both of them were Dim'ed but I couldn't find anything?  I just did a Find for uspGetAllEntries but the only result is that one line of code:

cmdCommand.CommandText = "uspGetAllEntries"

So I'm led to believe it must be in SQL Server somewhere?  Is it possible for me to just replace that stored procedure with something like "SELECT * FROM table SORT BY column" or is that a bad idea?  I'm thinking he chose a stored procedure for a reason...

Author

Commented:
Blah, sorry for the double post, and sorry for calling ya "Guru" heh, just noticed it's actually TimCottee.
Applications and Integrations Consultan
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.