-
Apr 21st, 2013, 11:52 AM
#1
Thread Starter
Lively Member
How to Update the access table rows with relative rows from another table
Dear All,
I am creating a simple banking application for my college project in VB.net 2010 with access 2003 as back end.
I have created four tables as CashDepositReg, CashDepositTran, CashWidrawReg, CashWidrawTran which is nothing its like register tables and its transactions tables.
If user deposit money it will save in CashDepositReg and its transaction details will save in CashDepositTran
If user withdraw his deposited money those info will save in CashWidrawReg and CashWidrawTran.
now my problem is, if user delete his cash withdraw in CashWidrawReg the withdraw amt should update in CashDepositTran.WidrawAmt against each respective transactions
Table: CashDepositReg
CRegID | CashDepDate | UserID | DepAmt
1 | 09/Mar/2013 | 123 | 10500
Table: CashDepositTran
CTranID | CRegID | CashDepDate | UserID | DepAmt | WidrawAmt
1 | 1 | 09/Mar/2013 | 123 | 10000 | 10000
2 | 1 | 09/Mar/2013 | 123 | 500 | 500
Table: CashWidrawReg
WRegID | CashWidDate | UserID | WidrawAmt
15 | 10/Mar/2013 | 123 | 10500
Table: CashWidrawTran
WTranID | WRegID | CTransID | CashWidDate | UserID | WidrawAmt
17 | 15 | 1 | 10/Mar/2013 | 123 | 10000
18 | 15 | 2 | 10/Mar/2013 | 123 | 500
I use the following query to get the CashWidrawTran details and now i need to pass this output to the update query to update the CashDepositTran.WidrawAmt as 0 because user cancel the particular cash withdraw details in CashWidrawReg
Code:
Dim conn123 As New OleDbConnection("My connection string")
Dim Query As String = "SELECT (B.WidrawAmt) AS test FROM CashWidrawReg A INNER JOIN CashWidrawTran B on A.WRegID = B.WRegID WHERE A.WRegID = 15"
Dim Cmd As New OleDbCommand(Query, conn123)
Dim Dr As OleDbDataReader
Dim outPutString As String = ""
conn123.Open()
Dr = Cmd.ExecuteReader
With Dr
If .HasRows = True Then
While .Read
outPutString += .Item(0).ToString & ","
End While
End If
End With
Dr.Close()
Output
10000,500,
-
Apr 21st, 2013, 11:56 AM
#2
Re: How to Update the access table rows with relative rows from another table
if user delete his cash withdraw in CashWidrawReg
If proper accounting rules are applied (and if this is a banking application they should be) transactions must never be deleted. Any errors must be rebalanced by an appropriate credit or debit.
As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"
Reviews: "dunfiddlin likes his DataTables" - jmcilhinney
Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!
-
Apr 21st, 2013, 11:58 AM
#3
Thread Starter
Lively Member
Re: How to Update the access table rows with relative rows from another table
This is not the pure banking application its just a trade concept and i need this to be done in my app
Originally Posted by dunfiddlin
If proper accounting rules are applied (and if this is a banking application they should be) transactions must never be deleted. Any errors must be rebalanced by an appropriate credit or debit.
-
Apr 21st, 2013, 12:15 PM
#4
Re: How to Update the access table rows with relative rows from another table
Originally Posted by tnncprojects
This is not the pure banking application its just a trade concept and i need this to be done in my app
Not really sure how that makes any difference. The correct way to balance an error is still to offset not to delete. In any case, this ...
Table: CashDepositTran
CTranID | CRegID | CashDepDate | UserID | DepAmt | WidrawAmt
1 | 1 | 09/Mar/2013 | 123 | 10000 | 10000
2 | 1 | 09/Mar/2013 | 123 | 500 | 500
... makes no sense. I see no reason for there to be a Withdraw amount in this table at all, let alone one which matches the Deposit! And why is the deposit amount split into two amounts and given the same CRegID? The only reason I can imagine for such a device would be to keep an account of different paying methods (cash, cheque etc.) in which case this should be registered as two different amounts in the Cash Deposit table with a further column to indicate the nature of the deposit. If it comes to that, what is the purpose of having two tables with identical information at all? If this is meant to be a double entry system you have completely failed to understand how this is done. Otherwise it's just total redundancy.
As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"
Reviews: "dunfiddlin likes his DataTables" - jmcilhinney
Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!
-
Apr 21st, 2013, 12:35 PM
#5
Thread Starter
Lively Member
Re: How to Update the access table rows with relative rows from another table
Thanks for your reply...
In CashDepositTran table i am showing the transaction details for a entry it may be 1 or more entries so i am identifying those trans by CRegID so there will be a ref for the entries in both the tables (CashDepositReg and CashDepositTran) and its easy for me to get the details by comparing CRegID.
WidrawAmt column in CashDepositTran is to know about each transaction status so that I will bind this table in DGV.
And what i am doing is not the banking process, by some logic/requirement i am doing this app...
-
Apr 21st, 2013, 12:42 PM
#6
Re: How to Update the access table rows with relative rows from another table
And what i am doing is not the banking process, by some logic/requirement i am doing this app...
Ah right. I was under the foolish impression that you might want to get a good mark for this assignment. Do forgive me.
As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"
Reviews: "dunfiddlin likes his DataTables" - jmcilhinney
Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!
-
Apr 21st, 2013, 12:56 PM
#7
Thread Starter
Lively Member
Re: How to Update the access table rows with relative rows from another table
Its a kind of application with my college guidance and I regret for saying that this is banking or trade application...
And this also helps me to learn how to over come this scenario...
I am waiting for the solution from this forums...
Thank you all...
-
Apr 23rd, 2013, 12:29 PM
#8
Thread Starter
Lively Member
Re: How to Update the access table rows with relative rows from another table
Any suggestions or advice please...
-
Apr 26th, 2013, 10:01 AM
#9
Thread Starter
Lively Member
Re: How to Update the access table rows with relative rows from another table
Dear all...
After trying and find the below update query but i am not able to achieve what i required.
Code:
Dim conn1 As New OleDbConnection("My Connection String")
Dim query1 As String = "UPDATE CashDepositTran SET CashDepositTran.WidrawAmt = CashDepositTran.WidrawAmt - '" & outPutString & "' WHERE CashDepositTran.CTranID IN (1,2)"
Dim Cmd1 As New OleDbCommand(query1, conn1)
conn1.Open()
With Cmd1
.ExecuteNonQuery()
End With
Cmd1.Dispose()
conn1.Close()
After excuting the UPDATE query its updating by concatenate the output values (mentioned bold in table)
Table: CashDepositTran (Before UPDATE Query)
CTranID | CRegID | CashDepDate | UserID | DepAmt | WidrawAmt
1 | 1 | 09/Mar/2013 | 123 | 10000 | 10000
2 | 1 | 09/Mar/2013 | 123 | 500 | 500
10000500 and updating in each rows as below
Table: CashDepositTran (After UPDATE Query)
CTranID | CRegID | CashDepDate | UserID | DepAmt | WidrawAmt
1 | 1 | 09/Mar/2013 | 123 | 10000 | -9990500
2 | 1 | 09/Mar/2013 | 123 | 500 | -10000000
Last edited by tnncprojects; Apr 28th, 2013 at 09:43 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|