stored Procedure with argument

Discussion in 'ASP General' started by vinodkus@gmail.com, Apr 9, 2007.

  1. Guest

    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, 2007
    #1
    1. Advertising

  2. 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.inetserver.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"
     
    Bob Barrows [MVP], Apr 9, 2007
    #2
    1. Advertising

  3. <> wrote in message
    news:...
    >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
    >
     
    Mark J. McGinty, Apr 9, 2007
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Sarmad Aljazrawi

    New Stored Procedure Template in .Net

    Sarmad Aljazrawi, Dec 16, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    552
    Sarmad Aljazrawi
    Dec 16, 2003
  2. ES
    Replies:
    2
    Views:
    1,553
    jgy3183
    Aug 20, 2004
  3. Mike P
    Replies:
    0
    Views:
    3,374
    Mike P
    Jun 19, 2006
  4. Jonathan Wood
    Replies:
    8
    Views:
    357
    Jonathan Wood
    Apr 30, 2008
  5. AlexWare
    Replies:
    2
    Views:
    788
    Paul Uiterlinden
    Oct 23, 2009
Loading...

Share This Page