Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > stored Procedure with argument

Reply
Thread Tools

stored Procedure with argument

 
 
vinodkus@gmail.com
Guest
Posts: n/a
 
      04-09-2007
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
%>
<script language = "JavaScript">
alert("Record Found")
</Script>
<%
else
%>
<script language = "JavaScript">
alert("Record Not Found")
</Script>
<%
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

 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      04-09-2007
http://www.velocityreviews.com/forums/(E-Mail Removed) 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/...935bd7c531d82b
adParamOutput is the correct parameter-direction constant.

You might be interested in my parameter code generator available here:
http://common.mvps.org/barrowsb/Clas..._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 (12 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"


 
Reply With Quote
 
 
 
 
Mark J. McGinty
Guest
Posts: n/a
 
      04-09-2007

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed) ups.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



> %>
> <script language = "JavaScript">
> alert("Record Found")
> </Script>
> <%
> else
> %>
> <script language = "JavaScript">
> alert("Record Not Found")
> </Script>
> <%
> 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
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
procedure as argument in procedure AlexWare VHDL 2 10-23-2009 09:14 AM
Allowing a Stored Procedure Argument to be NULL Jonathan Wood ASP .Net 8 04-30-2008 08:18 PM
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
How to modify default Stored Procedure template in VS.NET database project ES ASP .Net 2 08-20-2004 08:19 PM
New Stored Procedure Template in .Net Sarmad Aljazrawi ASP .Net 0 12-16-2003 11:36 AM



Advertisments