Inserting multiple records from a QofQ?

[Guest]

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".

[Newsgroup_User]

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

[insuractive]

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).

[Guest]

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>

[Dan_Bracuk]

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.

[Guest]

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.

[Guest]

(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>

[Newsgroup_User]

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

[Guest]

Hee -- Azadi, your last post and mine crossed in the mail. :) Thanks again for the help!!

[Guest · Accepted solution]

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. 🙂