3 Replies Latest reply on Aug 10, 2006 11:34 AM by Newsgroup_User

    Confused on Update

    Level 7
      I am attempting to update a record based on a URL.username in a multistep
      form. When I go to update the record, I get this error:

      Syntax error or access violation: You have an error in your SQL syntax;
      check the manual that corresponds to your MySQL server version for the right
      syntax to use near 'WHERE username = 'benchmarkbuilding'' at line 10

      Although it gives me this information:

      UPDATE softproofs.members SET priv = 'member', logo = 'default.jpg',
      projects = '5', sharesize = 102400, paycycle = 'Monthly', userpriv =
      'admin', WHERE username = 'benchmarkbuilding'

      Which all appears to be correct. There is a username in the members table
      names 'benchmarkbuilding'.

      Here is the code I have. Any ideas?! I am stumped.

      <cfset musername = #URL.username#>
      ....
      <cfquery name="updatememrecord" datasource="#Request.MainDSN#">
      UPDATE softproofs.members
      SET priv = 'member',
      logo = 'default.jpg',
      projects = '#FORM.projects#',
      <cfif memtype EQ 'single'>
      <cfif #Form.projects# EQ 5>
      sharesize = 102400,
      <cfelseif #Form.projects# EQ 10>
      sharesize = 204800,
      <cfelse>
      sharesize = 512000,
      </cfif>
      <cfelse>
      <cfif #Form.projects# EQ 10>
      sharesize = 512000,
      <cfelseif #Form.projects# EQ 25>
      sharesize = 1024000,
      <cfelseif #Form.projects# EQ 50>
      sharesize = 2048000,
      <cfelse>
      sharesize = 5120000,
      </cfif>
      </cfif>
      paycycle = '#FORM.paycycle#',
      userpriv = 'admin',
      WHERE username = '#musername#'
      </cfquery>

      --
      Wally Kolcz
      Developer / Support


        • 1. Re: Confused on Update
          Level 7
          'admin', WHERE username = 'benchmarkbuilding'

          Extra comma after your Set list. The database is expecting another
          field and value combination before the WHERE clause. Just delete the
          comma after 'admin'

          Wally Kolcz wrote:
          > I am attempting to update a record based on a URL.username in a multistep
          > form. When I go to update the record, I get this error:
          >
          > Syntax error or access violation: You have an error in your SQL syntax;
          > check the manual that corresponds to your MySQL server version for the right
          > syntax to use near 'WHERE username = 'benchmarkbuilding'' at line 10
          >
          > Although it gives me this information:
          >
          > UPDATE softproofs.members SET priv = 'member', logo = 'default.jpg',
          > projects = '5', sharesize = 102400, paycycle = 'Monthly', userpriv =
          > 'admin', WHERE username = 'benchmarkbuilding'
          >
          > Which all appears to be correct. There is a username in the members table
          > names 'benchmarkbuilding'.
          • 2. Re: Confused on Update
            jdeline Level 1
            UPDATE softproofs.members SET priv = 'member', logo = 'default.jpg',
            projects = '5', sharesize = 102400, paycycle = 'Monthly', userpriv =
            'admin', WHERE username = 'benchmarkbuilding'

            Take the comma off the end of 'admin'
            • 3. Re: Confused on Update
              Level 7
              Thanks. That worked.