|
 |
asp_databases thread: Re: Stored Procedure + Recordset = No Sense
Message #1 by "Gavin Landon" <glandon@g...> on Thu, 18 Apr 2002 13:34:10 -0500
|
|
adVarchar is your datatype, (varchar), adParamInput just tells the command
object, that this is an input var, not a return, and 40 is the size of the
field.
I'm not sure what your doing below, I have used it like this and it works
great. I didn't write this code, only showing you what they setup and I
use.
objDlCmd.ActiveConnection = objConnection
objDlCmd.CommandText = "StoredProcName"
objDlCmd.CommandType = adCmdStoredProc
objDlCmd.Parameters.Append objDlCmd.CreateParameter("RETURN_VALUE",
adInteger, _
adParamReturnValue, 0)
objDlCmd.Parameters.Append objDlCmd.CreateParameter("Field1Name",
adInteger, _
adParamInput, intField11ArrayVar)
objDlCmd.Parameters.Append objDlCmd.CreateParameter("Field2Name",
adVarChar, _
adParamInput, 15, strField2ArrayVar)
objDlCmd.Parameters.Append objAgStACmd.CreateParameter("Field3Name",
adDate, _
adParamInput)
objDlCmd.Parameters.Append objDlCmd.CreateParameter("Field4Name",
adVarChar, _
adParamInput, 35, strField4Name)
<anhdo@a...> wrote in message news:166189@a..._databases...
>
> Stephen,
>
> What do adVarchar, adParmamInput, and 40 mean in the create parameter
> object below mean? I'm trying hard to figure it out:
>
> Set objParam = objComm.CreateParameter("@SessionID", adVarChar,_
> adParamInput, 40)
>
> Thanks,
> Anh Do
> anhdo@a...
>
> > Hello:
> > I have a Stored Procedure that works and returns what I want when I
> > execute it alone in InterDev. When I try to access it from my ASP code
> the
> > recordset seems not to be open. Now the strange part, if I comment out
> all
> > of the logic in my Stored Pocedure a recordset is created and contains
> the
> > expected records...Here is the code for calling the Stored Procedure:
> >
> > Set objComm = Server.CreateObject("ADODB.Command")
> > Set objRS = Server.CreateObject("ADODB.Recordset")
> >
> > objComm.ActiveConnection = strConnect
> > objComm.CommandText = "sp_permissions"
> > objComm.CommandType = adCmdStoredProc
> >
> > 'Input Parameters
> > Set objParam = objComm.CreateParameter("@SessionID", adVarChar,_
> > adParamInput, 40)
> > objComm.Parameters.Append (objParam)
> >
> >
> > objComm.Parameters ("@SessionID") = SessionID
> >
> >
> > Set objRS = objComm.Execute
> >
> > 'Testing recordset
> > IF NOT objRS IS NOTHING THEN
> > If objRS.BOF AND objRS.EOF Then
> >
> > ...DO Something...
> >
> > End If
> >
> >
> >
> > objRS.Close
> > END IF
> > Set objRS = Nothing
> > Set objParam = Nothing
> > Set objComm = Nothing
> >
> > *****************
> > Now the Code for the Stored Procedure
> >
> > Create Procedure sp_permissions
> >
> > (
> > @SessionID varchar(40)
> > )
> >
> > AS
> > DECLARE @Permission INT
> > DECLARE @MachineNo INT
> > DECLARE @MachineType VARCHAR(60)
> > DECLARE @Count TINYINT
> >
> > DELETE FROM RSTEMP
> >
> > SELECT @Permission=USERS.Permission FROM USERLOG INNER JOIN USERS_
> > ON USERLOG.UserID = USERS.UserID WHERE (USERLOG.SessionID
> > =@SessionID)
> >
> > SET @Count = 0
> >
> > WHILE @Count < 29
> > BEGIN
> > IF (SELECT Status FROM MACHINES WHERE MachineNo
> > =POWER(2,@Count))<>0
> > BEGIN
> > IF ((@Permission & POWER(2,@Count)) = POWER(2,@Count))
> > BEGIN
> > SELECT @MachineNo=MachineNo, @MachineType=MachineType_
> > FROM MACHINES WHERE MachineNo = POWER
> (2,@Count)
> > INSERT INTO RSTEMP (MachineNo, MachineType) VALUES_
> > (@MachineNo, @MachineType)
> > END
> > END
> > SET @Count = @Count + 1
> > END
> >
> > SELECT * FROM RSTEMP
> > RETURN
> >
> > *******
> > If anyone could help me with this it would be greatly appreciated...All
> the
> > documentation I have come across uses simple Stored Procedures which
> this
> > seems not to be...And it isn't obvious I am kinda new to ASP's
> >
> > Thanks
> >
> > Stephen B Looney
>
>
Message #2 by Eric Hobbs <ehobbs@l...> on Wed, 17 Apr 2002 14:50:34 -0700
|
|
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C1E659.E7086750
Content-Type: text/plain
Anh,
I think this is what you're looking for. If you need to reference this kind
of material often, go get a copy of Professional Active Server Pages 3.0 --
a Wrox Publication (ISBN #1-861002-61-0).
------
Set Parameter = Command.CreateParameter (Name, [Type], [Direction], [Size],
[Value])
------
[Type] is the Data Type of the Parameter such as "adBigInt, adBinary,
adBoolean, adBSTR, adChar, adCurrency, adDate, etc..." (adVarChar, in your
example, would indicate a String value)
[Direction] is the direction of the parameter -- such as:
- adParamInput (sent to the stored procedure)
- adParamOutput (retrieved from the stored procedure)
- adParamInputOutput (both an Input and Output parameter)
- adParamReturnValue (the return status of the stored procedure)
[Size] is the size or length of the parameter.
[Value] is the value of the parameter.
___
Eric Hobbs
ehobbs@l...
-----Original Message-----
From: anhdo@a... [mailto:anhdo@a...]
Sent: Wednesday, April 17, 2002 1:32 PM
To: ASP Databases
Subject: [asp_databases] Re: Stored Procedure + Recordset = No Sense
Stephen,
What do adVarchar, adParmamInput, and 40 mean in the create parameter
object below mean? I'm trying hard to figure it out:
Set objParam = objComm.CreateParameter("@SessionID", adVarChar,_
adParamInput, 40)
Thanks,
Anh Do
anhdo@a...
> Hello:
> I have a Stored Procedure that works and returns what I want when I
> execute it alone in InterDev. When I try to access it from my ASP code
the
> recordset seems not to be open. Now the strange part, if I comment out
all
> of the logic in my Stored Pocedure a recordset is created and contains
the
> expected records...Here is the code for calling the Stored Procedure:
>
> Set objComm = Server.CreateObject("ADODB.Command")
> Set objRS = Server.CreateObject("ADODB.Recordset")
>
> objComm.ActiveConnection = strConnect
> objComm.CommandText = "sp_permissions"
> objComm.CommandType = adCmdStoredProc
>
> 'Input Parameters
> Set objParam = objComm.CreateParameter("@SessionID", adVarChar,_
> adParamInput, 40)
> objComm.Parameters.Append (objParam)
>
>
> objComm.Parameters ("@SessionID") = SessionID
>
>
> Set objRS = objComm.Execute
>
> 'Testing recordset
> IF NOT objRS IS NOTHING THEN
> If objRS.BOF AND objRS.EOF Then
>
> ...DO Something...
>
> End If
>
>
>
> objRS.Close
> END IF
> Set objRS = Nothing
> Set objParam = Nothing
> Set objComm = Nothing
>
> *****************
> Now the Code for the Stored Procedure
>
> Create Procedure sp_permissions
>
> (
> @SessionID varchar(40)
> )
>
> AS
> DECLARE @Permission INT
> DECLARE @MachineNo INT
> DECLARE @MachineType VARCHAR(60)
> DECLARE @Count TINYINT
>
> DELETE FROM RSTEMP
>
> SELECT @Permission=USERS.Permission FROM USERLOG INNER JOIN USERS_
> ON USERLOG.UserID = USERS.UserID WHERE (USERLOG.SessionID
> =@SessionID)
>
> SET @Count = 0
>
> WHILE @Count < 29
> BEGIN
> IF (SELECT Status FROM MACHINES WHERE MachineNo
> =POWER(2,@Count))<>0
> BEGIN
> IF ((@Permission & POWER(2,@Count)) = POWER(2,@Count))
> BEGIN
> SELECT @MachineNo=MachineNo, @MachineType=MachineType_
> FROM MACHINES WHERE MachineNo = POWER
(2,@Count)
> INSERT INTO RSTEMP (MachineNo, MachineType) VALUES_
> (@MachineNo, @MachineType)
> END
> END
> SET @Count = @Count + 1
> END
>
> SELECT * FROM RSTEMP
> RETURN
>
> *******
> If anyone could help me with this it would be greatly appreciated...All
the
> documentation I have come across uses simple Stored Procedures which
this
> seems not to be...And it isn't obvious I am kinda new to ASP's
>
> Thanks
>
> Stephen B Looney
Message #3 by anhdo@a... on Wed, 17 Apr 2002 20:31:45
|
|
Stephen,
What do adVarchar, adParmamInput, and 40 mean in the create parameter
object below mean? I'm trying hard to figure it out:
Set objParam = objComm.CreateParameter("@SessionID", adVarChar,_
adParamInput, 40)
Thanks,
Anh Do
anhdo@a...
> Hello:
> I have a Stored Procedure that works and returns what I want when I
> execute it alone in InterDev. When I try to access it from my ASP code
the
> recordset seems not to be open. Now the strange part, if I comment out
all
> of the logic in my Stored Pocedure a recordset is created and contains
the
> expected records...Here is the code for calling the Stored Procedure:
>
> Set objComm = Server.CreateObject("ADODB.Command")
> Set objRS = Server.CreateObject("ADODB.Recordset")
>
> objComm.ActiveConnection = strConnect
> objComm.CommandText = "sp_permissions"
> objComm.CommandType = adCmdStoredProc
>
> 'Input Parameters
> Set objParam = objComm.CreateParameter("@SessionID", adVarChar,_
> adParamInput, 40)
> objComm.Parameters.Append (objParam)
>
>
> objComm.Parameters ("@SessionID") = SessionID
>
>
> Set objRS = objComm.Execute
>
> 'Testing recordset
> IF NOT objRS IS NOTHING THEN
> If objRS.BOF AND objRS.EOF Then
>
> ...DO Something...
>
> End If
>
>
>
> objRS.Close
> END IF
> Set objRS = Nothing
> Set objParam = Nothing
> Set objComm = Nothing
>
> *****************
> Now the Code for the Stored Procedure
>
> Create Procedure sp_permissions
>
> (
> @SessionID varchar(40)
> )
>
> AS
> DECLARE @Permission INT
> DECLARE @MachineNo INT
> DECLARE @MachineType VARCHAR(60)
> DECLARE @Count TINYINT
>
> DELETE FROM RSTEMP
>
> SELECT @Permission=USERS.Permission FROM USERLOG INNER JOIN USERS_
> ON USERLOG.UserID = USERS.UserID WHERE (USERLOG.SessionID
> =@SessionID)
>
> SET @Count = 0
>
> WHILE @Count < 29
> BEGIN
> IF (SELECT Status FROM MACHINES WHERE MachineNo
> =POWER(2,@Count))<>0
> BEGIN
> IF ((@Permission & POWER(2,@Count)) = POWER(2,@Count))
> BEGIN
> SELECT @MachineNo=MachineNo, @MachineType=MachineType_
> FROM MACHINES WHERE MachineNo = POWER
(2,@Count)
> INSERT INTO RSTEMP (MachineNo, MachineType) VALUES_
> (@MachineNo, @MachineType)
> END
> END
> SET @Count = @Count + 1
> END
>
> SELECT * FROM RSTEMP
> RETURN
>
> *******
> If anyone could help me with this it would be greatly appreciated...All
the
> documentation I have come across uses simple Stored Procedures which
this
> seems not to be...And it isn't obvious I am kinda new to ASP's
>
> Thanks
>
> Stephen B Looney
|
|
|
View Cart
