?
Solved

insert query not functioning correctly

Posted on 2005-04-01
7
Medium Priority
?
147 Views
Last Modified: 2013-12-24
Hi all,
I have the following query but it's not working right (receiving an error). Any idea what's causing the problem?

<cfquery datasource="#request.dsn#" username="#request.dbUserName#" password="#request.dbPassword#" name="insNewProduct">
                              INSERT INTO #request.productTable#, db1_products_ext
                              (title, item_alias2, item_feature1, item_feature2, item_feature3, item_feature4, item_feature5,
                              department, category, subcategory, short_description, item_desc_extended, keywords, content, template,
                              layout, sku, list_price, price, item_cost, inventory, inventory_minimum, use_inventory, backorder, on_order,
                               ship_code, ship_notes, free_ship, item_weight, cart_button, vendorid, sale, taxable, ship_weight,
                               admin_use,item_back_order_delay, item_label1, item_label2, item_label3, item_label4, item_label5, item_value1, item_value2, item_value3, item_value4, item_value5, item_onsale)
                              VALUES ('#form.title#','#form.item_alias2#','#form.Detail1#','#form.Detail2#','#form.Detail3#','#form.Detail4#','#form.Detail5#',
                               '#form.dept#','#form.cat#','#form.subcat#','#form.short_description#','#form.meta_description#','#form.keywords#','#form.content#',
                               '#form.template#','#form.layout#','#form.sku#','#form.list_price#','#form.price#','#form.item_cost#',#form.inventory#,
                               #form.inventory_minimum#,#form.use_inventory#, #form.backorder#,#form.on_order#, '#form.ship_code#','#form.ship_notes#',
                               #form.free_ship#,'#form.ship_weight#',#form.cart_button#,'#form.vendorid#',#form.sale#,#form.taxable#,#form.ship_weight#,
                               '#cookie.adminUser#','#form.backorder#',
                               '#form.item_label2#',
                              '#form.item_label3#',
                              '#form.item_label4#',
                              '#form.item_label5#',
                               '#form.item_value1#',
                               '#form.item_value2#',
                               '#form.item_value3#',
                               '#form.item_value4#',
                               '#form.item_value5#',
                               #form.sale#)

                        </cfquery>
                  
0
Comment
Question by:bluskyGuy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 9

Expert Comment

by:CFDevHead
ID: 13685853
What is the error?
0
 

Author Comment

by:bluskyGuy
ID: 13685871

Error Executing Database Query.
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 ' db1_products_ext (id,title, item_alias2, item_feature1,
 
The error occurred in  line 89

87 :                                '#form.item_value4#',
88 :                                '#form.item_value5#',
89 :                                '#form.sale#')
90 :
91 :                         </cfquery>
0
 
LVL 9

Expert Comment

by:CFDevHead
ID: 13685877
I did see this #request.productTable#, db1_products_ext is it tring to referennce two table?
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Comment

by:bluskyGuy
ID: 13685931
Yup, there are two tables I'm looking to insert data into. Here's the most recent query:

<cfquery datasource="#request.dsn#" username="#request.dbUserName#" password="#request.dbPassword#" name="insNewProduct">
                              INSERT INTO db1_products, db1_products_ext
                              (id,title, item_alias2, item_feature1, item_feature2, item_feature3, item_feature4, item_feature5,
                              department, category, subcategory, short_description, item_desc_extended, keywords, content, template,
                              layout, sku, list_price, price, item_cost, inventory, inventory_minimum, use_inventory, backorder, on_order,
                               ship_code, ship_notes, free_ship, item_weight, cart_button, vendorid, sale, taxable, ship_weight,
                               admin_use,item_back_order_delay, item_label1, item_label2, item_label3, item_label4, item_label5, item_value1, item_value2, item_value3, item_value4, item_value5, item_onsale)
                              VALUES ('#variables.newid#','#form.title#','#form.item_alias2#','#form.Detail1#','#form.Detail2#','#form.Detail3#','#form.Detail4#','#form.Detail5#',
                               '#form.dept#','#form.cat#','#form.subcat#','#form.short_description#','#form.meta_description#','#form.keywords#','#form.content#',
                               '#form.template#','#form.layout#','#form.sku#','#form.list_price#','#form.price#','#form.item_cost#',#form.inventory#,
                               #form.inventory_minimum#,#form.use_inventory#, #form.backorder#,#form.on_order#, '#form.ship_code#','#form.ship_notes#',
                               #form.free_ship#,'#form.ship_weight#',#form.cart_button#,'#form.vendorid#',#form.sale#,#form.taxable#,#form.ship_weight#,
                               '#cookie.adminUser#','#form.backorder#',
                               '#form.item_label2#',
                              '#form.item_label3#',
                              '#form.item_label4#',
                              '#form.item_label5#',
                               '#form.item_value1#',
                               '#form.item_value2#',
                               '#form.item_value3#',
                               '#form.item_value4#',
                               '#form.item_value5#',
                               '#form.sale#')

                        </cfquery>
0
 
LVL 10

Expert Comment

by:rob_lorentz
ID: 13686024

I don't think you can insert into 2 different tables with one query. You probably need to break it up into 2 cfquery statements
0
 
LVL 35

Accepted Solution

by:
mrichmon earned 2000 total points
ID: 13757608
You can do it as one cfquery statement, but two insert statements.

But it cannot be done as you are doing it unless you do a join on the tables which I don't recommend.

Or you can do a stored procedure which may be a way to seriously consider.

Assuming you want two insert statements then do something like:


<cfquery datasource="#request.dsn#" username="#request.dbUserName#" password="#request.dbPassword#" name="insNewProduct">
        INSERT INTO db1_products (fields in db1_products) VALUES (Formfields for colums in db1_products);

       INSERT INTO db1_products_ext (fields in db1_products_ext) VALUES (Formfields for colums in db1_products_ext)
</cfquery>


(Note this may not work in access and to get it to work in MySQL you need to be sure that the driver you are using supports it  MySQL - the database - does support two insert operations in the same statement, but I don't know if the CF driver for MySQL does.)

I have successfully done this with MySQL in other languages, but have not had a need in for it in my MySQL/ColdFusion apps.

The advantage is that you can also do things like get the last created identity in the middle of the two inserts if the ID from the first is needed in teh second.
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question