Help with insert stored procedure

I'm trying to make sure that a user does not allocate more to funds than they have to payments. Here is what my stored procedure looks like now: I listed th error below

ALTER PROCEDURE [dbo].[AddNewFundAllocation]

@.Payment_IDInt,
@.Receipt_IDInt,
@.Fund_IDInt,
@.Amount_allocatedmoney,
@.DateEntereddatetime,
@.EnteredBynvarchar(50)

AS

SELECT (SUM(tblReceiptsFunds.Amount_allocated) +@.Amount_allocated)AStotal_allocations,Sum(tblReceipts.AmountPaid)astotal_payments
FROM tblReceiptsFundsINNERJOIN
tblReceiptsON tblReceiptsFunds.Receipt_ID = tblReceipts.Receipt_ID
WHERE tblReceipts.Payment_ID=@.Payment_ID

IF (total_allocations<total_payments)

INSERT INTO tblReceiptsFunds ([Receipt_ID],[Fund_ID],[Amount_allocated],DateEntered,EnteredBy)Values (@.Receipt_ID,@.Fund_ID,@.Amount_allocated,@.DateEntered,@.EnteredBy)ELSE BEGINPRINT'You are attempting to allocate more to funds than your total payment.'END

I get the following error when I try and save the stored procedure:

Msg 207, Level 16, State 1, Procedure AddNewFundAllocation, Line 26

Invalid column name 'total_allocations'.

Msg 207, Level 16, State 1, Procedure AddNewFundAllocation, Line 26

Invalid column name 'total_payments'.

Try this or something like it. I'm not sure if the data types are correct.

ALTER PROCEDURE [dbo].[AddNewFundAllocation] @.Payment_IDInt,@.Receipt_IDInt,@.Fund_IDInt,@.Amount_allocatedmoney,@.DateEntereddatetime,@.EnteredBynvarchar(50)ASDeclare @.total_allocationsdecimal(18,2)Declare @.total_paymentsdecimal(18,2)SELECT @.total_allocations = (SUM(tblReceiptsFunds.Amount_allocated) + @.Amount_allocated), @.total_payments =Sum(tblReceipts.AmountPaid)FROM tblReceiptsFundsINNERJOIN tblReceiptsON tblReceiptsFunds.Receipt_ID = tblReceipts.Receipt_IDWHERE tblReceipts.Payment_ID=@.Payment_IDIF (@.total_allocations < @.total_payments)INSERT INTO tblReceiptsFunds ([Receipt_ID],[Fund_ID],[Amount_allocated],DateEntered,EnteredBy)Values (@.Receipt_ID,@.Fund_ID,@.Amount_allocated,@.DateEntered,@.EnteredBy)ELSE BEGINPRINT'You are attempting to allocate more to funds than your total payment.'END

|||

John,

That worked with one exception, my doing. Do you know how I could get the PRINT (PRINT'You are attempting to allocate more to funds than your total payment.'
) to show on my web page?

I have a try catch like this:

Try

Con.Open()

intInsertCount = cmdInsert.ExecuteNonQuery()

Con.Close()

gvFundAllocations.DataBind()

Catch exAs Exception

Response.Write(ex.Message)

Finally

Con.Close()

Me.txtFundAmount.Text = 0

Me.FundDrop.SelectedValue =""

EndTry

|||

Yes there are 2 ways. 1 set and output parameter, and 2 have that message return in the result set like this.

ALTER PROCEDURE [dbo].[AddNewFundAllocation] @.Payment_IDInt,@.Receipt_IDInt,@.Fund_IDInt,@.Amount_allocatedmoney,@.DateEntereddatetime,@.EnteredBynvarchar(50)ASDeclare @.total_allocationsdecimal(18,2)Declare @.total_paymentsdecimal(18,2)SELECT @.total_allocations = (SUM(tblReceiptsFunds.Amount_allocated) + @.Amount_allocated), @.total_payments =Sum(tblReceipts.AmountPaid)FROM tblReceiptsFundsINNERJOIN tblReceiptsON tblReceiptsFunds.Receipt_ID = tblReceipts.Receipt_IDWHERE tblReceipts.Payment_ID=@.Payment_IDIF (@.total_allocations < @.total_payments)INSERT INTO tblReceiptsFunds ([Receipt_ID],[Fund_ID],[Amount_allocated],DateEntered,EnteredBy)Values (@.Receipt_ID,@.Fund_ID,@.Amount_allocated,@.DateEntered,@.EnteredBy)Select'All OK!'as messageELSE BEGINSelect'You are attempting to allocate more to funds than your total payment.'as message--PRINT 'You are attempting to allocate more to funds than your total payment.'END

|||

John,

So sorry I just cannot get the message to showup on the web page. Any suggestions?

The stored procedure is doing what it should, if the amount is to high it terminates.

|||

It is not throwing an exception to the web page when the amount is to high, but the stored procedure is terminating. I don't understand this.

|||

The stored procedure is returning a result set. This result being of one row and one column named "message".

A result set can be read using the SqlDataReader or the ExecuteScalar method of the command object.

Do some reading on binding a SqlDataReader to your control, or in using the ExecuteScalar method and you should be OK.

Let me know how you do.