|
|
Posted by pbd22 on 06/05/07 17:16
On Jun 5, 8:36 am, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> I will try to sketch here the answer to both questions:
>
> 1). To detect that the confirmation code exists, you can check the number of
> rows affected by the insert (using @@rowcount), and then return that value
> to the client using an output parameter. If the number of rows is 1 (I
> assume you have either a primary key or UNIQUE constraint on the
> confirmation code column so duplicates are not possible), then you know you
> had a code match, if 0 then there was no match. Here is an abbreviated code
> of the SP:
>
> CREATE PROCEDURE ConfirmUserRegistration
> @confirmation_cd NVARCHAR(50),
> @numrows INT OUTPUT
> AS
> -- ....
> BEGIN TRAN
>
> INSERT INTO Users
> (egdate,
> pass,
> -- ...
> firstName)
> SELECT regdate,
> pass,
> -- ...
> firstName
> FROM TempRegistration
> WHERE confirm = @confirmation_cd;
>
> SET @numrows = @@rowcount;
>
> DELETE FROM TempRegistration
> WHERE confirm = @confirmation_cd;
>
> COMMIT TRAN;
>
> Note that you can directly perform the DELETE without checking the result of
> the INSERT, because if there is no match then there will be no rows deleted.
> If you want you can have an IF @numrows > 0 before executing the DELETE
> statement to run it only when there is a match.
>
> 2). On your client side, you have to define the output parameter and then
> check the results, abbreviated code here:
>
> '... connection, command and first parameter initialization go here
> ' now add the output parameter
> parameter = command.Parameters.Add( _
> "@numrows",
> SqlDbType.Int)
> parameter.Direction = ParameterDirection.Output
>
> '... open the connection and execute command go here
> ' retrieve the output value
> If (command.Parameters("@numrows").Value = 1) Then
> ' we have a match and confirmation is complete
> Else
> ' confirmation code is invalid - show alert
> End If
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com
Thanks a ton Plamen,
This thread was immensely helpful. I really appreciate it.
As a final note, for anybody that is using this thread for their own
registration system, you need to comment out NOCOUNT ON to
get the appropriate response from the SPROC (at least, I think
that is what solved my "no response" problem).
Thanks again Plamen!
Navigation:
[Reply to this message]
|