dcsimg
Results 1 to 8 of 8

Thread: Table column update.

  1. #1

    Thread Starter
    Member Ahmed Shahni's Avatar
    Join Date
    Feb 2011
    Posts
    45

    Lightbulb Table column update.

    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.

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,892

    Re: Table column update.

    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.

  3. #3
    PowerPoster cicatrix's Avatar
    Join Date
    Dec 2009
    Location
    Moscow, Russia
    Posts
    3,654

    Re: Table column update.

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

  4. #4

    Thread Starter
    Member Ahmed Shahni's Avatar
    Join Date
    Feb 2011
    Posts
    45

    Re: Table column update.

    Sir thanking you very much...

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

  5. #5

    Thread Starter
    Member Ahmed Shahni's Avatar
    Join Date
    Feb 2011
    Posts
    45

    Re: Table column update.

    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.

  6. #6

  7. #7

    Thread Starter
    Member Ahmed Shahni's Avatar
    Join Date
    Feb 2011
    Posts
    45

    Re: Table column update.

    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.

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,325

    Re: Table column update.

    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)
    Quote Originally Posted by Ahmed Shahni View Post
    So now where I have to check to get the answer?
    Still this thread, but it is now in the 'Database Development' forum.

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width