locked
sql database with visual basic studio 2008 RRS feed

  • Question

  • I Have a school project on Banking simulation in VB.Net 2008 with SQl. i have two tables, AccountTable and TransactionTable. inside the TransactionTable i have columns like TransactionID, AccountNumber, TransactionType, TransactionAmount, CurrentBalance. now my Ready and  all working Fine But My problem is that i want that

    if TransactionType is = withdrawer Then

    The TransactionAmount should be subtracted from the CurrentBalance

    and return the Answer back in CurrentBalance Column but i dnt know what to do pls i need help!

    Tuesday, May 8, 2012 5:34 PM

Answers

  • Hello..

    in This case you must have two kind of data

    Transactiontype

    1.- income
    2.- Egress

    Your Currentbalance

    Summary (income - Egress)

    e.i

    Select  Sum (Case  When TransactionType = 1  Then  TransactionAmount else  -TransactionAmount end) as CurrentBalance

    From TransactionTable

    Good Luck!!

    Any Question Don't Hesitate to contact me


    Leonardo


    Tuesday, May 8, 2012 6:29 PM
  • SELECT CASE WHEN TransactionType ='withdrawer' THEN CurrentBalance-TransactionAmount  END 

    FROM tbl

    If it did not help please post sample data + desired result


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, May 9, 2012 5:45 AM

All replies

  • Hello..

    in This case you must have two kind of data

    Transactiontype

    1.- income
    2.- Egress

    Your Currentbalance

    Summary (income - Egress)

    e.i

    Select  Sum (Case  When TransactionType = 1  Then  TransactionAmount else  -TransactionAmount end) as CurrentBalance

    From TransactionTable

    Good Luck!!

    Any Question Don't Hesitate to contact me


    Leonardo


    Tuesday, May 8, 2012 6:29 PM
  • SELECT CASE WHEN TransactionType ='withdrawer' THEN CurrentBalance-TransactionAmount  END 

    FROM tbl

    If it did not help please post sample data + desired result


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, May 9, 2012 5:45 AM
  • where should i put the code you just posted? i try putting it inside the form class but it did not work 

    i also try to put it inside the AccountTransactionDataSet    

    Partial Class TransactionDataTable
      SELECT CASE WHEN TransactionType ="withdrawer" THEN CurrentBalance-TransactionAmount  END 
     End Class

     its saying statement can not appear inside a method

    pls this is the project question(task)

    Scenario
    Gillian is a lecturer in Business Studies who would like to demonstrate, to her students, a simplified
    version of a banking system. Hence she would like to have a computer based simulation of such a system.
    Gillian would need to be able to demonstrate a typical screen interface that might be used by a bank
    employee.
    Such an interface would include:
    • Means to access full details of a specific person’s account by entering a bank account number.
    • Means to search for a specific person’s account by entering part or whole of the surname. Such a
    search would be likely to produce a number of matches thus the employee would need to navigate
    these matches to access the required account.
    • Means to display the account holder details and also, display a separate table of all the transactions on
    that account for the last 30 days. (All existing transactions must be read-only but new transactions
    may be entered.) Current balance should be shown.
    • Means to edit some* details of the account holder.
    • Password protected means to close an account.
    • Password protected means to block an account.
    • Password protected means to add a new account.
    The simulation would be driven by a two table database:
    • Account Table to include Account Number, Account Type and Personal Details of Account Holder
    of all accounts.
    (The account number should be automatically generated when a new account is added)
    • Transaction Table to include Transaction Identifier, Account Number, Date of Transaction, Type of
    Transaction and Transaction Amount for all transactions on all accounts.
    (The transaction number should be automatically generated when a new transaction is added)
    *Confined to name, address and contact details. All other details to be read-only once a new account has
    been added
    Aim
    The assignment is to produce the simulation that presents a robust user interface and a reliable
    communication between the user interface and the database.
    Although the recommended textbook gives adequate coverage of the fundamentals of Visual Basic
    any particular assignment may require candidates to investigate other aspects of the language by
    using the Help facility provided by the Integrated Development Environment of Visual Basic or
    other references.

    The first design task would be the structure of the database. The structure of the database may be
    achieved either by using Microsoft Access or SQL Server.
    HOWEVER all processing of data, including the several data validation routines that will be required,
    must be done with VB 2005 program code.
    Populating the database would need to be done via the user interface of the simulation.
    Credit will NOT be given for processing done within the Database Management System.
    The quality of the data validation is critical for the robustness of the simulation.
    The point at which the database tables are updated must be considered carefully.
    The simulation would not have to have a facility to undo a database update but at any stage in the
    interaction with the employee, a CANCEL operation should reset the displays of the situation pertaining
    to the last database update.
    Although the recommended textbook gives adequate coverage of the fundamentals of VB 2005, any
    particular assignment may require candidates to investigate other aspects of the language by using
    the Help facility provided by the Integrated Development Environment of VB 2005.
    Task 1 – 15 Marks
    • Design the database as detailed above.
    • This design must define the tables required and the fields in each table.
    • Set up this design using an appropriate Database management System.
    Task 2 – 10 Marks
    Define the data validation criteria for each field in the database.
    Task 3 – 20 Marks
    Design the user interface required for the simulation.
    (This could involve one or more windows forms and menus)
    This design should include:
    • Controls required
    • Data validation algorithms
    • Navigation algorithms
    • Algorithms for appropriate access to the database.
    Task 4 – 40 Marks
    Implement and Test the design detailed in Task 3.

    Using the user interface of the simulation to populate the database with suitable test data carry out
    complete system testing.
    Evidence of this system testing must include suitable screen shots.
    Task 6 – 5 Marks
    Produce a publishable working copy of a compiled version of the completed assignment together with
    some installation notes. The installation notes should include the system requirements.
    This publishable copy, that includes a setup file, should be on an appropriate medium (zip disk, CDROM,
    DVD etc.).

    what anybody can help me out with here will really be appreciated. thanks 


    remilekun

    Thursday, May 10, 2012 6:16 PM