March 26, 2002 at 11:20 am
I pass data to stored procedures all day long from VisualinterDev .asp like this:
strSQL = "EXEC uspReports @EmpID = '" & EmpID & "', @mode = '" & mode & "'"
Set rsRecs = rsRecords.Execute(strSQL)
I can pass data in & out of usps & query analyzer no prob:
EXEC spBusDays @StartDate =@strStartday, @DiffDays =@strDiffDays OUTPUT
How can I pass data in & out of a .asp using OUTPUT parameter?
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
March 26, 2002 at 12:22 pm
You need a command object and a parameters collection. Should find plenty of stuff on MS site. Also a couple articles here:
http://www.sqlservercentral.com/columnists/awarren/reducingroundtripspart3.asp
http://www.sqlservercentral.com/columnists/awarren/introductiontoadothecommandobject.asp
Andy
March 26, 2002 at 2:09 pm
Sorry man, none of this code is working in InterDev, I can't seem to find sample code anywhere.
Dim DATABASE as ADODB.Connection
Dim cmd as ADODB.Command
Dim params as ADODB.parameters
Set DATABASE = Server.CreateObject("ADODB.Connection")
DATABASE.Open Application("DATABASE_ConnectionString")
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = DATABASE
cmd.CommandText = "spAddBusinessDays"
cmd.CommandType = adCmdStoredProc
Set params = cmd.parameters
params.append cmdTemp.CreateParameter("@StartDate", addatetime, adparamInput, 8)
params.append cmdTemp.CreateParameter("@AddDays", adInteger, adparamInput, 4)
params.append cmdTemp.CreateParameter("@NewDate", addatetime, adparamReturnValue, 8)
params("@StartDate") = Date()
params("@AddDays") = -2
cmd.Execute, , adExecuteNoRecords
str1day = params("@NewDate")
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
March 26, 2002 at 2:40 pm
Can you post the DDL for the proc?
Andy
March 26, 2002 at 2:50 pm
the asp doesn't get that far:
Microsoft VBScript compilation error '800a0401'
Expected end of statement
/DATA/reports/sumReport.asp, line 66
Dim DATABASE as ADODB.Connection
-----------^
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
March 26, 2002 at 2:54 pm
so I commented the Dims out, heres the next error:
Microsoft VBScript runtime error '800a01b6'
Object doesn't support this property or method: 'parameters'
/DATA/reports/sumReport.asp, line 79
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
March 26, 2002 at 3:21 pm
Have you set a reference to the ADO library?
Andy
March 26, 2002 at 3:22 pm
Isn't Interdev loosely typed? Try declaring as variant.
Andy
March 26, 2002 at 3:44 pm
It is loose, but I can't (AS) anything.
this works:
Dim DATABASE
Dim cmd
Dim params
but I still run into:
Microsoft VBScript runtime error '800a01b6'
Object doesn't support this property or method: 'parameters'
/DATA/reports/sumReport.asp, line 94
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
March 26, 2002 at 6:03 pm
Try this
'Note: variables in VBScript are only of the variant type so you do not declare type.
Dim DATABASE
Dim cmd
Dim params
Set DATABASE = Server.CreateObject("ADODB.Connection")
DATABASE.Open Application("DATABASE_ConnectionString")
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = DATABASE
cmd.CommandText = "spAddBusinessDays"
cmd.CommandType = adCmdStoredProc
'Parameters are appended to the command object directly.
'CreateParameter(NameForReferenceDoesntMatter,ExactDataType,InputReturn,Size,Value)
'Usually leave out the last 1, also must apply each parameter in order as in the procedure.
cmd.Parameters.Append cmd.CreateParameter("StartDate", addatetime, adparamInput, 8)
cmd.Parameters.Append cmd.CreateParameter("AddDays", adInteger, adparamInput, 4)
cmd.Parameters.Append cmd.CreateParameter("NewDate", addatetime, adparamReturnValue, 8)
cmd.Parameters("StartDate") = Date()
cmd.Parameters("AddDays") = -2
cmd.Execute, , adExecuteNoRecords
str1day = cmd.Parameters("NewDate")
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 27, 2002 at 7:51 am
Looks like your almost right:
ADODB.Parameters error '800a0e7c'
Parameter object is improperly defined. Inconsistent or incomplete information was provided.
cmd.parameters.append cmd.CreateParameter("StartDate", addatetime, adparamInput, 8)
cmd.parameters.append cmd.CreateParameter("AddDays", adInteger, adparamInput, 4)
cmd.parameters.append cmd.CreateParameter("NewDate", addatetime, adparamReturnValue, 8)
I'm using the datatype length for the size, do I need something for the value?
By the way, thanks much for your help!
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
March 27, 2002 at 7:54 am
This is the usp interface
CREATE PROCEDURE spAddBusinessDays @StartDate smalldatetime, @AddDays smallint, @NewDate smalldatetime OUTPUT
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
March 27, 2002 at 8:58 am
You parameters do not match with the ADO objects being sent thru or the sizes. Change this
cmd.Parameters.Append cmd.CreateParameter("StartDate", addatetime, adparamInput, 8)
cmd.Parameters.Append cmd.CreateParameter("AddDays", adInteger, adparamInput, 4)
cmd.Parameters.Append cmd.CreateParameter("NewDate", addatetime, adparamReturnValue, 8)
to
cmd.Parameters.Append cmd.CreateParameter("StartDate", adDBTimeStamp, adparamInput, 4)
cmd.parameters.append cmd.CreateParameter("AddDays", adSmallInt, adparamInput, 2)
cmd.Parameters.Append cmd.CreateParameter("NewDate", adDBTimeStamp, adparamReturnValue, 4)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 27, 2002 at 9:00 am
I got it to work!
I was using addatetime, it needs addate.
And the variable names do mater. This may be a Visual InterDev thing.
Again, I can't thank you enough for your help!
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
March 27, 2002 at 9:03 am
Must be as VB itself never has given me any trouble. I will have to play with a bit one day to be sure what actually does what.
Never mind, I must have been thinking of something else.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 03/27/2002 09:06:57 AM
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply