Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100

    Unanswered: UPDATE code migrated from Access 2003 to SQL Server 2005 not working

    I used to use the following code to update some table statistics when users login to a page. My old database was Access 2k3 and everything ran fine. Now I'm getting this error since I've migrated to SQL Server 2005.
    Microsoft SQL Native Client error '80040e14'

    Incorrect syntax near '4'.

    /TRWebSQL/Ownership_Reportreport.asp, line 401
    The line it refers to is
    conn.Execute (strSQL)
    Code:
    <% 'Update Owner Visit
    sSessionStatus = Session(ewSessionStatus)
    sSessionUserName = Session(ewSessionUserName)
    sRemoteAddress = Request.ServerVariables("REMOTE_ADDR")
    sHTTP_UserAgent = Request.ServerVariables("HTTP_USER_AGENT")
    dCurrentTime = Now()
    If sSessionStatus = "login" Then
     If LEN(sRemoteAddress) > 0 AND LEN(sHTTP_UserAgent) > 0 Then
      strSQL = "UPDATE OWNERS " & _
               "SET Visits = Visits + 1, " & _ 
               "LastLogin = #" & dCurrentTime & "#, " & _
               "RemoteAddr = '" & sRemoteAddress & "', " & _
               "HttpUserAgent = '" & sHTTP_UserAgent & "' " & _
               "WHERE Access_Code = '" & sSessionUserName & "';"
      Set conn = Server.CreateObject("ADODB.Connection")
      conn.Open xDb_Conn_Str
      conn.Execute (strSQL)
      conn.Close
      Set conn = Nothing
      Set strSQL = Nothing    
     End If
    End If
    %>
    BillS

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try replacing # with ' around the date. SQL Server doesn't require it.
    Paul

  3. #3
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    That did it. Many thanks!
    BillS

Posting Permissions

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