Updating date fields

Results 1 to 7 of 7

Thread: Updating date fields

  1. #1
    Kate Guest

    Default Updating date fields

    Hi,<BR><BR>I am trying to creat a facility that allows users to update Date fields in my database, but I keep getting a "Syntax error in UPDATE statement" error message.<BR><BR>Here is my code - can anyone help??<BR><BR>set conn=server.createobject("adodb.connection")<BR> conn.open "myConn"<BR>Dim var, var1<BR>var = Request.Form("Date") &#039; appears as eg 04/01/01<BR>var1 =Request.Form("Calendar_Id") &#039;this is an integer<BR>sqlstmt = "UPDATE Calendar"<BR>sqlstmt = sqlstmt & " SET Date=&#039;" & var & "&#039;"<BR>sqlstmt = sqlstmt & " WHERE Calendar_Id=" & var1<BR>Set RS = conn.execute(SQLstmt)<BR><BR>In my database, Date is a dated field and Calendar_Id is a number<BR>Thanks in advance<BR><BR>Kate<BR> <BR>

  2. #2
    CousinIT Guest

    Default try this

    i think it should be #&#039;s eg<BR>sqlstmt = sqlstmt & " SET Date=#" & var & "#"<BR>

  3. #3
    Still getting same error Guest

    Default RE: try this

    Hi,<BR><BR>I tried that and I tried using the code below and I still get the syntax error:<BR><BR>sqlstmt = "UPDATE Calendar"<BR>sqlstmt = sqlstmt & " SET Date=#" & "&#039;" & var & "&#039;" &"#"<BR>sqlstmt = sqlstmt & " WHERE Calendar_Id=" & var1<BR>Set RS = conn.execute(SQLstmt)<BR><BR><BR>Kat<BR>

  4. #4
    Eddie Campbell Guest

    Default RE: try this

    It might be because you are using a field called Date in the query. Date is a reserved word. If possible, rename your field to something else.

  5. #5
    Kate Guest

    Default Brilliant but...

    Hi,<BR>That did the trick - thanks alot Eddie - however it brought me to another issue!!<BR><BR>The dates are stored in my database in the dd/mm/yy format.<BR>However when the user modifies a date using this format they are written to the database in yy/mm/dd format. Is there a simple way to rectify this???<BR><BR>Kate<BR><BR>

  6. #6
    Eddie Campbell Guest

    Default Is your server in Japan ?

    I thought yy/mm/dd was only a Japanese format. Anyway, try this:<BR><BR>ymdDate = Request.Form("MyDateField")<BR>dmyDate = DatePart("d",ymdDate) & "/" & DatePart("m",ymdDate) & "/" DatePart("yyyy",ymdDate)<BR><BR>Response.write "The date was :" & ymdDate & "<BR>"<BR>Response.write "The date is :" & dmyDate<BR><BR>You can now use the value of dmyDate to update your database.<BR>

  7. #7
    Join Date
    Dec 1969

    Default yymmdd

    yymmdd is the most logical arrangement of date values.<BR><BR>dates in yymmdd (or, better yet, in yyyymmdd) can be sorted directly with a text sort and will be returned in date order.<BR><BR>i usually use this format for everything but display to the user when i am working with less structed data sources (delimmitted text files, etc).

Posting Permissions

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