9 Replies Latest reply on Mar 13, 2007 11:37 AM by lusciousmango

    Inserting multiple records from a QofQ?

    lusciousmango Level 1
      I'm doing (still!) an app for parents to sign up for information from their child's school, and giving them the option to choose more than one grade using checkboxes. I'm passing the grades as a string, then parsing them into individual searchable grades, and then querying the db to see if that email/school/grade subscription already exists. If it doesn't exist, I want to add it to the db. Using CFDUMP, I've verfied that I'm extracting the correct records to add.

      I've attached the query that selects the records to be added, and then my current INSERT query (which chokes on "INSERT") -- I've tried putting brackets around INSERT per the CFWACK, but that didn't work either. The error is "Query of Queries syntax error; Encountered INSERT".
        • 1. Re: Inserting multiple records from a QofQ?
          Level 7
          you can't use INSERT in a QoQ - in order to INSERT records you need to
          connect to the DSN, not use QoQ

          you can <cfloop> through your QoQ
          inside the cfloop do regular cfquery to insert records

          --

          Azadi Saryev
          Sabai-dee.com
          http://www.sabai-dee.com
          • 2. Re: Inserting multiple records from a QofQ?
            insuractive Level 3
            I can see a couple things wrong with your code -
            1) You shouldn't need to apply the following restrictions to your subsToAdd query as you already apply them to your Ignatz query:
            (Email = '#Form.Email#') AND (LocationCode = #Form.LocationCode#)

            2) For your insert statement, you want to surround the entire SQL statement with the <cfloop> block:

            <cfquery name="saveSubs" dbtype="query">
            <CFLOOP query="subsToAdd">
            INSERT INTO ...
            </CFLOOP>
            </cfquery>

            3) You're probably also recieving an error because you aren't qualifying your text fields (like Email) with single quotes. A better solution would be to use the <cfqueryparam> tags with your query - plus it will make your SQL run faster as well!

            INSERT INTO Subscriptions (Email,LocationCode,GradeID)
            VALUES (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Email#" maxlength="50">,
            <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#locationCode#" maxlength="30">,
            <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#gradeID#">)

            (I took a guess as to the datatypes of your fields, you'll have to adjust them accordingly).
            • 3. Re: Inserting multiple records from a QofQ?
              lusciousmango Level 1
              Michael --

              Thank you -- the first should have been obvious to me, but I'm a little tunnel-visioned right now. :)

              I made all of your suggested changes...and I get the identical error. (FWIW, we're running CF7)

              Here are the queries as amended:

              <!--- Select subscriptions not already in db --->
              <cfquery name="subsToAdd" dbtype="query">
              SELECT *
              FROM Ignatz
              WHERE (GradeID <> '#CheckSub.GradeID#')
              </cfquery>

              <!--- Save the new subscriptions to db --->
              <CFIF subsToAdd.RecordCount GT 0>
              <cfquery name="saveSubs" dbtype="query">
              <CFLOOP query="subsToAdd">
              INSERT INTO Subscriptions (Email,LocationCode,GradeID)
              VALUES (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Email#" maxlength="50">,
              <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#locationCode#" maxlength="4">,
              <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#gradeID#" maxlength="50">)
              </CFLOOP>
              </cfquery>
              • 4. Re: Inserting multiple records from a QofQ?
                Dan Bracuk Level 5
                If you are using a mysql db, you may have had it closer the first time. Otherwise, unless you are using a db that allows multiple sql statements in one cfquery tag, put the cfquery start and end tags inside your loop.
                • 5. Re: Inserting multiple records from a QofQ?
                  lusciousmango Level 1
                  Dan, it's a MS SQL database.

                  I just tried both restoring my original order, and putting the cfquery inside the cfloop. In all cases, I get the same "Query of Queries syntax error; Encountered INSERT" error.
                  • 6. Re: Inserting multiple records from a QofQ?
                    lusciousmango Level 1
                    (got an email from Azadi, saying):

                    "you can't use INSERT in a QoQ - in order to INSERT records you need to
                    connect to the DSN, not use QoQ

                    you can <cfloop> through your QoQ
                    inside the cfloop do regular cfquery to insert records "

                    Azadi, thanks for your help. I'm confused though -- isn't that what I'm doing with this?

                    <CFLOOP query="subsToAdd">
                    <cfquery name="saveSubs" dbtype="query">
                    INSERT INTO Subscriptions (Email,LocationCode,GradeID)
                    VALUES (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Email#" maxlength="50">,
                    <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#locationCode#" maxlength="4">,
                    <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#gradeID#" maxlength="2">)
                    </cfquery>
                    </CFLOOP>
                    • 7. Re: Inserting multiple records from a QofQ?
                      Level 7
                      not quiet. try this:

                      change the following line

                      > <cfquery name="saveSubs" dbtype="query">

                      to:

                      <cfquery name="saveSubs" datasource="your_dsn_name_here">



                      --
                      Azadi Saryev
                      Sabai-dee.com
                      Vientiane, Laos
                      http://www.sabai-dee.com
                      • 8. Re: Inserting multiple records from a QofQ?
                        lusciousmango Level 1
                        GOT IT!

                        I had to change the attribution in the INSERT cfquery from dbtype=query to datasource=schoolnotices.

                        THANK YOU all for your guidance. I'm learning loads, and getting better at solving smaller problems on the way to big ones like this. :)
                        • 9. Re: Inserting multiple records from a QofQ?
                          lusciousmango Level 1
                          Hee -- Azadi, your last post and mine crossed in the mail. :) Thanks again for the help!!