jpve
asked on
a doozy of an asp recordset question :/
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.su
}
</script>
<span class="Text_PageTitle">Iss
<br/><br/>
<%
Dim cmdCommand, rstData, strShowAll
Set cmdCommand = Server.CreateObject("ADODB
cmdCommand.ActiveConnectio
cmdCommand.CommandType = 4
If Request.Form("chkShowAll")
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();""
"<td align=""right""><a href=""entry_addedit.asp?E
"</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"">"
"<td width=""5%""><a href='default.asp?sort=id'
"<td width=""10%""><a href='default.asp?sort=typ
"<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=sta
"<td width=""5%""><a href='default.asp?sort=pri
"<td width=""10%"">Action</td>"
"</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>" & 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?E
"<a href=""entry_details.asp?E
"<a href=""entry_delete.asp?EI
Else
Response.Write "<a href=""entry_details.asp?E
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.
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.su
}
</script>
<span class="Text_PageTitle">Iss
<br/><br/>
<%
Dim cmdCommand, rstData, strShowAll
Set cmdCommand = Server.CreateObject("ADODB
cmdCommand.ActiveConnectio
cmdCommand.CommandType = 4
If Request.Form("chkShowAll")
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();""
"<td align=""right""><a href=""entry_addedit.asp?E
"</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"">"
"<td width=""5%""><a href='default.asp?sort=id'
"<td width=""10%""><a href='default.asp?sort=typ
"<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=sta
"<td width=""5%""><a href='default.asp?sort=pri
"<td width=""10%"">Action</td>"
"</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>" & 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?E
"<a href=""entry_details.asp?E
"<a href=""entry_delete.asp?EI
Else
Response.Write "<a href=""entry_details.asp?E
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
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?
ASKER
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...
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...
ASKER
Blah, sorry for the double post, and sorry for calling ya "Guru" heh, just noticed it's actually TimCottee.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.