retrieving last if on Stored Procedure insert problem

Aleks
Aleks used Ask the Experts™
on
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()

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)
%>

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)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Web Ninja at large
Commented:
remove the following line, that appears at the of your command object definition:

sp_addformsemail.Execute()

Author

Commented:
Never mind ... It worked  :)
Big MontyWeb Ninja at large

Commented:
not quite, I remove the first call to executing the command object, as seen below:



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()

and leave the other execute call as it is. The reason why you're getting no values is because you're calling Execute twice,

Author

Commented:
Yup. Got it !   :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial