|
|
Posted by pbd22 on 06/05/07 14:17
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
[Back to original message]
|