Passing a querystring to a stored procedure?
-
I am looking for some help on this issue....Thanks in advance... I pass form variables to a stored procedure in ASP which runs correctly regardless what combo of variables the SP receives:
strSQL="search_orders_1 @Region = '" & request("Region") & "', @SMC = '" & request("SMC") & "', @ATS = '" & request("ATS") & "', @Platform = '" & request("Platform") & "'"
I am paging through the recordset and get the (Prev 1 2 3 Next) links to work, but when I click to go to the next page, the querystring that is being rebuilt and passed back to the page fails to work. Does anyone know of a way to get paging through records to work with a SP? Here is some code: (The red is where the query is rebuilt and passed back to the page... All I get is a Done message on the web page)
' Get parameters
iPageSize = 10 ' You could easily allow users to change this
' Retrieve page to show or default to 1
If Request.QueryString("page") = "" Then
iPageCurrent = 1
Else
iPageCurrent = CInt(Request.QueryString("page"))
'response.write iPageCurrent
End If
If Request.QueryString("order") = "" Then
strOrderBy = "id"
Else
strOrderBy = Request.QueryString("order")
End If
if request.querystring("strSQLNext") <> "" then
strSQL = request.querystring("strSQLNext")
else
strSQL="search_orders_1 @Region = '" & request("Region") & "', @SMC = '" & request("SMC") & "', @ATS = '" & request("ATS") & "', @Platform = '" & request("Platform") & "'"
...connect to db, open RS etc.. blah blah blah
... show table loop etc....
<%
' Show "previous" and "next" page links which pass the page to view
' and any parameters needed to rebuild the query. You could just as
' easily use a form but you'll need to change the lines that read
' the info back in at the top of the script.
If iPageCurrent > 1 Then
%>
<font face="Tahoma">
<a href="find1.asp?page=<%= iPageCurrent - 1 %>&order=<%= Server.URLEncode(strOrderBy) %>&strSQLNext=">
<font size="1">[Prev]</font></a><font size="1">
<%
End If
' You can also show page numbers:
For I = 1 To iPageCount
If I = iPageCurrent Then
%>
<font size="1"><%= I %></font>
<%
Else
%>
<a href="find1.asp?page=<%= I %>&order=<%= Server.URLEncode(strOrderBy) %>&strSQLNext=<%= Server.URLEncode(strSQL) %>"><%= I %></a>
<%
End If
Next 'I
If iPageCurrent < iPageCount Then
%>
<a href="find1.asp?page=<%= iPageCurrent + 1 %>&order=<%= Server.URLEncode(strOrderBy) %>&strSQLNext=<%= Server.URLEncode(strSQL) %>">[Next]</a>
<%
end if
end if
' END RUNTIME CODE
-
- keep sql-injection in mind !
Check http://www.sqlservercentral.com/columnists/chedgate/sqlinjection.asp- just define your variables as sp-parameters and handle your statement in your sp. You will soon discover it's best to work with known predicates.
- check http://www.sommarskog.se/dynamic_sql.html and http://www.sommarskog.se/dyn-search.html
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hey, what a shame for me! Too late...
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Sorry Frank, didn't know you were here today
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Sorry Frank, didn't know you were here today
Now, that's a cheap excuse! Whereelse should I be?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Don't Know if this helps but this is how we do it.
<%
Dim CCList__classid
CCList__classid = "1"
if(Request("classid") <> "") then CCList__classid = Request("classid")
%>
<%
Dim classlist__MMColParam
classlist__MMColParam = "1"
If (Request.QueryString("Classid") <> "") Then
classlist__MMColParam = Request.QueryString("Classid")
End If
%>
<%
Dim classlist
Dim classlist_numRows
Set classlist = Server.CreateObject("ADODB.Recordset")
classlist.ActiveConnection = MM_inservice_STRING
classlist.Source = "SELECT * FROM dbo.t_ClassRoster WHERE Class_Number = " + Replace(classlist__MMColParam, "'", "''") + ""
classlist.CursorType = 0
classlist.CursorLocation = 2
classlist.LockType = 1
classlist.Open()
classlist_numRows = 0
%>
<%
set CCList = Server.CreateObject("ADODB.Command")
CCList.ActiveConnection = MM_inservice_STRING
CCList.CommandText = "dbo.CommaEmailList"
CCList.CommandType = 4
CCList.CommandTimeout = 0
CCList.Prepared = true
CCList.Parameters.Append CCList.CreateParameter("@RETURN_VALUE", 3, 4)
CCList.Parameters.Append CCList.CreateParameter("@classid", 3, 1,9,CCList__classid)
set CCEmailList = CCList.Execute
CCEmailList_numRows = 0
%>
<html>
<head>
And the stored proc that it is calling looks like:
CREATE PROCEDURE [dbo].[CommaEmailList]
@classid int
AS
DECLARE @EmployeeList varchar(5000)
SELECT @EmployeeList = COALESCE(@EmployeeList + ',','') +
CAST(WEBUSERS.dbo.Main.EMAIL AS varchar(50))
FROM WEBUSERS.dbo.Main INNER JOIN
dbo.t_ClassRoster ON WEBUSERS.dbo.Main.UID = dbo.t_ClassRoster.UID
WHERE (dbo.t_ClassRoster.Class_Number = @classid)
SELECT @EmployeeList as emaillist
GO
-
Thanx for the help...... If you can believe it, I had an 'end if' in the wrong place which caused the re-submit of the querystring not to execute..... DOH!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply