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("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]

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



: 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

 

[Bob Barrows]

Ken's given you a good answer, I just wanted to add:

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

 

[Bill Kellaway]

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

 

[Bob Barrows]

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]

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]

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

 

[Bill Kellaway]

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