Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2009
    Posts
    168

    Unanswered: passing error messages

    Hi,

    I am currently using a stored procedure to make necessary inserts to a my sql server database. If the insert statements are not successful, then I store a string error message in a variable like this:

    set @errorMessage = @errorMessage + 'Debit Order Account already exists'

    I am using vb.net in Visual Web Developer Express 2008 and I would like to get this variable from my vb.net code so that I can output it to the user in the form of a popup menu. Any suggestion how I can do this please? Thanks.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Either use an output parameter or use the return value. Both of those are accessible from a SqlCommand after execution.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jul 2009
    Posts
    168
    Yes the parameter is declared as an output parameter. In which method of the vb.net code should i access the parameter. Can you provide an example of the code you mentioned above? Thanks

  4. #4
    Join Date
    Jul 2009
    Posts
    168
    i cant seem to be able to set this variable as in the above code mentioned. When I retrieve the variable in vb.net as:

    Dim errorCode As String = command.Parameters("@errorMessage").Value
    Console.WriteLine(errorCode)

    it shows NULL? Any idea?

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What's the proc look like, what does the rest of the code look like?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Jul 2009
    Posts
    168
    I mean what is the best way of passing error messages to the vb.net code? What I did is create a temporary table, errorTable, and inserted all the messages in it and trying to pass it to the code. Is that correct? What is the best way of doing it? Thanks


    Code:
    	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

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    how about the signatures for how @errorMessage is declared, and the code that's consuming it? I assume you have a SqlConnection and SqlCommand hanging about somewhere?


    Output variables are a fine way of returning arbitrary strings to a calling method, to answer your question more directly.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Jul 2009
    Posts
    168
    proc declaration : @errorMessage nvarchar output

    vb.net code retrieving the message:
    I have used the sqlDataSource inserted method to retrieve the output parameters from the sqlCommand. As you see in the proc, when i set say the variable @intErrorCode = 2 just for testing, and I retrieve it using the below code, it does work. However if I set

    set @errorMessage = 'test'

    and I try to retrieve it, it gives null.


    Code:
    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
    Last edited by kpeeroo; 04-20-10 at 11:17.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

  10. #10
    Join Date
    Jul 2009
    Posts
    168
    I don't think am using a DataReader object and the direction is set properly, so I am not sure where the problem is coming. Also, it should have made the error when retrieving @intErrorCode as well I suppose. Very strange. Do you have any other methods I can use to raise errors in sprocs and pass on to the vb.net code? Thanks

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You didn't mention checking the datatype and the position of the parameter within the parameters collection...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Jul 2009
    Posts
    168
    Am using VB express 2008 so I added the parameter to the collection list just as normally as I did for all other variables, no problems there as well. I did not use dbCommand.Add(Parameter) thing to add each variable manually. VB Express 2008 does it in its GUI.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •