Very Computer

by **Beat Richl** » Wed, 05 Sep 2001 01:06:26

hi all

firstable, i've read thru several threads here and some other docs - can't
find the solution :(
i'm trying to get the output parameter of a stored procedure back into ASP.
the SP is somewhat set as is, can't do much about it.

there are about 15 input and 1 output parameter in the SP. all parameters
have a default. as a first step i only need to call the procedure and get
the output parameter (a ID) back.

this is what i tried so far, copied from examples...

----------
Set cmd = Server.CreateObject("ADODB.command")
cmd.ActiveConnection = session("wwwcn")
cmd.CommandText = "newAccount"
cmd.CommandType = 4

Set param1 = cmd.CreateParameter("Output",3,2) <--- IIS would not accept
constants like adParamOutput....
param1.value=0
cmd.Parameters.Append param1

Set rs = cmd.Execute
While Not rs.EOF
For Each fldloop In rs.Fields
Response.Write("rs.Fields(i)")
i = i + 1
Next
i = 0
rs.MoveNext
Wend
---------

the first parameter in the SP is the Output parameter, is defaulted to 0 and
will contain the newly created ID.

if i run this code, following errormessage at the "cmd.execute" line
appears: (translated from english)

"The Value NULL cannot be inserted into blablabla" it's a constrain.

if i run "exec newAccount" in Query Analyzer it works

what is wrong ? i also need to know, if i can use named parameters

named parameters are possible, aren't they, plleaaase

please, do not suggest to change the SP.

thanks a lot for any help.

Beat

PS. this command object is a incredibly annoying way to handle SP
output/input values. i hate it... it's like the developpers sat together and
asked "how could we make as a complicated as humanly possible". but well,
maybe someone can shed some light on this. i hope so.

Beorda Direktwerbung AG
Abteilung EDV
Kantonsstrasse 101
6234 Triengen
Tel.: 041 935 40 60
Fax: 041 935 40 76
Tel. direkt: 041 935 40 68

by **Evan Jon** » Thu, 06 Sep 2001 05:58:24

by **Beat Richl** » Thu, 06 Sep 2001 21:54:16

well, that sounds like an educated guess :-)
thanks anyway, evan. i've put the problem on ice for 1 or 2 days, more
important things come first. but i will try something like that soon.
problem is, if i define it as input, how will i get the data back ?
more tips are VERY appreciated.

regards,

beat

by Me » Fri, 07 Sep 2001 01:05:50

I think you have to close the recordset in the ASP first, before you
can get the value of the Output Parameter

Mel

by **Evan Jon** » Sat, 08 Sep 2001 00:59:53

> well, that sounds like an educated guess :-)
> thanks anyway, evan. i've put the problem on ice for 1 or 2 days, more
> important things come first. but i will try something like that soon.
> problem is, if i define it as input, how will i get the data back ?
> more tips are VERY appreciated.

> regards,

> beat

A guess? Absolutely. Educated? Don't know about that.

Can you post the SP code? From your description of the error that
seems to be where the actual problem occurs. I know you said you
can't change the SP, but seeing what it actually does might help.

by **Leonid Kozhinsk** » Tue, 11 Sep 2001 19:11:38

Use "objConnection.CursorLocation = adUseClient" and you can get both
recordset and output parameter in any order. By default ADODB uses
adUseServer ....

Leonid Kozhinsky

> hi all

