dcsimg
Results 1 to 9 of 9

Thread: Need help w/ code to increase database record by 1 year, plus formatting

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2011
    Posts
    6

    Need help w/ code to increase database record by 1 year, plus formatting

    Hello all! I'm trying to update the (End) Date for an existing record by 365 days, via a simple button, and am having difficulty with the update statement. Here's the snippet of code in its original state, which obviously is not what I need since all this does is change the date to Now:

    <%

    Set oRs1=Server.CreateObject("adodb.recordset")
    uptSQL = "UPDATE table"
    uptSQL = uptSQL & " SET End_Date = Now()"
    oRs1.Open uptSQL, conn

    %>


    Also, the format of the current record is mm/dd/yyyy and I'd like to keep that format.
    E.G. - 3/1/2011 would be updated to 3/1/2012.

    Thanks very much in advance!!!

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,351

    Re: Need help w/ code to increase database record by 1 year, plus formatting

    Welcome to VBForums

    I really hope that the value in the record does not have any format at all - because String/Text (which is the only data type that can have a format) is a bad choice for storing Date values.

    In addition to that, you should not be using a recordset to execute SQL statements that do not return records, because it wastes resources and takes extra time. If your statement does not contain values then I would recommend the .Execute method of the connection, eg:
    Code:
    conn.Execute uptSQL
    If an SQL statement of any kind has values in it (particularly if they are entered by the user) then I would recommend a Command object.


    As to your question, like many SQL based things the answer depends on which database system you are working with.

    Based on your use of Now() I presume you are using Jet or Ace, in which case I think this should work for you:
    Code:
    uptSQL = uptSQL & " SET End_Date = DateAdd('y', 1, Now())"
    ..or if you want it to be 365 days rather than 1 year (so it varies when leap years are involved), like this:
    Code:
    uptSQL = uptSQL & " SET End_Date = DateAdd('d', 365, Now())"

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2011
    Posts
    6

    Re: Need help w/ code to increase database record by 1 year, plus formatting

    Thx for the welcome Si.

    Here's the latest - I basically got it working, so the date for the record is now incrementing by a year in the database, but I'm trying to send an automated email after the fact and for some reason it keeps sending the wrong End Date within the body of the email instead of the actual End Date that's in the database record. In other words, I'll click the button on the page once, the date will increment from 1/1/2011 to 1/1/2012, then I'll go in and manually switch the year back from 2012 to 2011 (which I've done about 9 or 10 times already), but when I click the button once again the body of the email displays the End Date as 9 or 10+ years [e.g. - 1/1/2020, 1/1/2021, etc] despite the fact that the record in the database is again only incremented from 2011 to 2012.

    I know I'm probably missing something simple, but I've been staring at code all day (and I am working on little-to-no sleep). The code as it currently exists is below. Thanks in advance all!!

    Code:
    <%
    dim conn, ConnString
    
    set conn = server.createobject("adodb.connection")
    conn.open ConnString
    %>
    
    
    <%
    	Set oRs1=Server.CreateObject("adodb.recordset")
    	strSQL1 = "SELECT * FROM table1" 
    	oRs1.Open strSQL1, conn
    
    
    ' Update End Date
            Set oRs1b=Server.CreateObject("adodb.recordset")
    	uptSQL = "UPDATE table1"
    	uptSQL = uptSQL & " SET End_Date = Date_Add(End_Date, INTERVAL 1 YEAR)"
    	oRs1b.Open uptSQL, conn
    				
    
    	Dim emailpeep
    	email1 = "person@insideemail.com"
    %>
    
    	
    
    
    <%
    		' Send Email Alert If There Is A Change
    			
    	sendUrl="http://schemas.microsoft.com/cdo/configuration/sendusing"
    	smtpUrl="http://schemas.microsoft.com/cdo/configuration/smtpserver"
    		
    		
    		' Set the mail server configuration
    	Set objConfig=CreateObject("CDO.Configuration")
    	objConfig.Fields.Item(sendUrl)=5 ' cdoSendUsingPort
    	objConfig.Fields.Item(smtpUrl)="mail.blah.com"
    
    	objConfig.Fields.Update
    		
    		' Create and send the mail
    	Set objMail=CreateObject("CDO.Message")
    	Set objMail.Configuration=objConfig
    	objMail.To=email1
    	objMail.From="admin@insidemail.com"
    	objMail.Subject="A change has occurred"
    	objMail.HTMLBody ="<html>"
    	objMail.HTMLBody = objMail.HTMLBody  & "<body>"
    	objMail.HTMLBody = objMail.HTMLBody  & "<h3>FYI - there has been a change to the end date.</h3>"
    
    	objMail.HTMLBody  = objMail.HTMLBody  & "New End Date: "& oRs1("End_Date") & "</p>"
    
    	objMail.HTMLBody = objMail.HTMLBody  & "</body>"
    	objMail.HTMLBody = objMail.HTMLBody  & "</html>"
    	objMail.Send
    %>
    
    
    
    <%
    conn.Close
    Set conn = Nothing
    %>
    Last edited by gep13; Feb 23rd, 2011 at 02:15 AM. Reason: Added quote tags

  4. #4
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,966

    Re: Need help w/ code to increase database record by 1 year, plus formatting

    Hello vbfDBS,

    When posting into the forums, can you remember to surround your code in [CODE][/CODE] tags? It makes it a lot easier for people to read your code, and therefore be able to help you. I have edited your above post to include code tags this time.

    Thanks

    Gary

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,351

    Re: Need help w/ code to increase database record by 1 year, plus formatting

    One issue is that you open the recordset before you change the value - so the value in the email will be the value before you added anything to it.

    Another possible issue is that your code assumes that there is only one record in the table. Your Update statement changes all records, and your Select statement loads them all (but your code only uses the first record).

  6. #6

    Thread Starter
    New Member
    Join Date
    Feb 2011
    Posts
    6

    Re: Need help w/ code to increase database record by 1 year, plus formatting

    Si, thx again for the reply! Apologies for the lag on this end...was away traveling.

    OK all, so I've made a few adjustments but am still encountering the same issue. I should admit in advance, I'm not a vb coder and am basically trying to edit some pre-existing code (which originated from an ex-coworker). Here's the code as it exists now:

    Code:
     <%
     dim conn, ConnString
     
     set conn = server.createobject("adodb.connection")
     conn.open ConnString
     %>
     
     
     <%
     
     Set oRs=Server.CreateObject("adodb.recordset")
     uptSQL = "UPDATE table1 SET End_Date = Date_Add(End_Date, INTERVAL 1 YEAR)"
     oRs.Open uptSQL, conn                
     %>
     
     
     <%
     Set oRs1=Server.CreateObject("adodb.recordset")
     strSQL1 = "SELECT End_Date FROM table1" 
     oRs1.Open strSQL1, conn
     
     Dim emailpeep
     email1 = "person@insideemail.com"
     
     
     sendUrl="http://schemas.microsoft.com/cdo/configuration/sendusing"
     smtpUrl="http://schemas.microsoft.com/cdo/configuration/smtpserver"
     
     
     Set objConfig=CreateObject("CDO.Configuration")
     objConfig.Fields.Item(sendUrl)=5 ' cdoSendUsingPort
     objConfig.Fields.Item(smtpUrl)="mail.blah.com"
     objConfig.Fields.Update
     
     
     Set objMail=CreateObject("CDO.Message")
     Set objMail.Configuration=objConfig
     objMail.To=email1
     objMail.From="admin@insidemail.com"
     objMail.Subject="A change has occurred"
     objMail.HTMLBody ="<html>"
     objMail.HTMLBody = objMail.HTMLBody  & "<body>"
     objMail.HTMLBody = objMail.HTMLBody  & "<h3>FYI - there has been a change to the end date.</h3>"
     
     objMail.HTMLBody  = objMail.HTMLBody  & "New End Date: "& oRs1("End_Date") & "</p>"
     
     objMail.HTMLBody = objMail.HTMLBody  & "</body>"
     objMail.HTMLBody = objMail.HTMLBody  & "</html>"
     objMail.Send
     %>
     
     
     <%
     conn.Close
     Set conn = Nothing
     %>

    Again, I click the button once and the End Date in the database table increases by 1 year but then the email outputs the incorrect End Date. Then I'll go into the table and manually edit the date to the previous year and repeat, and the date (year) in the email is still incorrect. See below for examples of what I mean:


    * 3rd-to-last attempt: click the button, date changes from 1/1/2011 to 1/1/2012 in the database, but email outputs "1/1/2032 12:45:56 PM". I manually edit the year w/in the DB and change it from 2012 back to 2011.

    * 2nd-to-last attempt: click the button, date changes from 1/1/2011 to 1/1/2012 in the database, but email outputs "1/1/2033 12:45:56 PM". I manually edit the year w/in the DB and change it from 2012 back to 2011.

    * Last attempt: click the button, date changes from 1/1/2011 to 1/1/2012 in the database, but email outputs "1/1/2034 12:45:56 PM".

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,351

    Re: Need help w/ code to increase database record by 1 year, plus formatting

    I'm afraid that I can't see why that would be happening, unless there are multiple rows of data in the table.

    As before, you should change:
    Code:
    oRs.Open uptSQL, conn
    to:
    Code:
    conn.Execute uptSQL

  8. #8

    Thread Starter
    New Member
    Join Date
    Feb 2011
    Posts
    6

    Re: Need help w/ code to increase database record by 1 year, plus formatting

    Darnit - I neglected to make that change. OK, so I made the edit and changed

    Code:
    oRs.Open uptSQL, conn
    To

    Code:
    conn.Execute uptSQL
    Still no luck. And to address your last comment, there actually are multiple rows in the table but the button only updates one given row/record.

    Any other thoughts/ideas?! Again, thanks in advance!

  9. #9
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,351

    Re: Need help w/ code to increase database record by 1 year, plus formatting

    Quote Originally Posted by vbfDBS View Post
    Still no luck. And to address your last comment, there actually are multiple rows in the table
    Then as I explained before, that is the cause of the problem.
    but the button only updates one given row/record.
    That is not true I'm afraid - it updates all records, because your Update statement does not have a Where clause.

    As for reading the data, your Select statement also does not have a Where clause - so again works with all records. As you don't use a loop you only actually read one of the records, but you cannot be sure which one.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width