CREATE Procedure UserAdd 
( 
@UserName nvarchar(50), 
@Email nvarchar(50), 
@Password nvarchar(50), 
@Country nvarchar(50), 
@City nvarchar(50), 
@ZipCode nvarchar(50), 
@UserID int OUTPUT 
) 
AS 

BEGIN 

DECLARE 
@message varchar(50) 

SET NOCOUNT ON 

SET @Message = '' 

IF EXISTS (SELECT * FROM tblUser WHERE UserName = @UserName) 
BEGIN 
RAISERROR('The UserID you have chosen is not allowed.', 16, 1) 
SET @Message = 'User ID already in use' 
return 0 
END 


INSERT INTO tblUser 
( 
UserName, 
EMailAddress, 
UserPassword, 
Country, 
City, 
ZipCode 
) 

VALUES 
( 
@UserName, 
@Email, 
@Password, 
@Country, 
@City, 
@ZipCode 
) 

SELECT 
@UserID = @@Identity 

SET NOCOUNT OFF 

return 1 

END 
GO

 Public Function AddUser(ByVal userName As String, ByVal email As String, ByVal userPassword As String, ByVal country As String, ByVal city As String, ByVal zipcode As String) As String

            ' Create Instance of Connection and Command Object

            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

            Dim myCommand As New SqlCommand("UserAdd", myConnection)

            ' Mark the Command as a SPROC

            myCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC

            Dim parameterUserName As New SqlParameter("@UserName", SqlDbType.NVarChar, 50)

            parameterUserName.Value = userName

            myCommand.Parameters.Add(parameterUserName)

            Dim parameterEmail As New SqlParameter("@Email", SqlDbType.NVarChar, 50)

            parameterEmail.Value = email

            myCommand.Parameters.Add(parameterEmail)

            Dim parameterPassword As New SqlParameter("@Password", SqlDbType.NVarChar, 50)

            parameterPassword.Value = userPassword

            myCommand.Parameters.Add(parameterPassword)

            Dim parameterCountry As New SqlParameter("@Country", SqlDbType.NVarChar, 50)

            parameterCountry.Value = country

            myCommand.Parameters.Add(parameterCountry)

            Dim parameterCity As New SqlParameter("@City", SqlDbType.NVarChar, 50)

            parameterCity.Value = city

            myCommand.Parameters.Add(parameterCity)

            Dim parameterZipCode As New SqlParameter("@ZipCode", SqlDbType.NVarChar, 50)

            parameterZipCode.Value = zipcode

            myCommand.Parameters.Add(parameterZipCode)

            Dim parameteruserID As New SqlParameter("@UserID", SqlDbType.Int, 4)

            parameteruserID.Direction = ParameterDirection.Output

            myCommand.Parameters.Add(parameteruserID)

            Dim parametermessage As New SqlParameter("@Message", SqlDbType.NVarChar, 50)

            parametermessage.Direction = ParameterDirection.Output

            myCommand.Parameters.Add(parametermessage)

            Try

               myConnection.Open()
                myCommand.ExecuteNonQuery()
                myConnection.Close()

                ' Calculate the UserID using Output Param from SPROC

                Dim userId As Integer = CInt(parameteruserID.Value)

                Return userId.ToString()

                '  Catch

            Catch SQLExp As SqlException

                'Catch the error and display it.

                HttpContext.Current.Response.Write("An SQL Server Error Occurred: " & SQLExp.ToString())

                HttpContext.Current.Response.Write("An SQL Server Error Occurred: " & parametermessage.Value)

                ' HttpContext.Current.Session("Message") = "An SQL Server Error Occurred: " & SQLExp.ToString()

                '    Dim ErrorMessage As String

                Return parametermessage.ToString()

            End Try
        End Function