Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2009
    Posts
    5

    Unanswered: Calculating date in DetailsView control - ASP.NET

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Consider changing your table def to have a calculated column, or store the calculated column in a view
    Code:
    ALTER TABLE dbo.your_table
      DROP COLUMN A_WeekB4Expiry
    
    ALTER TABLE dbo.your_table
    ADD
       A_WeekB4Expiry As DateAdd(dd, -7, ExpiryDate)
    OR
    Code:
    CREATE VIEW dbo.your_view
      AS
    SELECT ClientName
         , MembershipNo
         , RegistrationDate
         , YearsRegisteredFor
         , ExpiryDate
         , DateAdd(dd, -7, ExpiryDate) As A_WeekB4Expiry
    FROM   dbo.your_table
    GO
    
    SELECT ClientName
         , MembershipNo
         , RegistrationDate
         , YearsRegisteredFor
         , ExpiryDate
         , WeekB4Expiry
    FROM   dbo.your_view
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Overlooked the ExpiryDate being calculated!
    In which case::
    Code:
    CREATE VIEW dbo.your_view
      AS
    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
    GO
    
    SELECT ClientName
         , MembershipNo
         , RegistrationDate
         , YearsRegisteredFor
         , ExpiryDate
         , WeekB4Expiry
    FROM   dbo.your_view
    George
    Home | Blog

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If you want to do it on the application side, have a look at the DateTime class.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2009
    Posts
    5

    Thumbs up Calculating date in DetailsView control - ASP.NET

    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

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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".
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Nov 2009
    Posts
    5

    Calculating date in DetailsView Control - Asp.net

    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
    Last edited by samosamo; 11-18-09 at 06:30.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have you created a view like I suggested above? Querying a view will be lots easier than all those repeated calculations!

    Code:
    FROM   dbo.your_view
    WHERE  A_WeekB4Expiry BETWEEN GetDate() AND DateAdd(dd, 7, GetDate())
    Or
    Code:
    FROM   dbo.your_view
    WHERE  ExpiryDate BETWEEN DateAdd(dd, -7, GetDate()) AND GetDate()
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2009
    Posts
    5
    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

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    Code:
    CREATE VIEW <give_your_view_a_name>
      AS
    George
    Home | Blog

  11. #11
    Join Date
    Nov 2009
    Posts
    5

    SQL Syntax Error

    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,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •