We help IT Professionals succeed at work.
Get Started

Return GUID via Parameters (Classic ASP)

1,469 Views
Last Modified: 2012-05-09
Hi All

Got a nice little script that inserts an email address into a table and creates a GUID which I want to use for the user to validate the email address with.

However, no matter what I do I can't get the GUID back via Parameters (Which I would of thought would be easy to do). The query works fine and outputs the GUID but I can't get the parameters code right.

Here are the parameters (sorry its classic ASP!!!)...

Function AddNewsletterMember(varSID,varTo)
		
	dim cmAddMX: Set cmAddMX = Server.CreateObject("ADODB.Command")
	
	With cmAddMX
		.CommandTimeout = 3600
		.CommandType = adCmdStoredProc
		.CommandText = "sp_addxxx"
		.ActiveConnection = xxx
		
		.Parameters.Append .CreateParameter("@GUID", adVarchar, adParamReturnValue, 150 , intID) '<--- This is wrong I know
		.Parameters.Append .CreateParameter("@SID", adInteger, adParamInput,  ,varSID)
		.Parameters.Append .CreateParameter("@Email", adVarchar, adParamInput, 150 , varTo)
		
		.Execute
	End With
	
	intID = cmAddMX.Parameters.Item(3).Value '<--- This is wrong I know 
	
	Set AddOrder = Nothing
	
	AddNewsletterMember = intID
	
End Function

Open in new window


Here's the stored proc

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[sp_addxxx]
(@SID int,@email varchar(150)) 

AS
BEGIN

	SET NOCOUNT ON;

	DECLARE @GUID uniqueidentifier
	SELECT @GUID = newid() 

	OPEN SYMMETRIC KEY NewsLetterSymmetricKey
	DECRYPTION BY CERTIFICATE Newsletter;

	INSERT INTO [beechData].[dbo].[tb_BH_newsletterMembers]
		(SID,newsletterGUID,EncryptedNewsletterEmail)
		OUTPUT inserted.newsletterGUID
		VALUES (@SID,@GUID,EncryptByKey(Key_GUID('NewsLetterSymmetricKey'), @Email))

		
END

GO

Open in new window


I've tried OUTPUT parameters in the SP which just doesnt do the job.I know I'm missing one step but just can't find the answer anywhere! Your help, as always, would be appreciated.

Regs,
Numb
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE