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! :)

Recommended Answers

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

Jump to Post

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
Jump to Post

All 6 Replies

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).

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

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

Thanks alot! works perfectly :)

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 learning and sharing knowledge.