Member Avatar for BigPandaCake

What I'm working on is a video rental project in visual basic 2008.
I have a local database which contains info on DVDs, Members and a table for current rentals.

The columns i have in the current rentals table are:
MemberID | DVDID | DaysOnRent | DateRented | DueDate | Overdue | TotalPrice

I have this data displayed in a datagridview, but the user adds it through a seperate form (called add a rental) with a 'details' view of the table on it. I've written code to automatically determine the TotalPrice and DueDate of the DVD in the add a rental form.

The Problem:
I don't know how to get the database to automatically detect when a DVD is overdue (when todays date is greater than the DueDate).

I want it to check it in the column "Overdue" (which is boolean)

..so basically what I'm asking is how do i update the overdue column automatically depending on whats in the DueDate


I'm fairly new to programming and just can't figure out what to do here.. any help would be greatly appreciated! :)

  • 2 Contributors
  • 6 Replies
  • 185 Views
  • 3 Weeks Discussion Span
  • Latest Post Latest Post by BigPandaCake
Member Avatar for kvprajapati

Welcome to the Daniweb.

You didn't mention the name of database product (I guess! it is MS-SQL server).

>I don't know how to get the database to automatically detect when a DVD is overdue

You can take the advantage of computed column specification (MS-SQL server).

Member Avatar for BigPandaCake

yeah I'm using ms-sql server compact, but i still don't really understand how to do this.. sorry I'm really new to working with SQL lol.

Im using VB 2008 Express Edition.. and in the edit table schema form there is no section for adding a computed column specification :/

Member Avatar for kvprajapati

MS-SQL compact doesn't support computed column but you may use select statement which evaluates overdue value.

SELECT 
   MemberID, DVDID, DaysOnRent,DateRented,DueDate, TotalPrice,
   CASE WHEN GETDATE() > DUEDATE THEN 0 ELSE 1 END AS OVERDUE 
FROM  Rentals
Member Avatar for BigPandaCake

Thanks alot! works perfectly :)

Member Avatar for BigPandaCake

Having a problem.. for some reason when i save the project and open it up again, the SQL Code i typed for that table dissapears :S

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.