Posted by pbd22 on 06/05/07 14:50

On Jun 5, 7:17 am, pbd22 <dush...@gmail.com> wrote:
> On Jun 4, 8:15 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
>
>
>
> > Ok, now it is more clear what you are trying to do... :)
>
> > Yes, stored procedure is best here, as it can reuse previously cached
> > execution plan. Perhaps something like this:
>
> > CREATE PROCEDURE ConfirmUserRegistration
> > @confirmation_cd NVARCHAR(50)
> > AS
>
> > SET NOCOUNT ON;
>
> > BEGIN TRY
>
> > BEGIN TRAN
>
> > INSERT INTO Users
> > (egdate,
> > pass,
> > role,
> > squestion,
> > sanswer,
> > zcode,
> > altemail,
> > email,
> > bdaymonth,
> > bdayday,
> > bdayyear,
> > gender,
> > sitename,
> > city,
> > state,
> > country,
> > lastName,
> > firstName)
> > SELECT regdate,
> > pass,
> > role,
> > squestion,
> > sanswer,
> > zcode,
> > altemail,
> > email,
> > bdaymonth,
> > bdayday,
> > bdayyear,
> > gender,
> > sitename,
> > city,
> > state,
> > country,
> > lastName,
> > firstName
> > FROM TempRegistration
> > WHERE confirm = @confirmation_cd;
>
> > COMMIT TRAN;
>
> > END TRY
> > BEGIN CATCH
>
> > IF (XACT_STATE()) = -1
> > BEGIN
> > ROLLBACK TRAN;
> > END
> > ELSE IF (XACT_STATE()) = 1
> > BEGIN
> > COMMIT TRAN;
> > END
>
> > DECLARE
> > @ErrorMessage NVARCHAR(4000),
> > @ErrorSeverity INT,
> > @ErrorState INT,
> > @ErrorNumber INT,
> > @ErrorLine INT,
> > @ErrorProcedure NVARCHAR(200),
> > @ErrMessage NVARCHAR(4000);
>
> > SELECT
> > @ErrorMessage = ERROR_MESSAGE(),
> > @ErrorSeverity = ERROR_SEVERITY(),
> > @ErrorState = ERROR_STATE(),
> > @ErrorNumber = ERROR_NUMBER(),
> > @ErrorLine = ERROR_LINE(),
> > @ErrorProcedure = COALESCE(ERROR_PROCEDURE(), '-');
>
> > SET @ErrMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
> > +
> > N'Message: '+ ERROR_MESSAGE();
>
> > RAISERROR(
> > @ErrMessage,
> > @ErrorSeverity,
> > 1,
> > @ErrorNumber,
> > @ErrorSeverity,
> > @ErrorState,
> > @ErrorProcedure,
> > @ErrorLine
> > );
>
> > END CATCH;
>
> > GO
>
> > Then in your code call the SP like this (I just typed here, please check for
> > syntax, I've been using more C# lately and could be missing something):
>
> > Using connection As New
> > SqlConnection(ConfigurationManager.ConnectionStrings("myConnStr").ConnectionString)
>
> > Try
> > Dim command As SqlCommand = New SqlCommand( _
> > "ConfirmUserRegistration",
> > connection)
> > command.CommandType = CommandType.StoredProcedure
>
> > Dim parameter As SqlParameter = command.Parameters.Add( _
> > "@confirmation_cd ",
> > SqlDbType.NVarChar, _
> > 50)
> > parameter.Value = "8c37a0737eegd64532rgdf56g5ec3f75aab5d9e7a712077"
>
> > command.Connection.Open()
> > command.ExecuteNonQuery()
>
> > Catch exSQL As SqlException
> > ' Log and show error
>
> > Catch exGen As Exception
> > ' Log and show error
>
> > End Try
>
> > End Using
>
> > HTH,
>
> > Plamen Ratchevhttp://www.SQLStudio.com
>
> Thanks again Plamen. This thread has been very helpful.
>
> I have a final question. How do I handle cases where the confirmation
> code
> doesn't exist? Say a user is trying to guess a code - How would the
> stored procedure catch a mismatch and return the result to VB.NET so
> the appropriate message can be sent to the user?
>
> Thanks again for all your help.
> Peter


Actually, I have a bit of an addition to the above "final
question" :) .
I am also wondering where in the SPROC that you have provided
I could place a confirmation that the insert statement has happened
successfully? Or, how do I include a check within the SPROC to
verify successful insertion? I ask because, once the data has been
successfully moved from the Temp table to the Users table, I will need
to delete the source row in the Temp table. I can figure out how to
code the deletion but am not quite sure how the "onSuccess" statement
looks that indicates that it is OK to go ahead and delete the row.

Thanks again!

[Back to original message]