ASP / Stored Procedure SQL Insert Help

Discussion in 'ASP General' started by Bill Kellaway, Oct 10, 2003.

  1. Hi there - this should be fairly simple for someone. Basically I
    can't figure out how to pass the parameters from ASP to a Stored
    Procedure on SQL.

    Here's my code:

    I just need to help in learning how to pass these varibables from ASP
    to the SP.


    Here's my ASP code ...
    *********************************************************************
    DIM Connect, Q, AdminRep, LTSOffice, Gender, OfficeNum

    AdminRep = Request("AdminRep")
    LTSOffice = Request("LTSOffice")
    Gender = Request("Gender")
    OfficeNum = Request.Cookies("OfficeNum")

    SET Connect = SERVER.CREATEOBJECT("ADODB.CONNECTION")
    Connect.Open = "DATABASE=blah;UID=blah;PWD=blah;DSN=blah;"
    Connect.Execute("Insert_LeadStat")
    Connect.Close
    Set Connect = Nothing
    *********************************************************************
    Gives me this error:
    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
    'insert_LeadStat' expects parameter '@OfficeNum', which was not
    supplied.
    /ltsenroll/leadsource_dbadd.asp, line 22

    Here's my Stored procedure ...
    *********************************************************************
    CREATE PROCEDURE [insert_LeadStat]
    ( @OfficeNum [int],
    @LTSOffice [varchar](50),
    @AdminRep [varchar](50),)
    AS INSERT INTO [MFKProductions].[dbo].[LeadStats]
    ( [OfficeNum],
    [LTSOffice],
    [AdminRep],)
    VALUES
    ( @OfficeNum,
    @LTSOffice,
    @AdminRep,)
    GO

    I've searched through the groups and thought I had it with
    "parameters.append .createparameter ...." but I can't seem to get this
    to work ...

    Thanks in advance
     
    Bill Kellaway, Oct 10, 2003
    #1
    1. Advertising

  2. Bill Kellaway

    Ken Schaefer Guest

    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open ...

    Set objCommand = Server.CreateObject("ADODB.Command")
    Set objCommand.ActiveConnection = objConn
    With objCommand
    .CommandType = adCmdStoredProc
    .CommandText = Insert_LeadStat"
    .Parameters.Append .CreateParameter("@OfficeNum", adInteger,
    adParamInput, 4, OfficeNum)
    '
    ' Append other parameters here
    '
    .Execute
    End With

    Set objCommand = Nothing
    objConn.Close
    Set objConn = Nothing

    Cheers
    Ken



    "Bill Kellaway" <> wrote in message
    news:...
    : Hi there - this should be fairly simple for someone. Basically I
    : can't figure out how to pass the parameters from ASP to a Stored
    : Procedure on SQL.
    :
    : Here's my code:
    :
    : I just need to help in learning how to pass these varibables from ASP
    : to the SP.
    :
    :
    : Here's my ASP code ...
    : *********************************************************************
    : DIM Connect, Q, AdminRep, LTSOffice, Gender, OfficeNum
    :
    : AdminRep = Request("AdminRep")
    : LTSOffice = Request("LTSOffice")
    : Gender = Request("Gender")
    : OfficeNum = Request.Cookies("OfficeNum")
    :
    : SET Connect = SERVER.CREATEOBJECT("ADODB.CONNECTION")
    : Connect.Open = "DATABASE=blah;UID=blah;PWD=blah;DSN=blah;"
    : Connect.Execute("Insert_LeadStat")
    : Connect.Close
    : Set Connect = Nothing
    : *********************************************************************
    : Gives me this error:
    : Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    : [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
    : 'insert_LeadStat' expects parameter '@OfficeNum', which was not
    : supplied.
    : /ltsenroll/leadsource_dbadd.asp, line 22
    :
    : Here's my Stored procedure ...
    : *********************************************************************
    : CREATE PROCEDURE [insert_LeadStat]
    : ( @OfficeNum [int],
    : @LTSOffice [varchar](50),
    : @AdminRep [varchar](50),)
    : AS INSERT INTO [MFKProductions].[dbo].[LeadStats]
    : ( [OfficeNum],
    : [LTSOffice],
    : [AdminRep],)
    : VALUES
    : ( @OfficeNum,
    : @LTSOffice,
    : @AdminRep,)
    : GO
    :
    : I've searched through the groups and thought I had it with
    : "parameters.append .createparameter ...." but I can't seem to get this
    : to work ...
    :
    : Thanks in advance
     
    Ken Schaefer, Oct 10, 2003
    #2
    1. Advertising

  3. Bill Kellaway

    Bob Barrows Guest

    Ken's given you a good answer, I just wanted to add:
    Bill Kellaway wrote:
    > Connect.Open = "DATABASE=blah;UID=blah;PWD=blah;DSN=blah;"


    You should be using the native OLEDB provider for SQL: the ODBC provider has
    been deprecated by Microsoft. See www.connectionstrings.com


    > Connect.Execute("Insert_LeadStat")
    > Connect.Close
    > Set Connect = Nothing



    > *********************************************************************
    > Gives me this error:
    > Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    > [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
    > 'insert_LeadStat' expects parameter '@OfficeNum', which was not
    > supplied.
    > /ltsenroll/leadsource_dbadd.asp, line 22


    You did not pass any parameters.

    >
    > Here's my Stored procedure ...
    > *********************************************************************
    > CREATE PROCEDURE [insert_LeadStat]
    > ( @OfficeNum [int],
    > @LTSOffice [varchar](50),
    > @AdminRep [varchar](50),)
    > AS INSERT INTO [MFKProductions].[dbo].[LeadStats]
    > ( [OfficeNum],
    > [LTSOffice],
    > [AdminRep],)
    > VALUES
    > ( @OfficeNum,
    > @LTSOffice,
    > @AdminRep,)
    > GO
    >

    If you want to use a Command object to run this procedure, you may want to
    give my free Stored Procedure Call Code Generator a try. It's available at
    http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear

    However, your procedure has no ouptut parameters and you do not seem to be
    interested in the Return parameter, so you do not need a Command object: you
    can use the stored-procedure-as-connection-method technique:

    dim offnum, lts, admin
    offnum = ...
    lts = "..."
    admin = "..."
    Connect.insert_LeadStat offnum, lts,admin

    Pass the parameter values just as if insert_LeadStat was a native method of
    your connection object. Use variables or literal values.

    HTH,
    Bob Barrows
     
    Bob Barrows, Oct 10, 2003
    #3
  4. Thanks Ken !!! works great ..

    Took me a bit .. had to include ADOVBS and then took awhile to the the
    correct verbiage for adVarchars ...

    Thanks again ...

    "Ken Schaefer" <> wrote in message
    news:...
    > Set objConn = Server.CreateObject("ADODB.Connection")
    > objConn.Open ...
    >
    > Set objCommand = Server.CreateObject("ADODB.Command")
    > Set objCommand.ActiveConnection = objConn
    > With objCommand
    > .CommandType = adCmdStoredProc
    > .CommandText = Insert_LeadStat"
    > .Parameters.Append .CreateParameter("@OfficeNum", adInteger,
    > adParamInput, 4, OfficeNum)
    > '
    > ' Append other parameters here
    > '
    > .Execute
    > End With
    >
    > Set objCommand = Nothing
    > objConn.Close
    > Set objConn = Nothing
    >
    > Cheers
    > Ken
    >
    >
    >
    > "Bill Kellaway" <> wrote in message
    > news:...
    > : Hi there - this should be fairly simple for someone. Basically I
    > : can't figure out how to pass the parameters from ASP to a Stored
    > : Procedure on SQL.
    > :
    > : Here's my code:
    > :
    > : I just need to help in learning how to pass these varibables from ASP
    > : to the SP.
    > :
    > :
    > : Here's my ASP code ...
    > : *********************************************************************
    > : DIM Connect, Q, AdminRep, LTSOffice, Gender, OfficeNum
    > :
    > : AdminRep = Request("AdminRep")
    > : LTSOffice = Request("LTSOffice")
    > : Gender = Request("Gender")
    > : OfficeNum = Request.Cookies("OfficeNum")
    > :
    > : SET Connect = SERVER.CREATEOBJECT("ADODB.CONNECTION")
    > : Connect.Open = "DATABASE=blah;UID=blah;PWD=blah;DSN=blah;"
    > : Connect.Execute("Insert_LeadStat")
    > : Connect.Close
    > : Set Connect = Nothing
    > : *********************************************************************
    > : Gives me this error:
    > : Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    > : [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
    > : 'insert_LeadStat' expects parameter '@OfficeNum', which was not
    > : supplied.
    > : /ltsenroll/leadsource_dbadd.asp, line 22
    > :
    > : Here's my Stored procedure ...
    > : *********************************************************************
    > : CREATE PROCEDURE [insert_LeadStat]
    > : ( @OfficeNum [int],
    > : @LTSOffice [varchar](50),
    > : @AdminRep [varchar](50),)
    > : AS INSERT INTO [MFKProductions].[dbo].[LeadStats]
    > : ( [OfficeNum],
    > : [LTSOffice],
    > : [AdminRep],)
    > : VALUES
    > : ( @OfficeNum,
    > : @LTSOffice,
    > : @AdminRep,)
    > : GO
    > :
    > : I've searched through the groups and thought I had it with
    > : "parameters.append .createparameter ...." but I can't seem to get this
    > : to work ...
    > :
    > : Thanks in advance
    >
    >
     
    Bill Kellaway, Oct 11, 2003
    #4
  5. Bill Kellaway

    Bob Barrows Guest

    Bill Kellaway wrote:
    > Thanks Ken !!! works great ..
    >
    > Took me a bit .. had to include ADOVBS


    http://www.aspfaq.com/show.asp?id=2112

    Just to make sure you did not miss this:

    If you want to use a Command object to run this procedure, you may want to
    give my free Stored Procedure Call Code Generator a try. It's available at
    http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear

    However, your procedure has no ouptut parameters and you do not seem to be
    interested in the Return parameter, so you do not need a Command object: you
    can use the stored-procedure-as-connection-method technique:

    dim offnum, lts, admin
    offnum = ...
    lts = "..."
    admin = "..."
    Connect.insert_LeadStat offnum, lts,admin

    Pass the parameter values just as if insert_LeadStat was a native method of
    your connection object. Use variables or literal values.

    HTH,
    Bob Barrows
     
    Bob Barrows, Oct 11, 2003
    #5
  6. Thanks Bob,

    I'm not sure what the Return Parameter does. I'll take a guess though -
    please correct me if I'm wrong - It's SQL's way of telling the Command
    object if the command was successful or not ??? If so, this would be very
    helpful to me. The reason that I changed this page from ADO to a SP Insert
    was that I was getting intermittant duplicate inserts. Rebooting SQL
    seemed to help for awhile.

    Might I be able to use a return parameter to prevent duplicate inserts from
    the ASP page ???

    Thanks again all ...

    Bill



    "Bob Barrows" <> wrote in message
    news:%...
    > Bill Kellaway wrote:
    > > Thanks Ken !!! works great ..
    > >
    > > Took me a bit .. had to include ADOVBS

    >
    > http://www.aspfaq.com/show.asp?id=2112
    >
    > Just to make sure you did not miss this:
    >
    > If you want to use a Command object to run this procedure, you may want to
    > give my free Stored Procedure Call Code Generator a try. It's available at
    >

    http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear
    >
    > However, your procedure has no ouptut parameters and you do not seem to be
    > interested in the Return parameter, so you do not need a Command object:

    you
    > can use the stored-procedure-as-connection-method technique:
    >
    > dim offnum, lts, admin
    > offnum = ...
    > lts = "..."
    > admin = "..."
    > Connect.insert_LeadStat offnum, lts,admin
    >
    > Pass the parameter values just as if insert_LeadStat was a native method

    of
    > your connection object. Use variables or literal values.
    >
    > HTH,
    > Bob Barrows
    >
    >
     
    Bill Kellaway, Oct 11, 2003
    #6
  7. Bill Kellaway

    Bob Barrows Guest

    Bill Kellaway wrote:
    > Thanks Bob,
    >
    > I'm not sure what the Return Parameter does. I'll take a guess
    > though - please correct me if I'm wrong - It's SQL's way of telling
    > the Command object if the command was successful or not ???


    Close. The Return parameter contains the value returned by a RETURN
    statement in your SP. If you do not have a RETURN statement, a successful
    procedure will return 0, while a procedure that raises an error will return
    NULL.

    There are 3 ways to return values from a SQL Server stored procedure:
    1. a Select statement that returns a resultset
    --run this script in Query Analyzer (QA):
    Create Procedure SelectValue
    (@input int)
    AS
    Select @input + 5
    go
    exec SelectValue 10
    go
    drop procedure SelectValue


    2. a Return parameter:
    --run this script in QA:
    create procedure ReturnValue
    (@input int)
    AS
    Return @input + 5
    go
    declare @returnvalue int
    exec @returnvalue = ReturnValue 10
    select @returnvalue
    go
    drop procedure ReturnValue

    3. an Output Parameter:
    --run this script in QA:
    create procedure OutputValue
    (@input int output)
    AS
    SET @input = @input + 5
    go
    declare @outputvalue int
    SET @outputvalue = 10
    exec OutputValue @outputvalue output
    select @outputvalue
    go
    drop procedure OutputValue


    I do not recommend method 1 for returning a single value. A resultset is
    expensive to build, in that it must contain metadata in addition to data. So
    more network traffic is created, and the client app needs to expend more
    resources in order to retrieve and expose the resultset to the calling
    procedure.

    Most developers use the Return parameter to return status codes instead of
    data. This is for the sake of consistency: there is no technical reason not
    to use RETURN to return data, except that RETURN can only be used to return
    integers. If you need to return other datatypes, you need to use an output
    parameter.

    > If so,
    > this would be very helpful to me. The reason that I changed this
    > page from ADO to a SP Insert was that I was getting intermittant
    > duplicate inserts. Rebooting SQL seemed to help for awhile.
    >


    Very strange. Did you have a unique index to prevent duplicate inserts?

    > Might I be able to use a return parameter to prevent duplicate
    > inserts from the ASP page ???


    Yes, but you don't have to. You can use EXISTS in your stored procedure to
    do this without raising an error:

    IF NOT EXISTS
    (Select * from sometable
    where somecolumn=<data_to_be_inserted>)
    BEGIN
    INSERT sometable ...
    END
    --optionally - do this only if you want your client app to know
    ELSE
    BEGIN
    RETURN 2
    --code which you create to designate that record exists
    END

    HTH,
    Bob Barrows
     
    Bob Barrows, Oct 11, 2003
    #7
  8. "Bob Barrows" <> wrote in message
    news:...
    > Bill Kellaway wrote:
    > > Thanks Bob,
    > >
    > > I'm not sure what the Return Parameter does. I'll take a guess
    > > though - please correct me if I'm wrong - It's SQL's way of telling
    > > the Command object if the command was successful or not ???



    Ah .. of course ... a select statement would have to have return values
    .... not required with an Insert ...

    So .. how might I access this return value of 0 from ASP ?

    Also .. no .. I have no index's on that table. This table used to count
    phone calls. If you were to index that table what field would you index ?

    Thanks again for your great help ...

    Bill

    > Close. The Return parameter contains the value returned by a RETURN
    > statement in your SP. If you do not have a RETURN statement, a successful
    > procedure will return 0, while a procedure that raises an error will

    return
    > NULL.
    >
    > There are 3 ways to return values from a SQL Server stored procedure:
    > 1. a Select statement that returns a resultset
    > --run this script in Query Analyzer (QA):
    > Create Procedure SelectValue
    > (@input int)
    > AS
    > Select @input + 5
    > go
    > exec SelectValue 10
    > go
    > drop procedure SelectValue
    >
    >
    > 2. a Return parameter:
    > --run this script in QA:
    > create procedure ReturnValue
    > (@input int)
    > AS
    > Return @input + 5
    > go
    > declare @returnvalue int
    > exec @returnvalue = ReturnValue 10
    > select @returnvalue
    > go
    > drop procedure ReturnValue
    >
    > 3. an Output Parameter:
    > --run this script in QA:
    > create procedure OutputValue
    > (@input int output)
    > AS
    > SET @input = @input + 5
    > go
    > declare @outputvalue int
    > SET @outputvalue = 10
    > exec OutputValue @outputvalue output
    > select @outputvalue
    > go
    > drop procedure OutputValue
    >
    >
    > I do not recommend method 1 for returning a single value. A resultset is
    > expensive to build, in that it must contain metadata in addition to data.

    So
    > more network traffic is created, and the client app needs to expend more
    > resources in order to retrieve and expose the resultset to the calling
    > procedure.
    >
    > Most developers use the Return parameter to return status codes instead of
    > data. This is for the sake of consistency: there is no technical reason

    not
    > to use RETURN to return data, except that RETURN can only be used to

    return
    > integers. If you need to return other datatypes, you need to use an output
    > parameter.
    >
    > > If so,
    > > this would be very helpful to me. The reason that I changed this
    > > page from ADO to a SP Insert was that I was getting intermittant
    > > duplicate inserts. Rebooting SQL seemed to help for awhile.
    > >

    >
    > Very strange. Did you have a unique index to prevent duplicate inserts?
    >
    > > Might I be able to use a return parameter to prevent duplicate
    > > inserts from the ASP page ???

    >
    > Yes, but you don't have to. You can use EXISTS in your stored procedure to
    > do this without raising an error:
    >
    > IF NOT EXISTS
    > (Select * from sometable
    > where somecolumn=<data_to_be_inserted>)
    > BEGIN
    > INSERT sometable ...
    > END
    > --optionally - do this only if you want your client app to know
    > ELSE
    > BEGIN
    > RETURN 2
    > --code which you create to designate that record exists
    > END
    >
    > HTH,
    > Bob Barrows
    >
    >
     
    Bill Kellaway, Oct 12, 2003
    #8
    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. EmJayEm
    Replies:
    4
    Views:
    4,155
    EmJayEm
    Jan 11, 2005
  2. Quinet, Joel
    Replies:
    0
    Views:
    491
    Quinet, Joel
    Aug 8, 2003
  3. Replies:
    3
    Views:
    13,960
    raghav
    Sep 6, 2006
  4. JB
    Replies:
    2
    Views:
    918
    Göran Andersson
    Mar 5, 2010
  5. sm2010
    Replies:
    0
    Views:
    340
    sm2010
    Apr 2, 2010
Loading...

Share This Page