I have a SP that inserts data into a table.
USE [BlueDot]
GO
/****** Object: StoredProcedure [dbo].[Addblueformsemail] Script Date: 10/29/2015 10:44:50 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[Addblueformsemail]
@code1 VARCHAR(10) ,
@code2 VARCHAR(100) ,
@firmid INT ,
@caseid INT ,
@datesent DATETIME ,
@dateexpires DATETIME ,
@dateopened DATETIME ,
@sentby INT ,
@emailfrom VARCHAR(200) ,
@emailto VARCHAR(200) ,
@emailcc VARCHAR(200) ,
@emailbcc VARCHAR(200) ,
@subject VARCHAR(200) ,
@message VARCHAR(1000)
AS
INSERT INTO dbo.blueformsemailed
( code1 ,
code2 ,
firmid ,
caseid ,
datesent ,
dateexpires ,
dateopened ,
sentby ,
emailfrom ,
emailto ,
emailcc ,
emailbcc ,
subject ,
message
)
VALUES ( @code1 ,
@code2 ,
@firmid ,
@caseid ,
@datesent ,
@dateexpires ,
@dateopened ,
@sentby ,
@emailfrom ,
@emailto ,
@emailcc ,
@emailbcc ,
@subject ,
@message
);
SELECT SCOPE_IDENTITY()
Select all Open in new window
Here is the ASP Code:
<!--#SP Insert email information-->
<%
Dim sp_addformsemail__code1
sp_addformsemail__code1 = "0"
if(RandomString() <> "") then sp_addformsemail__code1 = RandomString()
Dim sp_addformsemail__code2
sp_addformsemail__code2 = "0"
if(EncrytPswd(RandomString2()) <> "") then sp_addformsemail__code2 = EncrytPswd(RandomString2())
Dim sp_addformsemail__firmid
sp_addformsemail__firmid = "0"
if(Session("Firmid") <> "") then sp_addformsemail__firmid = Session("Firmid")
Dim sp_addformsemail__caseid
sp_addformsemail__caseid = "0"
if(Request.Querystring("caseid") <> "") then sp_addformsemail__caseid = Request.Querystring("caseid")
Dim sp_addformsemail__datesent
sp_addformsemail__datesent = null
if(now() <> "") then sp_addformsemail__datesent = now()
Dim sp_addformsemail__dateexpires
sp_addformsemail__dateexpires = null
if(Request.Form("expireson") <> "") then sp_addformsemail__dateexpires = Request.Form("expireson")
Dim sp_addformsemail__dateopened
sp_addformsemail__dateopened = null
if(Request.Form("openedon") <> "") then sp_addformsemail__dateopened = Request.Form("openedon")
Dim sp_addformsemail__sentby
sp_addformsemail__sentby = "0"
if(Session("Userid") <> "") then sp_addformsemail__sentby = Session("Userid")
Dim sp_addformsemail__emailfrom
sp_addformsemail__emailfrom = null
if(Request.Form("from") <> "") then sp_addformsemail__emailfrom = Request.Form("from")
Dim sp_addformsemail__emailto
sp_addformsemail__emailto = null
if(Request.Form("txt_To") <> "") then sp_addformsemail__emailto = Request.Form("txt_To")
Dim sp_addformsemail__emailcc
sp_addformsemail__emailcc = null
if(Request.Form("txt_Cc") <> "") then sp_addformsemail__emailcc = Request.Form("txt_Cc")
Dim sp_addformsemail__emailbcc
sp_addformsemail__emailbcc = null
if(Request.Form("txt_Bcc") <> "") then sp_addformsemail__emailbcc = Request.Form("txt_Bcc")
Dim sp_addformsemail__subject
sp_addformsemail__subject = null
if(Request.Form("subject") <> "") then sp_addformsemail__subject = Request.Form("subject")
Dim sp_addformsemail__message
sp_addformsemail__message = null
if(Request.Form("message") <> "") then sp_addformsemail__message = Request.Form("message")
%>
<%
set sp_addformsemail = Server.CreateObject("ADODB.Command")
sp_addformsemail.ActiveConnection = MM_bluedot_STRING
sp_addformsemail.CommandText = "dbo.Addblueformsemail"
sp_addformsemail.CommandType = 4
sp_addformsemail.CommandTimeout = 0
sp_addformsemail.Prepared = true
sp_addformsemail.Parameters.Append sp_addformsemail.CreateParameter("@RETURN_VALUE", 3, 4)
sp_addformsemail.Parameters.Append sp_addformsemail.CreateParameter("@code1", 200, 1,10,sp_addformsemail__code1)
sp_addformsemail.Parameters.Append sp_addformsemail.CreateParameter("@code2", 200, 1,100,sp_addformsemail__code2)
sp_addformsemail.Parameters.Append sp_addformsemail.CreateParameter("@firmid", 3, 1,4,sp_addformsemail__firmid)
sp_addformsemail.Parameters.Append sp_addformsemail.CreateParameter("@caseid", 3, 1,4,sp_addformsemail__caseid)
sp_addformsemail.Parameters.Append sp_addformsemail.CreateParameter("@datesent", 135, 1,20,sp_addformsemail__datesent)
sp_addformsemail.Parameters.Append sp_addformsemail.CreateParameter("@dateexpires", 135, 1,20,sp_addformsemail__dateexpires)
sp_addformsemail.Parameters.Append sp_addformsemail.CreateParameter("@dateopened", 135, 1,20,sp_addformsemail__dateopened)
sp_addformsemail.Parameters.Append sp_addformsemail.CreateParameter("@sentby", 3, 1,4,sp_addformsemail__sentby)
sp_addformsemail.Parameters.Append sp_addformsemail.CreateParameter("@emailfrom", 200, 1,200,sp_addformsemail__emailfrom)
sp_addformsemail.Parameters.Append sp_addformsemail.CreateParameter("@emailto", 200, 1,200,sp_addformsemail__emailto)
sp_addformsemail.Parameters.Append sp_addformsemail.CreateParameter("@emailcc", 200, 1,200,sp_addformsemail__emailcc)
sp_addformsemail.Parameters.Append sp_addformsemail.CreateParameter("@emailbcc", 200, 1,200,sp_addformsemail__emailbcc)
sp_addformsemail.Parameters.Append sp_addformsemail.CreateParameter("@subject", 200, 1,200,sp_addformsemail__subject)
sp_addformsemail.Parameters.Append sp_addformsemail.CreateParameter("@message", 200, 1,1000,sp_addformsemail__message)
sp_addformsemail.Execute()
%>
<!--#repeated region-->
<%
Set rs = sp_addformsemail.Execute()
LASTINSERTEDID = rs(0)
%>
Select all Open in new window
The code below sets the last ID into a variable.
The problem is when I add this last code the Stored Procedure inserts twice ... why .. how to prevent this ?
This is the code that when added it makes a double insert:
Set rs = sp_addformsemail.Execute()
LASTINSERTEDID = rs(0)
Select all Open in new window