Hi Sergey,
I was looking for a way of calling a stored procedure in a SQL Server and if it is posible also passing some parameters entered in one of the pages.
Probably it is not that easy but it would be a really nice and powerfull feature for ASPRunner.
Has anybody done something like this? it will be nice to have some help in this area.
Thanks a lot in advance
I would also love to see an example as I haven't figured it out yet but here is a real working sample I have created for a system
hdxdata.asp (receives the parameters: hdxdata.asp?CallID=1234&Seg=1234&PrimeRep=1234&AMGRep=1234&Lang=NA&App=App_1&Sector=1234&Group=1234&ExtID=1234)
<%@ Language = "VBScript" %>
<!-- #INCLUDE VIRTUAL="include/ADOVBS.INC" -->
<%
strParam1 = Request.QueryString("CallID")
strParam2 = Request.QueryString("Seg")
strParam3 = Request.QueryString("PrimeRep")
strParam4 = Request.QueryString("AMGRep")
strParam5 = Request.QueryString("Lang")
strParam6 = Request.QueryString("App")
strParam7 = Request.QueryString("Sector")
strParam8 = Request.QueryString("Group")
strParam9 = Request.QueryString("ExtID")
Dim objConn
Dim objCmd
Dim strCnn
Set objConn = Server.CreateObject("ADODB.Connection")
set objCmd = Server.CreateObject("ADODB.Command")
strCnn = "DRIVER=SQL Server;SERVER=HDXDBSrv;UID=Usccs;PWD=Psccs;APP=HDXData;WSID=SYMPO;DATABASE=HDXDa
ta;Network=DBMSSOCN"
objConn.Open strCnn
With objCmd
.ActiveConnection = objConn
.CommandText = "InsHDX"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@CallID", adChar, adParamInput, 10)
.Parameters.Append .CreateParameter("@Seg", adChar, adParamInput, 6)
.Parameters.Append .CreateParameter("@PrimeRep", adChar, adParamInput, 20)
.Parameters.Append .CreateParameter("@AMGRep", adChar, adParamInput, 6)
.Parameters.Append .CreateParameter("@Lang", adChar, adParamInput, 4)
.Parameters.Append .CreateParameter("@App", adChar, adParamInput, 80)
.Parameters.Append .CreateParameter("@Sector", adChar, adParamInput, 4)
.Parameters.Append .CreateParameter("@Group", adChar, adParamInput, 8)
.Parameters.Append .CreateParameter("@ExtID", adChar, adParamInput, 20)
objCmd("@CallID") = strParam1
objCmd("@Seg") = strParam2
objCmd("@PrimeRep") = strParam3
objCmd("@AMGRep") = strParam4
objCmd("@Lang") = strParam5
objCmd("@App") = strParam6
objCmd("@Sector") = strParam7
objCmd("@Group") = strParam8
objCmd("@ExtID") = strParam9
.Execute, , adExecuteNoRecords
End With
objConn.Close
Set objConn = Nothing
Set objCmd = Nothing
%>
***
Stored Procedure - SQL Server 2000
CREATE PROCEDURE dbo.[InsHDX]
(
@CallID char(10)=NULL,
@Seg char(6)=NULL,
@PrimeRep char(20)=NULL,
@AMGRep char(6)=NULL,
@Lang char(4)=NULL,
@App char(80)=NULL,
@Sector char(4)=NULL,
@Group char(8)=NULL,
@ExtID char(20)=NULL
)
AS
SET NOCOUNT ON
SET ANSI_NULLS OFF
SET ANSI_PADDING OFF
IF EXISTS
(SELECT CallID from dbo.Tmp_HDXCad WHERE dbo.Tmp_HDXCad.CallID = @CallID)
BEGIN
UPDATE dbo.Tmp_HDXCad
SET dbo.Tmp_HDXCad.CallID = @CallID, dbo.Tmp_HDXCad.Segmentation = @Seg, dbo.Tmp_HDXCad.PrimeRepID = @PrimeRep, dbo.Tmp_HDXCad.AMGRepID = @AMGRep, dbo.Tmp_HDXCad.Lang = @Lang, dbo.Tmp_HDXCad.App = @App, dbo.Tmp_HDXCad.Sector = @Sector, dbo.Tmp_HDXCad.[Group] = @Group, dbo.Tmp_HDXCad.ExtID = @ExtID, dbo.Tmp_HDXCad.[TimeStamp] = GetDate()
WHERE dbo.Tmp_HDXCad.CallID = @CallID
END
ELSE
BEGIN
INSERT INTO dbo.Tmp_HDXCad
(CallID, Segmentation, PrimeRepID, AMGRepID, Lang, App, Sector, [Group], ExtID)
VALUES
( @CallID, @Seg, @PrimeRep, @AMGRep, @Lang, @App, @Sector, @Group, @ExtID)
END
SET NOCOUNT OFF
GO