How to update specific record in MS Access DB using VB.NET

Question

1.00/5 (1 vote)

See more:

Im creating a library system and part of the system calculates any late fees. Now the late fee that the system calculates for the member needs to be added to the members record, how can i achieve this.

For e.g Member ID 1 has occurred a late fee of £2.40 now this fee must be added to his record which consists of their member id, name, address, telphone number and late fees..

The £2.40 must be update/added in the late fees part of his record

how can this be achieved i cant seem to work it out.

can someone kindly direct me to achieving this

thanks

Posted 29-Feb-12 10:24am

alom_93

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2012-02-29T10:51:00

Solution 1

You don't add it to the members record. You add it to a seperate table, probably called LateFees, and use the members primary key value as the foreign key in the late fees table.

Posted 29-Feb-12 10:51am

Dave Kreskowiak

Comments

alom_93 29-Feb-12 18:28pm

didn't really understand what your saying can you kindly explain...

because the members table consists of member id,name address,tel no and late fees and if some one has a late fee then that value should be added to the late fees section of the record or at least update the current value.

Thanks

Dave Kreskowiak 29-Feb-12 20:24pm

I don't have enough space here to explain the basics of a Relational Database.

First, your LateFees cannot hold more than one value, so you need to tables. One for the member information and another to hold the late fees for every member.

Basically, you have to two related tables. What maintains the relation between them is a Key value. Your first table has your member information (wihtout the latefee column). It also has an extra field that holds an autonumber (a self incrementing value). This field is the Primary Key and is unique for every record in the table.

The second table is your late fees table and has two columns. The first is an ID field an holds the ID number of the member that has a late fee. The second column is the amount of the late fee. When a member has a late fee, you get their ID number from the first table and put it in the ID column (also refered to as the Foreign Key) of the second table, along with the amount of the fee.

If that doesn't explain it, start here: http://office.microsoft.com/en-us/training/build-relationships-for-a-new-access-2007-database-RZ010291798.aspx

alom_93 1-Mar-12 18:28pm

oh ok i understand what your saying.... however it does seem rather long and complicated. isnt there another way of doing this?

Dave Kreskowiak 2-Mar-12 12:16pm

Sure. If you don't want to track individual fees for a member, you get the value of LateFees from the table you have now, add the new fee to the value, then write the new value back to the same record. Oh, you'll still need a Primary Key for each user in order to do this.

If you thought THIS was complicated, you haven't been writing very much code for very long. This is nothing compared to "whats complicated". My largest solo project spanned about 45 seperate classes and took me 3 months to write.

alom_93 2-Mar-12 18:39pm

yeah i have a primary key set to member id so its ok however will i still that new table which has id and late fee??

yeah i am just a beginner

ProEnggSoft · Answer 2 · 2012-02-29T15:30:00

If your requirement is to store only one late fee for each member then you can retrieve the DataRow for the member by the PrimaryKey value i.e. MemberID and set the late fee as below

VB

Dim memberRow As DataRow = membersTable.Rows.Find(memberID)

If memberRow IsNot Nothing Then
    'Store the lateFee calculated in the LateFee field of the member row
    memberRow.Item("LateFee")= lateFee;
End If

You may accept and vote the solution if your problem is solved, otherwise please post your queries.