ASP / Stored Procedure SQL Insert Help

[Bill Kellaway]

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("AdminR ep")
LTSOffice = Request("LTSOff ice")
Gender = Request("Gender ")
OfficeNum = Request.Cookies ("OfficeNum" )

SET Connect = SERVER.CREATEOB JECT("ADODB.CON NECTION")
Connect.Open = "DATABASE=blah; UID=blah;PWD=bl ah;DSN=blah;"
Connect.Execute ("Insert_LeadSt at")
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_LeadSta t' expects parameter '@OfficeNum', which was not
supplied.
/ltsenroll/leadsource_dbad d.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.app end .createparamete r ...." but I can't seem to get this
to work ...

Thanks in advance

[Ken Schaefer]

Re: ASP / Stored Procedure SQL Insert Help

Set objConn = Server.CreateOb ject("ADODB.Con nection")
objConn.Open ...

Set objCommand = Server.CreateOb ject("ADODB.Com mand")
Set objCommand.Acti veConnection = objConn
With objCommand
.CommandType = adCmdStoredProc
.CommandText = Insert_LeadStat "
.Parameters.App end .CreateParamete r("@OfficeNum ", adInteger,
adParamInput, 4, OfficeNum)
'
' Append other parameters here
'
.Execute
End With

Set objCommand = Nothing
objConn.Close
Set objConn = Nothing

Cheers
Ken



"Bill Kellaway" <billkellaway@h otmail.com> wrote in message
news:f3ae0ca6.0 310092147.35c7f e53@posting.goo gle.com...
: 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("AdminR ep")
: LTSOffice = Request("LTSOff ice")
: Gender = Request("Gender ")
: OfficeNum = Request.Cookies ("OfficeNum" )
:
: SET Connect = SERVER.CREATEOB JECT("ADODB.CON NECTION")
: Connect.Open = "DATABASE=blah; UID=blah;PWD=bl ah;DSN=blah;"
: Connect.Execute ("Insert_LeadSt at")
: 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_LeadSta t' expects parameter '@OfficeNum', which was not
: supplied.
: /ltsenroll/leadsource_dbad d.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.app end .createparamete r ...." but I can't seem to get this
: to work ...
:
: Thanks in advance

[Bob Barrows]

Re: ASP / Stored Procedure SQL Insert Help

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

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

[color=blue]
> Connect.Execute ("Insert_LeadSt at")
> Connect.Close
> Set Connect = Nothing[/color]

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

You did not pass any parameters.
[color=blue]
>
> 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
>[/color]
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]

Re: ASP / Stored Procedure SQL Insert Help

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" <kenREMOVE@THIS adOpenStatic.co m> wrote in message
news:e5fAqkwjDH A.2772@TK2MSFTN GP10.phx.gbl...[color=blue]
> Set objConn = Server.CreateOb ject("ADODB.Con nection")
> objConn.Open ...
>
> Set objCommand = Server.CreateOb ject("ADODB.Com mand")
> Set objCommand.Acti veConnection = objConn
> With objCommand
> .CommandType = adCmdStoredProc
> .CommandText = Insert_LeadStat "
> .Parameters.App end .CreateParamete r("@OfficeNum ", adInteger,
> adParamInput, 4, OfficeNum)
> '
> ' Append other parameters here
> '
> .Execute
> End With
>
> Set objCommand = Nothing
> objConn.Close
> Set objConn = Nothing
>
> Cheers
> Ken
>
>
>
> "Bill Kellaway" <billkellaway@h otmail.com> wrote in message
> news:f3ae0ca6.0 310092147.35c7f e53@posting.goo gle.com...
> : 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("AdminR ep")
> : LTSOffice = Request("LTSOff ice")
> : Gender = Request("Gender ")
> : OfficeNum = Request.Cookies ("OfficeNum" )
> :
> : SET Connect = SERVER.CREATEOB JECT("ADODB.CON NECTION")
> : Connect.Open = "DATABASE=blah; UID=blah;PWD=bl ah;DSN=blah;"
> : Connect.Execute ("Insert_LeadSt at")
> : 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_LeadSta t' expects parameter '@OfficeNum', which was not
> : supplied.
> : /ltsenroll/leadsource_dbad d.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.app end .createparamete r ...." but I can't seem to get this
> : to work ...
> :
> : Thanks in advance
>
>[/color]

[Bob Barrows]

Re: ASP / Stored Procedure SQL Insert Help

Bill Kellaway wrote:[color=blue]
> Thanks Ken !!! works great ..
>
> Took me a bit .. had to include ADOVBS[/color]

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]

Re: ASP / Stored Procedure SQL Insert Help

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" <reb01501@NOyah oo.SPAMcom> wrote in message
news:%23MZPEPAk DHA.3700@TK2MSF TNGP11.phx.gbl. ..[color=blue]
> Bill Kellaway wrote:[color=green]
> > Thanks Ken !!! works great ..
> >
> > Took me a bit .. had to include ADOVBS[/color]
>
> 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
>[/color]
http://www.thrasherwebdesign.com/ind...asp&c=&a=clear[color=blue]
>
> 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:[/color]
you[color=blue]
> 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[/color]
of[color=blue]
> your connection object. Use variables or literal values.
>
> HTH,
> Bob Barrows
>
>[/color]

[Bob Barrows]

Re: ASP / Stored Procedure SQL Insert Help

Bill Kellaway wrote:[color=blue]
> 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 ???[/color]

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.
[color=blue]
> 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.
>[/color]

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

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=<dat a_to_be_inserte d>)
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]

Re: ASP / Stored Procedure SQL Insert Help


"Bob Barrows" <reb01501@NOyah oo.SPAMcom> wrote in message
news:eHxea2AkDH A.2244@TK2MSFTN GP12.phx.gbl...[color=blue]
> Bill Kellaway wrote:[color=green]
> > 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 ???[/color][/color]


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
[color=blue]
> 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[/color]
return[color=blue]
> 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.[/color]
So[color=blue]
> 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[/color]
not[color=blue]
> to use RETURN to return data, except that RETURN can only be used to[/color]
return[color=blue]
> integers. If you need to return other datatypes, you need to use an output
> parameter.
>[color=green]
> > 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.
> >[/color]
>
> Very strange. Did you have a unique index to prevent duplicate inserts?
>[color=green]
> > Might I be able to use a return parameter to prevent duplicate
> > inserts from the ASP page ???[/color]
>
> 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=<dat a_to_be_inserte d>)
> 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
>
>[/color]