> firstable, i've read thru several threads here and some other docs - can't
> find the solution :(
> i'm trying to get the output parameter of a stored procedure back into
ASP.
> the SP is somewhat set as is, can't do much about it.

> there are about 15 input and 1 output parameter in the SP. all parameters
> have a default. as a first step i only need to call the procedure and get
> the output parameter (a ID) back.

> this is what i tried so far, copied from examples...

> ----------
> Set cmd = Server.CreateObject("ADODB.command")
> cmd.ActiveConnection = session("wwwcn")
> cmd.CommandText = "newAccount"
> cmd.CommandType = 4

> Set param1 = cmd.CreateParameter("Output",3,2) <--- IIS would not
accept
> constants like adParamOutput....
> param1.value=0
> cmd.Parameters.Append param1

> Set rs = cmd.Execute
> While Not rs.EOF
> For Each fldloop In rs.Fields
> Response.Write("rs.Fields(i)")
> i = i + 1
> Next
> i = 0
> rs.MoveNext
> Wend
> ---------

> the first parameter in the SP is the Output parameter, is defaulted to 0
and
> will contain the newly created ID.

> if i run this code, following errormessage at the "cmd.execute" line
> appears: (translated from english)

> "The Value NULL cannot be inserted into blablabla" it's a constrain.

> if i run "exec newAccount" in Query Analyzer it works

> what is wrong ? i also need to know, if i can use named parameters

> named parameters are possible, aren't they, plleaaase

> please, do not suggest to change the SP.

> thanks a lot for any help.

> Beat

> PS. this command object is a incredibly annoying way to handle SP
> output/input values. i hate it... it's like the developpers sat together
and
> asked "how could we make as a complicated as humanly possible". but well,
> maybe someone can shed some light on this. i hope so.

> Beorda Direktwerbung AG
> Abteilung EDV
> Kantonsstrasse 101
> 6234 Triengen
> Tel.: 041 935 40 60
> Fax: 041 935 40 76
> Tel. direkt: 041 935 40 68

by **Beat Richl** » Fri, 14 Sep 2001 01:29:20

many thanks, leonid ! that sounds good. i haven't solved the problem till
now. going to try that right away.

> Use "objConnection.CursorLocation = adUseClient" and you can get both
> recordset and output parameter in any order. By default ADODB uses
> adUseServer ....

> Leonid Kozhinsky

> > hi all

> > firstable, i've read thru several threads here and some other docs -
can't
> > find the solution :(
> > i'm trying to get the output parameter of a stored procedure back into
> ASP.
> > the SP is somewhat set as is, can't do much about it.

> > there are about 15 input and 1 output parameter in the SP. all
parameters
> > have a default. as a first step i only need to call the procedure and
get
> > the output parameter (a ID) back.

> > this is what i tried so far, copied from examples...

> > ----------
> > Set cmd = Server.CreateObject("ADODB.command")
> > cmd.ActiveConnection = session("wwwcn")
> > cmd.CommandText = "newAccount"
> > cmd.CommandType = 4

> > Set param1 = cmd.CreateParameter("Output",3,2) <--- IIS would not
> accept
> > constants like adParamOutput....
> > param1.value=0
> > cmd.Parameters.Append param1

> > Set rs = cmd.Execute
> > While Not rs.EOF
> > For Each fldloop In rs.Fields
> > Response.Write("rs.Fields(i)")
> > i = i + 1
> > Next
> > i = 0
> > rs.MoveNext
> > Wend
> > ---------

> > the first parameter in the SP is the Output parameter, is defaulted to 0
> and
> > will contain the newly created ID.

> > if i run this code, following errormessage at the "cmd.execute" line
> > appears: (translated from english)

> > "The Value NULL cannot be inserted into blablabla" it's a
constrain.

> > if i run "exec newAccount" in Query Analyzer it works

> > what is wrong ? i also need to know, if i can use named parameters
> > (explizit?) with a command object. like this: "exec newAccount

> > named parameters are possible, aren't they, plleaaase

> > please, do not suggest to change the SP.

> > thanks a lot for any help.

> > Beat

> > PS. this command object is a incredibly annoying way to handle SP
> > output/input values. i hate it... it's like the developpers sat together
> and
> > asked "how could we make as a complicated as humanly possible". but
well,
> > maybe someone can shed some light on this. i hope so.

> > Beorda Direktwerbung AG
> > Abteilung EDV
> > Kantonsstrasse 101
> > 6234 Triengen
> > Tel.: 041 935 40 60
> > Fax: 041 935 40 76
> > Tel. direkt: 041 935 40 68

by **Beat Rich** » Thu, 27 Sep 2001 05:36:33

I've solved the problem in the meanwhile with some great efforts. The
main solution is to install at least MDAC 2.6 . its the MS ADO
component. previous versions do NOT support named parameters on SP
calls from ASP. you can get it from here:
http://www.microsoft.com/data/

here's a working example, the SP header is posted further below:
first, i include a little asp file with ADO constants, for better
reading of the code and to be able to use examples...:

<%
'ADO und andere VB Konstanten
const adCmdStoredProc = 4

const adParamInput = 1
const adParamOutput = 2
const adParamInputOutput = 3

const adTinyInt = 16
const adInteger = 3
const adChar = 129
%>

*********here's the SP call*************
'Neuer Account
Set y_cmd=Server.CreateObject("ADODB.Command")
Set y_Cmd.ActiveConnection = session("wwwcn")

y_Cmd.CommandText = "newaccount"
y_Cmd.CommandType = adCmdStoredProc
y_Cmd.NamedParameters = True

adParamInputOutput , 4)

adParamInput, 1)

