I have a challenge with DetailsView Server Control - ASP.NET.

I have a database table in an SQL Server Express Edition. Below is the table structure:

Column Name | Data Type
ClientName | varchar(50)
MembershipNo | varchar(10)
RegistrationDate | date
YearsRegisteredFor | int
ExpiryDate | date
A_WeekB4Expiry | date

Here is the Challenge:

I have been using DetailsView Control to Insert Data into the database before this time and it has been working but im having difficulty using the DetailsView Control to perform calculation for these column above:

a. ExpiryDate
b. A_WeekB4Expiry
c. Finally, I will create a Pop-up that shows those Clients that their expiry is =<7 i.e. Less or Equal to 7 days (A Week).

Objective:
I want a situation where when user input say -

RegistrationDate = #10/10/2006#
YearsRegisteredFor = 5

I want to perform a calculation that automatically generate the ExpiryDate and A_WeekB4Expiry.

Furthermore, I want to also automatically calculate A_WeekB4Expiry by removing 7 days(A week) from ExpiryDate.

Just an Idea (Hint):
I know that in my VBA Application using MS Access Database all i have to do is use the Event of the textbox control to calculate. For example: DateAdd('yyyy', [YearRegisteredFor], [RegistrationDate]) will give me the ExpiryDate; while, DateAdd('d', -7, [ExpiryDate]) will give me A_WeekB4Expiry.



Honestly, im having difficulties doing this. Please, kindly help me out as i will really appreciate it.

Samson

Consider changing your table def to have a calculated column, or store the calculated column in a view
OR

Overlooked the ExpiryDate being calculated!
In which case::

If you want to do it on the application side, have a look at the DateTime class.

George,

You have made my day. Thank you for the solution. It has done the magic.

Thank you, thank you a million time. This has given me several days of cracking of head and eating of finger nails...

It worked.

But, the only thing I want to do is to do a pop-up. If I am not lucky enough, I will still post this to the forum.

I really appreciate your effort.

Best regards,

Samson

The AjaxControlToolkit has a modal popup that will take care of it for you automatically.

Otherwise look in to javascript hide/show functions. You can place your controls inside a regular old plain <div /> and then use javascript to hide or show that div, effectively presenting a "popup".

George,

Please, i am still having another challenge. I need to get those clients whose their REGISTRATION will be expired in about seven (7) days in the future. It is this that will form my Pop-Up Reminder. I have modified my query and ran it but did not bring result. It only brought NULL values.

Please, Note: that it did not bring any error message. It only brought NULL values. I dont know what to do.

Kindly assist.

Below is the QUERY:

SELECT ClientName
, MembershipNo
, RegistrationDate
, YearsRegisteredFor
, DateAdd(yy, YearsRegisteredFor, RegistrationDate) As ExpiryDate
, DateAdd(dd, -7, DateAdd(yy, YearsRegisteredFor, RegistrationDate)) As A_WeekB4Expiry
FROM dbo.your_table WHERE DateAdd(dd, -7, DateAdd(yy, YearsRegisteredFor, RegistrationDate)) BETWEEN {fn NOW()} AND {fn NOW() } +7)

Best regards,

Samson

Have you created a view like I suggested above? Querying a view will be lots easier than all those repeated calculations!

Or

George,

The problem is I dont know how to create View that i why i am using just SQL Query.

I will quickly check tutorials on creating View and I will try it out.

Presently, the system is reporting error because all of the Calculated fields (ExpiryDate & A_WeekB4Expiry) are not recorgnized as a valid field names.

The solution you sent still did not bring result.

I will try and readup on View and create it.

Thank you very much except you have any other solution. I really appreciate it. God bless.

Samson

In SQL Server Management Studio, click on the new query button and then type up your select query and run it. If this returns what you want then shove these two lines of code at the top of the select statement to "save" the query as a view

Please, I need another assistance in my SQL statement.

I have two tables - (a)RewardSystem table & (b) customer table. I want to perform an INNER JOIN and at the same time GROUP the result with COUNT FUNCTION but i keep getting error message below:

Error Message:
Column 'RewardSystem.id is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Here is my SQL Query:
SELECT RewardSystem.id, RewardSystem.Activity, RewardSystem.DateTimeStamp, RewardSystem.No, customer.lastName + ', ' + customer.firstName AS MemberName, customer.membershipNumber FROM RewardSystem INNER JOIN customer ON RewardSystem.CustomerID = customer.id GROUP BY RewardSystem.CustomerID


Please, what can I do? I cannot GROUP OR DO PERFORM COUNT / SUM Function.

Any assistance will be highly appreciated.

Regards,