Professional Web Applications Themes

Extrend SP to check for Expiry date prior to updating? - ASP Database

Is it possible to extend this User Login SP to return a error if the column u_ExpiryDate is < Todays Date and based on this send him to an registration-update page.....? I have included sample: SP, table, an calling asp SP code: (1) - Stored Procedure SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO Create Procedure sp_logonuser u_name varchar(50), u_password varchar(12) As set nocount on BEGIN UPDATE t_user SET u_lastDate = GETDATE(), u_logcount = u_logcount + 1 WHERE (u_name = u_name) AND (u_password = u_password) select * from t_user where u_name=u_name and u_password=u_password return GO SET QUOTED_IDENTIFIER OFF GO SET ...

  1. #1

    Default Extrend SP to check for Expiry date prior to updating?

    Is it possible to extend this User Login SP to return a error if the column
    u_ExpiryDate is < Todays Date and based on this send him to an
    registration-update page.....?
    I have included sample: SP, table, an calling asp SP code:

    (1) - Stored Procedure
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO


    Create Procedure sp_logonuser
    u_name varchar(50),
    u_password varchar(12)
    As
    set nocount on
    BEGIN

    UPDATE t_user SET u_lastDate = GETDATE(), u_logcount = u_logcount + 1 WHERE
    (u_name = u_name) AND (u_password = u_password)
    select * from t_user where u_name=u_name and u_password=u_password
    return

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO




    (2) - TABLE: t_user
    ----------------
    REATE TABLE [t_user] (
    [u_id] [int] IDENTITY (1, 1) NOT NULL ,
    [u_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [u_password] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [u_firstname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    ,
    [u_lastname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [u_email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [u_logcount] [int] NOT NULL CONSTRAINT [DF_t_user_u_logcount] DEFAULT (1),
    [u_createDate] [datetime] NOT NULL CONSTRAINT [DF_t_user_u_createDate]
    DEFAULT (getdate()),
    [u_lastDate] [datetime] NOT NULL CONSTRAINT [DF_t_user_u_lastDate] DEFAULT
    (getdate()),
    [u_SecurityLevel] [int] NULL ,
    [u_SourceID] [int] NULL ,
    [u_YachtPlacementID] [int] NULL ,
    [u_NationID] [int] NULL ,
    [u_StateID] [int] NULL ,
    [u_BrokerID] [int] NULL ,
    [u_ExpiryDate] [datetime] NULL ,
    [u_Comment] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [u_CurrentURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [u_FromURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [u_VisitorIP] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [u_AcceptCookies] [int] NULL ,
    [u_AcceptEmail] [int] NULL ,
    [u_EmailOK] [int] NULL ,
    [u_Browser] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [u_OS] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [u_TimeFrame] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [u_IDStatus] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_t_user_name] PRIMARY KEY NONCLUSTERED
    (
    [u_id]
    ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
    CONSTRAINT [IX_t_user_name] UNIQUE NONCLUSTERED
    (
    [u_name]
    ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
    CONSTRAINT [FK_t_user_Broker] FOREIGN KEY
    (
    [u_BrokerID]
    ) REFERENCES [Broker] (
    [BrokerID]
    ) ON UPDATE CASCADE ,
    CONSTRAINT [FK_t_user_Nation] FOREIGN KEY
    (
    [u_NationID]
    ) REFERENCES [Nation] (
    [NationID]
    ) ON UPDATE CASCADE ,
    CONSTRAINT [FK_t_user_Source] FOREIGN KEY
    (
    [u_SourceID]
    ) REFERENCES [Source] (
    [SourceID]
    ) ON UPDATE CASCADE ,
    CONSTRAINT [FK_t_user_State] FOREIGN KEY
    (
    [u_StateID]
    ) REFERENCES [State] (
    [StateID]
    ) ON UPDATE CASCADE ,
    CONSTRAINT [FK_t_user_YachtPlacement] FOREIGN KEY
    (
    [u_YachtPlacementID]
    ) REFERENCES [YachtPlacement] (
    [YppID]
    ) ON UPDATE CASCADE
    ) ON [PRIMARY]
    GO


    (3) Dim oCmd, oConn, oRS
    set oConn = GetConnection()
    Set oCmd = GetStoredProcedure(oConn,"spr_logonuser")
    oCmd.Parameters.append oCmd.CreateParameter("u_name", adVarChar,
    adParamInput,50,sName)
    '//oCmd.Parameters.append oCmd.CreateParameter("u_password", adVarChar,
    adParamInput,12,spassword)
    set oRS = oCmd.execute()
    if oRS.eof then
    sError = "<BR>Invalid User Email Address. Please enter the email you
    signed up with originally"
    else '//SUCCESSFUL
    response.redirect SelectedPage.asp
    End If



    Guest

  2. #2

    Default Re: Extrend SP to check for Expiry date prior to updating?

    SQL Server can't send a user to an ASP page. You can certainly determine if
    the expiry date has past (you can use DATEDIFF or simple < logic), and
    return a different result based on that. The ASP page could then interpret
    the result and act accordingly.

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    <com> wrote in message
    news:#phx.gbl... 
    column 
    WHERE 
    NULL 

    (1), 
    DEFAULT 



    Aaron Guest

  3. #3

    Default Re: Extrend SP to check for Expiry date prior to updating?

    Ok..thanks...I could not figure out how to adapt the sp without messing up
    my asp code so I decided to do the test on the asp page itself using the
    diff function.. fyi:



    Dim oCmd, oConn, oRS
    set oConn = GetConnection()
    Set oCmd = GetStoredProcedure(oConn,"spr_logonuser")
    oCmd.Parameters.append oCmd.CreateParameter("u_name", adVarChar,
    adParamInput,50,sName)
    'oCmd.Parameters.append oCmd.CreateParameter("u_password", adVarChar,
    adParamInput,12,spassword)
    set oRS = oCmd.execute()
    if oRS.eof then
    sError = "<BR>Invalid User Email Address. Please enter the email you
    signed up with originally"
    else '//SUCCESSFUL

    Dim u_id, doent, CookieBln, u_createDate, u_ExpiryDate, DateToday,
    OverdueDays
    u_id = oRS.fields("u_id")
    '//u_createDate =oRS.fields("u_createDate")
    u_ExpiryDate= oRS.fields("u_ExpiryDate")

    DateToday = FormatDateTime(Now(),vbGeneralDate)
    '//OverdueDays = DateToday - FormatDateTime(u_ExpiryDate,
    vbGeneralDate)
    Response.write "Today's Date: " & DateToday
    Response.write "<br>Overdue Days" & DATEDIFF("d",u_ExpiryDate,NOW())
    & "<br>"

    If DATEDIFF("d",u_ExpiryDate,NOW()) >= 0 then
    '//*** REGISTRATION HAS EXPIRED
    response.write "<p><strong>[TODAY'S DATE]:</strong> " & DateToday
    response.write "<br><br><strong>Datebase Expiration Date</strong>:" &
    u_ExpiryDate & "<br><br>"
    response.write "Your registration has <font size=3 color=red
    face=verdana>expired</font>. You will need to update your details"
    '//response.write "Your expiration is overdue by" & OverdueDays & "
    days<br><br>"
    doent = "Update2.asp?u_CurrentURL=" & u_CurrentURL &
    "&u_FromURL=" & u_FromURL
    response.redirect doent
    Else
    '//CHECK FOR COOKIES AND WRITE/RE-WRITE COOKIES TO DRIVE
    '//THIS EXERCISE IS DONE EVERYTIME THE USER LOGINS IN
    '//AND ONLY TAKES PLACE IF HIS REGISTRATION HAS NOT EXPIRED

    Response.write "<br>Your registration has not expired"

    If CookieSUP=1 and CookieEN=1 Then

    response.write "HOWZIT"
    '//response.end
    CookieBln=1

    Response.Cookies("Prospect").Expires = u_ExpiryDate
    Response.Cookies("Prospect")("u_id") = u_id
    Response.Cookies("Prospect")("u_name") = sName
    Response.Cookies("Prospect")("VisitorIP") = VisitorIP

    response.write "<p>"
    doent = u_CurrentURL & "?vID=" & u_id & "&u_FromURL=" &
    u_FromURL
    response.write doent & "?u_FromURL=" & u_FromURL
    response.write "<p>"
    response.redirect doent

    Else '//COOKIES DISABLED - USE DATABASE ID

    CookieBln=0
    doent = u_CurrentURL & "?vID=" & u_id & "&u_FromURL=" &
    u_FromURL

    call write("doent=",doent)
    'doent=trim(doent)
    response.redirect doent

    End iF


    End IF

    response.End()


    bLogon = false
    end if

    "Aaron [SQL Server MVP]" <noraa> wrote in message
    news:phx.gbl... 
    if 
    interpret 
    > column 
    > WHERE 
    > NULL [/ref]
    NULL [/ref]

    > (1), 
    > DEFAULT [/ref]
    NULL [/ref]
    , [/ref]
    , [/ref]
    , [/ref]
    adVarChar, 
    >
    >[/ref]


    Guest

Similar Threads

  1. Validate expiry date in Yaromat Check Form
    By Claire S in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 0
    Last Post: December 5th, 04:32 PM
  2. cookie expiry date problem
    By Ray in forum ASP.NET Security
    Replies: 0
    Last Post: April 21st, 06:41 AM
  3. WHOIS expiry date ...
    By elyob in forum PHP Development
    Replies: 5
    Last Post: December 3rd, 07:59 AM
  4. expiry date in the exe
    By lucy2 webforumsuser@macromedia.com in forum Macromedia Director Basics
    Replies: 3
    Last Post: November 3rd, 06:59 PM
  5. AUTOMATING EXPIRY DATE FROM THE DATE ENTERED
    By Howard Brody in forum Microsoft Access
    Replies: 1
    Last Post: July 21st, 10:30 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139