if trim(y_cntry)<>"" then

adParamInput, 2)

end if

y_cmd.execute
set y_cmd=nothing

********and here's the beginning of the SP***************

= 0 )

as

> many thanks, leonid ! that sounds good. i haven't solved the problem till
> now. going to try that right away.

> > Use "objConnection.CursorLocation = adUseClient" and you can get both
> > recordset and output parameter in any order. By default ADODB uses
> > adUseServer ....

> > Leonid Kozhinsky

> > > hi all

> > > firstable, i've read thru several threads here and some other docs -
> can't
> > > find the solution :(
> > > i'm trying to get the output parameter of a stored procedure back into
> ASP.
> > > the SP is somewhat set as is, can't do much about it.

> > > there are about 15 input and 1 output parameter in the SP. all
> parameters
> > > have a default. as a first step i only need to call the procedure and
> get
> > > the output parameter (a ID) back.

> > > this is what i tried so far, copied from examples...

> > > ----------
> > > Set cmd = Server.CreateObject("ADODB.command")
> > > cmd.ActiveConnection = session("wwwcn")
> > > cmd.CommandText = "newAccount"
> > > cmd.CommandType = 4

> > > Set param1 = cmd.CreateParameter("Output",3,2) <--- IIS would not
> accept
> > > constants like adParamOutput....
> > > param1.value=0
> > > cmd.Parameters.Append param1

> > > Set rs = cmd.Execute
> > > While Not rs.EOF
> > > For Each fldloop In rs.Fields
> > > Response.Write("rs.Fields(i)")
> > > i = i + 1
> > > Next
> > > i = 0
> > > rs.MoveNext
> > > Wend
> > > ---------

> > > the first parameter in the SP is the Output parameter, is defaulted to 0
> and
> > > will contain the newly created ID.

> > > if i run this code, following errormessage at the "cmd.execute" line
> > > appears: (translated from english)

> > > "The Value NULL cannot be inserted into blablabla" it's a
> constrain.

> > > if i run "exec newAccount" in Query Analyzer it works

> > > what is wrong ? i also need to know, if i can use named parameters
> > > (explizit?) with a command object. like this: "exec newAccount

> > > named parameters are possible, aren't they, plleaaase

> > > please, do not suggest to change the SP.

> > > thanks a lot for any help.

> > > Beat

> > > PS. this command object is a incredibly annoying way to handle SP
> > > output/input values. i hate it... it's like the developpers sat together
> and
> > > asked "how could we make as a complicated as humanly possible". but
> well,
> > > maybe someone can shed some light on this. i hope so.

> > > Beorda Direktwerbung AG
> > > Abteilung EDV
> > > Kantonsstrasse 101
> > > 6234 Triengen
> > > Tel.: 041 935 40 60
> > > Fax: 041 935 40 76
> > > Tel. direkt: 041 935 40 68

Very Computer

Going Nuts ! Stored Proc and ASP

Going Nuts ! Stored Proc and ASP

Going Nuts ! Stored Proc and ASP

Going Nuts ! Stored Proc and ASP

Going Nuts ! Stored Proc and ASP

Going Nuts ! Stored Proc and ASP

Going Nuts ! Stored Proc and ASP

Going Nuts ! Stored Proc and ASP

Going Nuts ! Stored Proc and ASP