p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

proasp_codeclinic thread: works in Query Analyzer?


Message #1 by dont worry <aspmailbox@y...> on Fri, 8 Mar 2002 11:50:06 -0800 (PST)
Even with those changes it is not returning any value.
Here is what I have.

PROCEDURE [insert]

(@USER_ID_1	[nvarchar](100),
 @USER_FIRST_NAME_2 		[nvarchar](100),
 @USER_LAST_NAME_3 		[nvarchar](100),
 @USER_START_DATE_10 		[datetime],
 @Progress	 [int]	OUTPUT,
 @USER_ID_NUM_12 [int]	OUTPUT	)

AS SET NOCOUNT ON

SET @Progress = 0


IF EXISTS (SELECT USER_ID FROM USER_TABLE WHERE
USER_ID=@USER_ID_1)
    BEGIN
	SELECT Progress=1,Errormsg='Userid in use.'
    END		
ELSE    
    BEGIN	
	
	INSERT INTO [USER_TABLE] 
		 ( [USER_ID],
		 [USER_FIRST_NAME],
		 [USER_LAST_NAME],
		 [USER_START_DATE],
		 [CREATED_DATE],
		 [CREATED_BY],
		 [MODIFIED_DATE],
		 [MODIFIED_BY]) 
	 
	VALUES 	
		(@USER_ID_1,
		 @USER_FIRST_NAME_2,	
		 @USER_LAST_NAME_3,
		 @USER_START_DATE_10,	
		 getDate(),
		 'Admin',	
		 getDate(),
		 'Admin')	
		
		SELECT @USER_ID_NUM_12 = @@IDENTITY
		SELECT @Progress=0

	    END	    
RETURN	
SET NOCOUNT OFF


Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = dbconn
cmd.CommandText = "insert"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("Param1",
adVarChar, adParamInput, 100)

cmd.Parameters.Append cmd.CreateParameter("Param2",
adVarChar, adParamInput, 100)

cmd.Parameters.Append cmd.CreateParameter("Param3",
adVarChar, adParamInput, 100)

cmd.Parameters.Append cmd.CreateParameter("Param11",
adInteger,adParamReturnValue)

cmd.Parameters.Append cmd.CreateParameter("Param12",
adInteger,adParamReturnValue)

  cmd("Param1") = UCase(USER_ID)
  cmd("Param2") = user_first_name
  cmd("Param3") = user_last_name		
  cmd("Param10") = user_date				
  Progress = cmd("Param11")
  user_id_num = cmd("Param12")
		
  cmd.Execute
  response.write Progress &" <BR>"
  response.write user_id_num

If progress = 0 Then......



--- David Cameron <dcameron@i...> wrote:
> This code is off the top of my head and is untested.
> 
> Proc:
> CREATE PROCEDURE MyProc
> @Progress Int OUTPUT, @ID Int OUTPUT, @IntputFld Int
> AS
> 
> SET NOCOUNT ON
> 
> SET @Progress = 0
> 
> IF (@InputFld IS NOT NULL)
> BEGIN
>     INSERT INTO MyTable VALUES (@InputFld)
>     SET @ID = @@IDENTITY
>     SET @Progress = 0
> END
> 
> ASP:
> Dim cmdAdd
> Dim intMyVal, intID, intProgress
> 
> intMyVal = Request("Value")
> 
> Set cmdAdd = Server.CreateObject("ADODB.Command")
> 
> With cmdAdd
> 	.ActiveConnection = cn
> 	.CommandType = adCmdStoredProc
> 	.CommandText = "MyProc"
> 	.Parameters.Append .CreateParameter("Progress",
> adParamOutput,
> adInteger)
> 	.Parameters.Append .CreateParameter("ID",
> adParamOutput, adInteger)
> 	.Parameters.Append .CreateParameter("Input",
> adParamInput,
> adInteger, , intMyVal)
> 	.Execute
> End With
> 
> 
> intID = cmdAdd.Parameters("Progress")
> intProgress= cmdAdd.Parameters("ID")
> Set cmdAdd = Nothing
> 
> regards
> David Cameron
> nOw.b2b
> dcameron@i...
> 
> -----Original Message-----
> From: dont worry [mailto:aspmailbox@y...]
> Sent: Tuesday, 12 March 2002 1:59 AM
> To: Code Clinic
> Subject: [proasp_codeclinic] RE: works in Query
> Analyzer?
> 
> 
> Please provide, I'm intersted in best practices
> programming.
> 
> Thank you,
> 
> --- David Cameron <dcameron@i...> wrote:
> > You should really be using a parameter to pass
> back
> > the value of @@IDENTITY,
> > and again the value for Progress should also go to
> > an output parameter (or
> > RETURN value). Less overhead than a recordset. It
> > does mean you need to use
> > a command object. I could provide code on request.
> > 
> > regards
> > David Cameron
> > nOw.b2b
> > dcameron@i...
> > 
> > -----Original Message-----
> > From: dont worry [mailto:aspmailbox@y...]
> > Sent: Saturday, 9 March 2002 6:50 AM
> > To: Code Clinic
> > Subject: [proasp_codeclinic] works in Query
> > Analyzer?
> > 
> > 
> > I'm asking to return 2 results sets in an insert.
> > 
> > IF (EXIST....
> > BEGIN
> > INSERT....
> > Select Progress=0
> > Select USER_ID_NUM = @@IDENTITY;
> > END
> > return
> > ++++++
> > in my asp I have 
> > rs("progress")
> > rs("USER_ID_NUM")
> > In query analyzer it gives both results back, but
> my
> > code is giving me the following error.
> > Item cannot be found in collection.....
> > I've tried many combos, whats the problem?
> > 
> > 
> > 
> > __________________________________________________
> > Do You Yahoo!?
> > Try FREE Yahoo! Mail - the world's greatest free
> > email!
> > http://mail.yahoo.com/
> > 
> > $subst('Email.Unsub').
> > 
> > 
> > $subst('Email.Unsub').
> > 
> 
> 
> __________________________________________________
> Do You Yahoo!?
> Try FREE Yahoo! Mail - the world's greatest free
> email!
> http://mail.yahoo.com/
> 
> $subst('Email.Unsub').
> 
> 
> $subst('Email.Unsub').
> 


__________________________________________________
Do You Yahoo!?
Try FREE Yahoo! Mail - the world's greatest free email!
http://mail.yahoo.com/

  Return to Index