• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

insert query not functioning correctly

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
bluskyGuy
Asked:
bluskyGuy
1 Solution
 
CFDevHeadCommented:
What is the error?
0
 
bluskyGuyAuthor Commented:

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
 
CFDevHeadCommented:
I did see this #request.productTable#, db1_products_ext is it tring to referennce two table?
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
bluskyGuyAuthor Commented:
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
 
rob_lorentzCommented:

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
 
mrichmonCommented:
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now