473,326 Members | 2,012 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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
%>
<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

Apr 9 '07 #1
2 5650
vi******@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/...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 (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"
Apr 9 '07 #2

<vi******@gmail.comwrote in message
news:11*********************@p77g2000hsh.googlegro 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

Apr 9 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Matt | last post by:
I want to exexute stored procedure in ASP, but it has error "Microsoft VBScript compilation (0x800A0401) Expected end of statement" on line (1). The stored procedure "sp_emp" contain "select *...
7
by: Együd Csaba | last post by:
Hi, I've a problem with some of my stored procs. My config is: RH7.1, Postgres 7.3.2 I had converted a few fields of a few tables from one type to another and after this I made all the...
4
by: Rhino | last post by:
Is it possible for a Java Stored Procedure in DB2 V7.2 (Windows) to pass a Throwable back to the calling program as an OUT parameter? If yes, what datatype should I use when registering the...
8
by: Wonderinguy | last post by:
Hi everybody , I have been trying to execute a simple DB2 stored Procedure from perl. But it doesn't work. Could anybody please help me find out why this is happening : here is my perl script...
28
by: mooreit | last post by:
The purpose for my questions is accessing these technologies from applications. I develop both applications and databases. Working with Microsoft C#.NET and Microsoft SQL Server 2000 Production and...
7
by: Siv | last post by:
Hi, I have a stored procedure that I want to execute and then wait in a loop showing a timer whilst it completes and then carry on once I get notification that it has completed. The main reason...
5
by: wpellett | last post by:
I can not get the SQL compiler to rewrite my SQL UPDATE statement to include columns being SET in a Stored Procedure being called from a BEFORE UPDATE trigger. Example: create table...
9
by: fniles | last post by:
I am using VB.NET 2003 and SQL2000 database. I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1 parameter (varchar(10)) and returns the identity column value from that table....
9
by: Jonathan Wood | last post by:
I've written a stored procedure and would like to filter the results returned based on the argument. It works so far, but I'd also like to allow this argument to be null. I know I can rewrite my...
7
by: jamesclose | last post by:
My problem is this (apologies if this is a little long ... hang in there): I can define a function in VB.NET with optional parameters that wraps a SQL procedure: Sub Test(Optional ByVal Arg1...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.