stored Procedure with argument

I am Beginner in ASP
Problem:I have a table named emp which fields are(id int(4), name
varchar(50), rollNo int(4))
I have to just pass the name of the emp and it will just say that
record is found or Not

My code is :

<%
nm1 = request.form("txtName")
Set cmd = Server.CreateObject("adodb.Command")
cmd.ActiveConnection = con
cmd.CommandText = "sp_search"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("nm", adchar, adParamInput)
'Just Above Line no is 19
cmd.Parameters.Append cmd.CreateParameter("flag",adInteger,
adParamReturnValue)
cmd.execute
x = cmd.Parameters("flag")
Response.write (x)
y = Cint(x)
if y = 1 then
%>

<%
else
%>

<%
end if
%>

My Stored Procedure is

CREATE PROCEDURE sp_search(@nm varchar(50), @flag int output) AS

if exists(select name from emp where name = @nm)
begin
select @flag = 1
return @flag
end
else
begin
select @flag = 0
return @flag
end
GO

My Error is
Error Type:
ADODB.Parameters (0x800A0E7C)
Parameter object is improperly defined. Inconsistent or incomplete
information was provided.
/vkasp/search_rec.asp, line 19

Re: stored Procedure with argument

vinodkus@gmail.com wrote:
> I am Beginner in ASP
> Problem:I have a table named emp which fields are(id int(4), name
> varchar(50), rollNo int(4))
> I have to just pass the name of the emp and it will just say that
> record is found or Not
>
> My code is :
>
> <%
> nm1 = request.form("txtName")
> Set cmd = Server.CreateObject("adodb.Command")
> cmd.ActiveConnection = con
> cmd.CommandText = "sp_search"
> cmd.CommandType = adCmdStoredProc
> cmd.Parameters.Append cmd.CreateParameter("nm", adchar, adParamInput)
> 'Just Above Line no is 19

You have a varchar parameter and set it up as char. advarchar is the correct
datatype constant. Also, you need to include the length argument.

> cmd.Parameters.Append cmd.CreateParameter("flag",adInteger,
> adParamReturnValue)

You have an output parameter, not a return value (actually, there is always
a return value, but you aren't using it. Read this to see the difference
between output and return parameters:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.general/msg/2c935bd7c531d82b
adParamOutput is the correct parameter-direction constant.

You might be interested in my parameter code generator available here:
http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator .zip

> cmd.execute

I alwasys tell people to specify the command and execute options argument in
the Execute call, mainly to avoid making ADO guess. But in this case it is
even more critical because you want to avoid the default ADO behavior of
always creating a recordset when calling Execute by specifying
adExecuteNoRecords (128) in the call:

cmd.execute ,,128

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: stored Procedure with argument

wrote in message
news:1176106038.745166.44640@p77g2000hsh.googlegroups.com...
>I am Beginner in ASP
> Problem:I have a table named emp which fields are(id int(4), name
> varchar(50), rollNo int(4))
> I have to just pass the name of the emp and it will just say that
> record is found or Not
>
> My code is :
>
> <%
> nm1 = request.form("txtName")
> Set cmd = Server.CreateObject("adodb.Command")
> cmd.ActiveConnection = con
> cmd.CommandText = "sp_search"
> cmd.CommandType = adCmdStoredProc
> cmd.Parameters.Append cmd.CreateParameter("nm", adchar, adParamInput)

For one thing you've specified the type as adChar, while the stored proc
accepts type varchar. The correct type constant would be [intuitively
named] adVarChar. For another you omitted the length parameter, always
required for variable length types -- and btw, how were you expecting a
value to be sent up with the parameter? You omitted that too. And the
parameter name should be omitted...

cmd.CommandText = "sp_search ?, ?"
cmd.CommandType = adCmdStoredProc
' I hard-coded the value of adVarChar and adParamInput, pending reason
' to believe you've #included adovbs.inc in your ASP, or the typelib in
global.asa
cmd.Parameters.Append cmd.CreateParameter(, 200, 1, Len(nm1), nm1)


> 'Just Above Line no is 19
> cmd.Parameters.Append cmd.CreateParameter("flag",adInteger,
> adParamReturnValue)

More problems, @flag is not a return parameter, it's merely an output
parameter... not sure it will work as below, but it's closer to being
correct than it was, at the very least.

cmd.Parameters.Append cmd.CreateParameter(,3, 2)


> cmd.execute
> x = cmd.Parameters("flag")

You should specify the .Value property, rather than relying on the default
property.


> Response.write (x)
> y = Cint(x)
> if y = 1 then

Why assign it to another variable? The variable y isn't any less a variant
than is x, and the parameter's value is an integer type. Coersion is likely
unnecessary, but even if it wasn't, (CInt(x) = 1) is a perfectly valid
boolean expression... probably a moot point in this case, the response.write
will tell you what you need to know -- assuming your code makes it that far.

-Mark



> %>
>
> <%
> else
> %>
>
> <%
> end if
> %>
>
> My Stored Procedure is
>
> CREATE PROCEDURE sp_search(@nm varchar(50), @flag int output) AS
>
> if exists(select name from emp where name = @nm)
> begin
> select @flag = 1
> return @flag
> end
> else
> begin
> select @flag = 0
> return @flag
> end
> GO
>
> My Error is
> Error Type:
> ADODB.Parameters (0x800A0E7C)
> Parameter object is improperly defined. Inconsistent or incomplete
> information was provided.
> /vkasp/search_rec.asp, line 19
>