Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

ASP Error

Options
  • 26-03-2008 6:59pm
    #1
    cyberwit
    Registered Users Posts: 269 ✭✭


    Getting the following error trying to update a record in a Access Database

    Error Type:
    Microsoft JET Database Engine (0x80040E14)
    Syntax error in UPDATE statement.
    /Project UIv Slight Changes/courseupdate3.asp, line 31


    I can't seem to see the error any help would be appreciated :confused:

    <html>
    <head>
    <title>Course Update</title>
    </head>
    <body>


    <%
    Set oConn = Server.CreateObject("ADODB.Connection")
    sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & Server.MapPath("db\HR System.mdb") & ";"
    oConn.Open(sConnection)
    %>
    <p><font face="Arial" size="6" color="#000080">Update a Course Record Confirmation</font></p>
    <%
    sSQL = "SELECT * FROM Course where CourseNo='" & Request("CourseNo") &"'"
    Set oRS = oConn.Execute(sSQL)

    If Request("submit") = "Update" Then

    sSQL = "Update Course set "
    sSQL = sSQL & "CourseNo = '" & Request("CourseNo") & "', "
    sSQL = sSQL & "EmployeePPS = '" & Request("EmployeePPS") & "', "
    sSQL = sSQL & "CourseTitle = '" & Request("CourseTitle") & "', "
    sSQL = sSQL & "CourseDescription = '" & Request("CourseDescription") & "', "
    sSQL = sSQL & "StartDate = '" & Request("StartDate") & "', "
    sSQL = sSQL & "FinishDate = '" & Request("FinishDate") & "',"
    sSQL = sSQL & "ManagerAuthorisation= '" & Request("ManagerAuthorisation") & "', "
    sSQL = sSQL & "WHERE CourseNo = '" & Request("CourseNo") & "',"
    End If

    oConn.Execute (sSQL)
    Response.Write "Course <b>" & Request("CourseNo") & "</b> has been successfully updated<br>"

    oRS.Close
    oConn.Close
    Set oRS = Nothing
    Set oConn = Nothing
    %>
    <p>
    <a href="JavaScript:window.close()">Close</a>
    </body>
    </html>


Comments

  • Options
    #2
    Evil Phil
    Registered Users Posts: 7,468 ✭✭✭Evil Phil 


    If Request("submit") = "Update" Then

sSQL = "Update Course set "
sSQL = sSQL & "CourseNo = '" & Request("CourseNo") & "', "
sSQL = sSQL & "EmployeePPS = '" & Request("EmployeePPS") & "', "
sSQL = sSQL & "CourseTitle = '" & Request("CourseTitle") & "', "
sSQL = sSQL & "CourseDescription = '" & Request("CourseDescription") & "', "
sSQL = sSQL & "StartDate = '" & Request("StartDate") & "', "
sSQL = sSQL & "FinishDate = '" & Request("FinishDate") & "',"
sSQL = sSQL & "ManagerAuthorisation= '" & Request("ManagerAuthorisation") & "', "
sSQL = sSQL & "WHERE CourseNo = '" & Request("CourseNo") [b][COLOR="Red"]& "',"[/COLOR][/b]
End If

    Reckon that's a problem in red anyway. And you need to look out for sql injection too.


  • Options
    #3
    stevenmu
    Moderators, Society & Culture Moderators Posts: 9,689 Mod ✭✭✭✭stevenmu


    A useful tip when creating sql statements dynamically like this is to put in some lines to debug the statement, for e.g. just before your execute statement you could put in

    Response.Write sSQL
    Response.End

    the second line halts execution after printing out your SQL statement. You will normally be able to spot the error in the statement at that point. If not copy and paste into access and that might help.


  • Options
    #4
    dzy
    Closed Accounts Posts: 81 ✭✭dzy 


    If Request("submit") = "Update" Then

sSQL = "Update Course set "
sSQL = sSQL & "CourseNo = '" & Request("CourseNo") & "', "
sSQL = sSQL & "EmployeePPS = '" & Request("EmployeePPS") & "', "
sSQL = sSQL & "CourseTitle = '" & Request("CourseTitle") & "', "
sSQL = sSQL & "CourseDescription = '" & Request("CourseDescription") & "', "
sSQL = sSQL & "StartDate = '" & Request("StartDate") & "', "
sSQL = sSQL & "FinishDate = '" & Request("FinishDate") & "',"
sSQL = sSQL & "ManagerAuthorisation= '" & Request("ManagerAuthorisation") & "'[B][COLOR=Red],[/COLOR][/B] "
sSQL = sSQL & "WHERE CourseNo = '" & Request("CourseNo")[COLOR=Black] & "',"[/COLOR]
End If

    Also the extra comma before the WHERE.


  • Options
    #5
    cyberwit
    Registered Users Posts: 269 ✭✭cyberwit


    Thanks your help


Advertisement