Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2009
    Posts
    8

    Unanswered: Syntax error in UPDATE statement..and Operation must use an updateable query

    ANY help is appreciated!! I've read this forum before which has helped me, but I'm stuck now.

    My hosting company messed me, somewhat of a novice, up by moving my databases for my site. They sent some sample code (far below), but it is so different from my original code (immediately below), that I've only been able to make a bit of progress by eliminating the commas (", amountpaid = ") out of my original code...only to get another updataerror. My original code is:

    <%

    dsndir = Server.MapPath("../_dsn")
    myDSN="filedsn=" & dsndir & "/access_csl"

    set conntemp=server.createobject("adodb.connection")
    conntemp.open myDSN

    id=request("id")
    status=request("status")
    amountpaid=request("amountpaid")
    mySQL = "update roster set status ="
    mySQL = mySQL & status
    mySQL = mySQL & ", amountpaid = "
    mySQL = mySQL & amountpaid
    mySQL = mySQL & ", confirmdate = #"
    mySQL = mySQL & date
    mySQL = mySQL & "# where ID = "
    mySQL = mySQL & id

    set rstemp=conntemp.execute(mySQL)

    set rstemp=nothing
    conntemp.close
    set conntemp=nothing
    response.redirect("index.htm")

    %>


    The hosting company sent me sample code on which to base my tweaks, but it is different and I've made little progress when trying different things from their code:

    This example describes using File DSN and ASP/ADO to connect to an Access Database.

    <%
    Dim oConn, oRs
    Dim qry, connectstr, sDSNDir
    Dim db_name, db_username, db_userpassword
    Dim dsn_name

    dsn_name = "your_dsn_name"
    fieldname = "your_fieldname"
    tablename = "your_tablename"

    'assumes that _dsn exists in the root
    sDSNDir = Server.MapPath("/_dsn")

    connectstr = "filedsn=" & sDSNDir & "/" & dsn_name

    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open connectstr
    qry = "SELECT * FROM " & tablename

    Set oRS = oConn.Execute(qry)

    if not oRS.EOF then
    while not oRS.EOF
    response.write ucase(fieldname) & ": " & oRs.Fields(fieldname) & " "
    oRS.movenext
    wend
    oRS.close
    end if

    Set oRs = nothing
    Set oConn = nothing

    %>

    The hosting company also sent me the following 4 changes where they moved my databases:

    ------------------1---

    connect to database CongmyDSN has been changed from:

    p3swhsql-v14.shr.phx3.secureserver.net


    to:

    CongmyDSN.db.2913758.hostedresource.com


    -----------------2----

    connect to database con0806201355562 has been changed from:

    p3swhsql-v14.shr.phx3.secureserver.net

    to:

    con0806201355562.db.2913758.hostedresource.com


    ----------------3-----


    connect to database CongmySql has been changed from:

    p41mysql1.secureserver.net

    to:

    CongmySql.db.2913758.hostedresource.com



    ----------------4-----


    connect to database con0806202294417 has been changed from:

    p3swhsql-v13.shr.phx3.secureserver.net


    to:

    con0806202294417.db.2913758.hostedresource.com

    ----------------------
    I think my main file names are:

    DSN Name:
    access_csl.dsn

    File Name:
    csl.mdb



    -----------------


    Thanks for any help!
    Last edited by Hottline; 02-21-09 at 03:30.

  2. #2
    Join Date
    Feb 2009
    Posts
    8
    I'm getting close but still need advice. I've tested the page and when I disable one line, the page does not give me an error:

    Microsoft OLE DB Provider for ODBC Drivers error '80004005'
    [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

    So the line I need advice on is:

    set rstemp=conntemp.execute(mySQL)


    Here is the entire script:

    <%

    dsndir = Server.MapPath("../_dsn")
    myDSN="filedsn=" & dsndir & "/access_csl"

    set conntemp=server.createobject("adodb.connection")
    conntemp.open myDSN

    id=request("id")
    status=request("status")
    amountpaid=request("amountpaid")
    mySQL = "update roster set status ="
    mySQL = mySQL & status
    mySQL = mySQL & ", amountpaid = "
    mySQL = mySQL & amountpaid
    mySQL = mySQL & ", confirmdate = #"
    mySQL = mySQL & date
    mySQL = mySQL & "# where ID = "
    mySQL = mySQL & id

    set rstemp=conntemp.execute(mySQL)

    set rstemp=nothing
    conntemp.close
    set conntemp=nothing
    response.redirect("index.htm")

    %>
    Last edited by Hottline; 02-21-09 at 07:15.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    set rstemp=conntemp.execute(mySQL)
    First thing - no need to set a recordset to this as it is an UPDATE not a SELECT. Please can you print out the value of mySQL just before you execute it and post it here?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2009
    Posts
    8

    I'm not sure

    I only have Dreamweaver and had nothing to do with the original code...so I am not sure if I know what you are asking...but here is what I have:

    the ID number would remain the same
    status would change from the number 6 to the number 10
    the amount paid would enter the number 24

    Is that what you need?

  5. #5
    Join Date
    Feb 2009
    Posts
    8
    I know what you need...getting it soon.

  6. #6
    Join Date
    Feb 2009
    Posts
    8

    I don’t see anything after the equals sign?

    This is what I got for mySQL. I don’t see anything after the equals sign?


    update roster set status =, amountpaid = , confirmdate = #2/21/2009# where ID =

  7. #7
    Join Date
    Feb 2009
    Posts
    8
    Could this be a password issue on the server?

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well that SQL certainly isn't going to run.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    So - these lines are not working:
    Code:
    id=request("id")
    status=request("status")
    amountpaid=request("amountpaid")
    Nothing to do with passwords - these variables don't get set values.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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