Table column update.

[Ahmed Shahni]

Dear Sir,

Below shown table contained daily CASH BOOK entries in my Accounting application with Date, Details, Debit, Credit....

the Balance Column I have to update end of the month because middle of the month Accounts Clerks may change some figures or corrections.

Date....Desc...............Debit...........Credit........Balance
1/1......Cash Balance.....................................5000
1/1......Sales..............2000...........................
2/1......Purchase.............................2000.......
3/1......Sales..............3000...........................
4/1......Rent...................................1000.......

the above transaction continuing upto 31/1/2011. Now I want to update all the rows at a time for complete month like this.
Cash Balance + Debit - Credit = Balance

I'm using Visual Basic 2010 & Database SQLServer, I hope if you can give SQL command to me then I can complete my project successfully.

Please help me to solve it with written sample code.

[jmcilhinney]

SQL questions have nothing specific to do with VB.NET and belong in the Database Development forum. I've asked the mods to move this thread.

[cicatrix]

As I understand you'll need to add a new record to the table, not update the first one:
Date....Desc...............Debit...........Credit........Balance
31/12....Outstanding Balance..............................7000

To do this you can create a stored procedure, something like this

DECLARE @StartBal Money,
        @TurnOver Money,
	@EndBal Money;

    Select @StartBal = SUM(Balance) FROM [CASH BOOK] WHERE Date='1/1/2011';
    Select @Turnover = SUM(Debit) - SUM(Credit) FROM [CASH BOOK];
    Select @EndBal = @StartBal + @TurnOver;
    Insert Into [CASH BOOK] ([Date], [Desc], [Balance]) VALUES ('12/31/2011','Outstanding balance', @EndBal);
	
    RETURN

[Ahmed Shahni]

Sir thanking you very much...

So now where I have to check to get the answer?

[Ahmed Shahni]

Thank you very much for the code...

Sir same way can you update the BALANCE column itself in the Table(Cash Book) like this....
Date....Desc...............Debit...........Credit........Balance
1/1......Cash Balance.....................................5000
1/1......Sales..............2000...........................7000
2/1......Purchase.............................2000.......5000
3/1......Sales..............3000...........................8000
4/1......Rent...................................1000.......7000

This update row by row in vb 0.6 I did the update using SQL Code While Loop,
With recordsetCashBook
.MoveFirst
tot = .Fields("Balance")
Do While .EOF = False
tot = tot + .Fields("Debit") - .Fields("Credit")
.Fields("Balance") = tot
.MoveNext
Loop
End With

so please try to do & help me.

Very please to you sir.

[cicatrix]

If you're using SQL Server the problem of yours is best dealt with using computed columns.

[Ahmed Shahni]

Sir..

Thanks for code but I could not get idea of that to solve my problem..Sir if you can pls give me sample code of it.... also use the same fields Debit, Credit, Balance for your sample.

Thanks a lot.

[si_the_geek]

Thread moved to 'Database Development' forum - which is where you should always post SQL questions (while SQL can be used in VB, it is certainly not specific to VB)

So now where I have to check to get the answer?

Still this thread, but it is now in the 'Database Development' forum.