set @newStudentID = 0 
	set @intErrorCode = 2 
	set @errorMessage = 'test'

	if (@firstname = null OR @surname = null) return
	
	IF NOT EXISTS(SELECT * FROM Student WHERE firstname = @firstname AND surname = @surname and dateOfBirth = @dateOfBirth) BEGIN
		
	BEGIN TRAN
		
		INSERT INTO Student
                    (firstname, surname, nationality, dateOfBirth, title, idNumber, dateOfRegistration)
		VALUES      (@firstname,@surname, @nationality ,@dateOfBirth,@title,@idNumber,@dateOfRegistration)
		
		set @newStudentID = scope_identity()
		/*
		declare @residentialID int
		declare @postalID int
		declare @workPhoneID int
		declare @homePhoneID int
		declare @cellularPhoneID int
		declare @faxNumberID int 
		declare @paymentPlanID int 
		declare @paymentMethodID int 
		declare @selectedPaymentPlanID int 
		declare @selectedPaymentMethodID int 
		declare @newBankBranchID int 
		declare @newBankID int 
		declare @debitOrderAccountID int
		declare @newDebitOrderAccountID int
		*/
		
		set @residentialID = (select dataTypeID from DataType where name like 'residential')	
		set @postalID = (select dataTypeID from DataType where name like 'postal')
		set @workPhoneID = (select dataTypeID from DataType where name like 'work')	
		set @homePhoneID = (select dataTypeID from DataType where name like 'home')	
		set @cellularPhoneID = (select dataTypeID from DataType where name like 'cellular')	
		set @faxNumberID = (select dataTypeID from DataType where name like 'fax')	
		set @paymentPlanID = (SELECT financeEntityID FROM FinanceEntity WHERE (name LIKE N'payment plan'))
		set @paymentMethodID = (SELECT financeEntityID FROM FinanceEntity WHERE (name LIKE N'payment method'))
		set @debitOrderAccountID = (SELECT financeEntityID FROM FinanceEntity WHERE (name LIKE N'debit order account'))
		
		DECLARE @errorTable TABLE (
				error nvarchar(100) )
				
		if not (@residentialAddress is null) 
		
		
		INSERT INTO Address
			  (address, code, studentID, dateTypeID)
		VALUES(@residentialAddress,@residentialAddressCode,@newStudentID,@residentialID)
	
		if not (@postalAddress is null)
		INSERT INTO Address
					(address, code, studentID, dateTypeID)
		VALUES      (@postalAddress,@postalAddresscode,@newStudentID,@postalID)
		
		if not (@workPhone is null)
		INSERT INTO Phone
						(number, code, dataTypeID, studentID)
		VALUES      (@workPhone,@workPhoneCode,@workPhoneID,@newStudentID)
		
		if not (@homePhone is null)
		INSERT INTO Phone
					(number, code, dataTypeID, studentID)
		VALUES      (@homePhone,@homePhoneCode,@homePhoneID,@newStudentID)
	
		if not (@cellularPhone is null)
		INSERT INTO Phone
					(number, dataTypeID, studentID)
		VALUES      (@cellularPhone,@cellularPhoneID,@newStudentID)
			
		if not (@faxNumber is null)
		INSERT INTO Phone
					(number, code, dataTypeID, studentID)
		VALUES      (@faxNumber,@faxNumberCode,@faxNumberID,@newStudentID)
			
		if not (@email is null)
		INSERT INTO Email
					(studentID, address)
		VALUES      (@newStudentID,@email)
		/*
		INSERT INTO Kin
					(name,address,telephone,relationship,studentID)
		VALUES		(@kinName,@kinAddress,@kinPhone,@kinRelationship,@newStudentID)
		*/	
		INSERT INTO StudentFinance
                  (financeEntityID, studentID, entityID)
		VALUES (@paymentPlanID,@newStudentID,@selectedPaymentPlanID)
		
		INSERT INTO StudentFinance
                  (financeEntityID, studentID, entityID)
		VALUES (@paymentMethodID,@newStudentID,@selectedPaymentMethodID)
		
		/* ***STUDENTFINANCE TABLE NEEDS TO BE UPDATED IF DEBIT ACCOUNT IS SELECTED */
		IF (@debitAccountFullName is not null) BEGIN
		declare @x int 
		declare @y int
			/*checks if Debit Account already exists*/
			IF NOT EXISTS(SELECT * FROM DebitAccount WHERE name = @debitAccountFullName AND (bankID = (select bankID from bank where name like @bank)) and accountNumber = @debitOrderAccountNumber) BEGIN
				declare @bankID int
				declare @branchID int
				/*checks if bank info exists, if not create new bank*/
				PRINT 'debit account does not exist'
				IF NOT EXISTS((SELECT * FROM bankBranch WHERE name like @branch AND bankID = (SELECT bankID from Bank WHERE name like @bank) and code like @branchCode)) BEGIN
					/*checks if bank name not already exist as a new bank branch can be being inserted*/
					PRINT 'bank details do not exist'
					IF NOT EXISTS((SELECT * FROM bank where name like @bank)) BEGIN
						PRINT 'bank does not exist'
						INSERT INTO bank 
									(name)
						VALUES (@bank)	
						set @bankID = scope_identity()
					END
					IF NOT EXISTS((SELECT * FROM bankBranch WHERE (bankID = (select bankID from bank where name like @bank)) and name like @branch and code like @branchCode)) BEGIN
						PRINT 'bank branch does not exist'
						set @bankID = (select bankID from bank where name like @bank)
						INSERT INTO bankBranch 
									(bankID,name,code) 
						VALUES (@bankID,@branch,@branchCode)
						set @branchID = scope_identity()
					END
				END
				/*bank exists, find bankID based on branch name and code*/
				ELSE BEGIN
					 SELECT        @bankID = Bank.bankID, @branchID = BankBranch.branchID
					 FROM            Bank INNER JOIN
					                          BankBranch ON Bank.bankID = BankBranch.bankID
					 WHERE        (Bank.name = @bank) AND (BankBranch.name = @branch) AND (BankBranch.code = @branchCode)
				END
				INSERT INTO DebitAccount
							(name,bankID,branchID,accountNumber,date)
				VALUES (@debitAccountFullName,@bankID,@branchID,@debitOrderAccountNumber,@debitAccountDate)
				set @newDebitOrderAccountID = scope_identity()
				INSERT INTO StudentFinance
							(financeEntityID,studentID,entityID)
				VALUES (@debitOrderAccountID,@newStudentID,@newDebitOrderAccountID) 
			END
			ELSE BEGIN
				print 'account exists...go to problem routine'
				set @intErrorCode = -1
				ROLLBACK TRAN
				return
				GOTO PROBLEM
				set @errorMessage = @errorMessage + 'Debit Order Account already exists'
				INSERT INTO @errorTable (error) values (@errorMessage)
				select * from @errorTable
			END
		END
		
		/*inserting credit card details if selected	*/
		IF (@ccAccountNumber is not null) BEGIN
			
		/*checks if credit card accounts exists*/
		
			/*declare @newCreditCardID int
			declare @cardTypeID int 
			declare @ccID int
			declare @ccFinanceEntityID int */
			set @cardTypeID = (SELECT cardTypeID from CreditCardType where creditCard like @creditCard) 
			set @ccBudgetAccountID = (SELECT ccBudgetAccountID from CreditCardBudgetAccount where months = @ccBudgetMonths)
			IF NOT EXISTS(SELECT * FROM CreditCard WHERE cardTypeID = @cardTypeID
							AND accountNumber = @ccAccountNumber AND cvc = @cvc AND expiryDate = @cardExpiryDate
							AND ccBudgetAccountID = (SELECT ccBudgetAccountID from CreditCardBudgetAccount where months = @ccBudgetMonths)) BEGIN
				PRINT 'credit card account does not exist'
				/*checks if new credit card being entered exists*/
		
				IF NOT EXISTS(SELECT cardTypeID from CreditCardType where creditCard like @creditCard) BEGIN
					PRINT 'credit card does not exist'
					IF (@newCreditCard is not null) BEGIN
						/*inserts new Credit Card*/
						PRINT 'inserting new credit card'
						set @creditCard = @newCreditCard
						INSERT INTO CreditCardType 
									(creditCard)
						VALUES      (@newCreditCard)
						set @cardTypeID = scope_identity()
					END																					
				END
				/*credit card exists*/
				/*ELSE BEGIN*/
					PRINT 'inserting credit card account'			
					/*INSERT INTO CreditCardDebitAccount
					            (amount, ccID)
					VALUES      (@ccDebitAccountFee,@cardTypeID)*/
				/*END*/
				/*inserts credit card details in CreditCard table*/
				INSERT INTO CreditCard
					            (cardTypeID, accountNumber, cvc, expiryDate, ccBudgetAccountID)
					VALUES      (@cardTypeID,@ccAccountNumber,@cvc,@cardExpiryDate,@ccBudgetAccountID) 
				set @ccID = scope_identity()
				/*inserts amount to be debited from credit card account*/
				INSERT INTO CreditCardDebitAccount
					            (amount, ccID)
					VALUES      (@ccDebitAccountFee,@ccID)	
				/*select financeEntityID from finance entity table where entity is credit card account
				  inserts the record in StudentFinance table*/
				set @ccFinanceEntityID = (select financeEntityID from FinanceEntity where name like 'credit card account')
				INSERT INTO StudentFinance
							(financeEntityID,studentID,entityID)
					VALUES (@ccFinanceEntityID,@newStudentID,@ccID) 
							
			END						
			ELSE BEGIN
				print 'account exists...go to problem routine'
				set @intErrorCode = -1
				ROLLBACK TRAN
				return
				GOTO PROBLEM
				set @errorMessage = 'Debit Order Account already exists'
				INSERT INTO @errorTable (error) values (@errorMessage)
				select * from @errorTable
			END
		END
				
		
		SELECT @intErrorCode = @@ERROR
		IF (@intErrorCode <> 0) GOTO PROBLEM				
		ELSE BEGIN
		declare @message nvarchar
		set @message = 'test'
		set @errorMessage = (select * from errorTable)
			COMMIT TRAN
			PRINT 'Commiting transaction'
		END
		PROBLEM:
			IF (@intErrorCode <> 0) BEGIN
			print 'problem routine'
			PRINT 'Unexpected error occurred!'
			set @errorMessage =  'Unexpected error occurred!'
			ROLLBACK TRAN
	END -- end of transaction problem clause
END

RETURN

Private Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted
        Dim command As DbCommand
        command = e.Command



        ' The label displays the primary key of the recently inserted roy w.

        'newStudentID = command.Parameters("@newStudentID").Value
        'errorCode = command.Parameters("@intErrorCode").Value
        Dim errorCode As String = command.Parameters("@errorMessage").Value
        Console.WriteLine(errorCode)
        kinNameTBox.Text = "hey there" + errorCode



    End Sub