EasyCFM.COM ColdFusion Forums / Coding Help! / cf insert

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: cf insert

remove book mark from this topic Printer-friendly Version  send this discussion to a friend  new posts last

souvrae
04-20-2013 @ 9:33 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Member
Posts:
Joined: Apr 2013

I am having trouble with an insert statement.
This was a stored procedure and all of a sudden it
doesn't work. I've tried everything that I can think
of.
I'm going to try and past the code here.

Here is the page that runs the program, so that you can
see the error I am getting.
http://yrisbridal.com/test/index_(1).cfm

<cfloop query="sheet">
  <cfif sheet.Product_ColorGroup1 EQ "null">
  <cfset sheet.Product_ColorGroup1 = "">
    </cfif>

     <cfif sheet.Product_ColorGroup2 EQ "null">
          <cfset sheet.Product_ColorGroup2 = "">
    </cfif>
    
<cfif sheet.Sort EQ '><cfset sheet.Sort = 0></cfif>
     
    <!---<cftransaction>--->
    <cfquery datasource="#dsn#" name="insert1">
          insert into tbl_products
          (
            product_MerchantProductID,
            product_Name,
            product_ShortDescription,
            product_Description,
            product_Sort,
            product_OnWeb,
            product_Archive,
            product_shipchrg,
            product_taxgroupid,
            product_colorGroup1,
            product_colorGroup2
        )
          values
          (
             '#sheet.Product_ID#',
            '#sheet.Product_Name#',
            '#sheet.ShortDesc#',
            '#sheet.ProductDesc#',
            #sheet.Sort#,
            #sheet.OnWeb#,
            0,
            #sheet.ChargeShipping#,
            #sheet.taxgroupID#,
            '#sheet.Product_ColorGroup1#',
            '#sheet.Product_ColorGroup2#'
)
        
        SELECT product_ID FROM tbl_products WHERE
product_ID = @@IDENTITY
    </cfquery>

If I put the quotes around these variables, I then get
an error that says I can't convert this data to an int
It always has been an int, so I don't understand why
it's not working now but...Can someone please help?
I've been trying to figure this out for days. -thank
you
            #sheet.Sort#,
            #sheet.OnWeb#,
            0,
            #sheet.ChargeShipping#,
            #sheet.taxgroupID#,

cfSearchin
04-22-2013 @ 12:15 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 608
Joined: Feb 2008

quote:

If I put the quotes around these variables, I then get an error that says I can't convert this data to an int. It always has been an int, so I don't understand why it's not working now


Take a look at the generated SQL in the error message.  It clearly shows you are trying to insert values that are NOT integers, like "Sort", "OnWeb", "ChargeShipping".


INSERT INTO tbl_products ( .... )
VALUES ( ...
, Sort   <!--- #sheet.Sort# == "Sort" --->
, OnWeb    <!--- #sheet.OnWeb# == "OnWeb" --->
, 0
, ChargeShipping
, taxgroupID

, .... )

Bottom line, the #sheet# query contains different values than you were expecting. You need to figure out why. Start by dumping the query.

souvrae
04-24-2013 @ 11:35 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Member
Posts:
Joined: Apr 2013

Thank you. Yes, I can see that. What puzzles me is that
this is a stored procedure that I did not build myself
nor did I change it or the fields to integers. I did not
convert those fields, don't know how they got to be that
way. When I try to convert those to text, in sql, I get
an error message that the server will not support it.
So, again I am still puzzled.

cfSearchin
04-24-2013 @ 11:47 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 608
Joined: Feb 2008

The problem is not the stored procedure, it is your #sheet# query. Some of the columns contains strings, when you clearly were expecting numbers. For example

   #sheet.Sort#   => contains the letters "Sort"
   #sheet.OnWeb#  => contains the letters "OnWeb"

Where does that query come from? If you are reading the data from a spreadsheet maybe you are picking up the column headers?

In any event, you should always to validate input first. Then handle the bad data accordingly (skip the insert, show an error, etcetera).

This message was edited by cfSearchin on 4-24-13 @ 11:48 AM

souvrae
04-26-2013 @ 1:47 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Member
Posts:
Joined: Apr 2013

Yes, the input is numbers. The title of the column is text. The data is pulling from an excel sheet. I have told the query that the first two rows are headers and to pull from row 3

I've been examining the code and changed a few things, now I'm getting another error:
[Macromedia][SQLServer JDBC Driver][SQLServer]There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

This isn't making any sense. There are exactly 11 columns in the insert statement and 11 values.

I am pasting the code from the beginning. You see at the top I have told the program that the first two colums are headers. So it knows to go to row 3 which is indeed a number.


    <cfset filename = "2C4B4000(1).xls">
<cfset dsn = "yrisbridaldsn">
<cfset dsn_remote = "yrisbridaldsn">

<cfspreadsheet action="read" src="#filename#" query="sheet" headerrow="2" rows="3-3">


<cfspreadsheet action="read" src="#filename#" headerrow="2" columns="19-110" rows="3-3" query="rs_skus">

<cfloop query="sheet">
  <cfif sheet.Product_ColorGroup1 EQ "null">
  <cfset sheet.Product_ColorGroup1 = "">
    </cfif>

     <cfif sheet.Product_ColorGroup2 EQ "null">
          <cfset sheet.Product_ColorGroup2 = "">
    </cfif>
    
<cfif sheet.Sort EQ "null"><cfset sheet.Sort = ""></cfif>
     
    <!---<cftransaction>--->
    <cftry>
    <cfquery datasource="#dsn#" name="insert1">
          insert into tbl_products
          (
            product_MerchantProductID,
            product_Name,
            product_ShortDescription,
            product_Description,
            product_Sort,
            product_OnWeb,
            product_Archive,
            product_shipchrg,
            product_taxgroupid,
            product_colorGroup1,
            product_colorGroup2
        )
          values
          (
             '#sheet.Product_ID#',
            '#sheet.Product_Name#',
            '#sheet.ShortDesc#',
            '#sheet.ProductDesc#',
            #sheet.Sort#,
            #sheet.OnWeb#,
            0,
            #sheet.ChargeShipping#,
            #sheet.taxgroupID#,
            '#sheet.Product_ColorGroup1#',
            '#sheet.Product_ColorGroup2#'
           )
        SELECT product_ID FROM tbl_products WHERE product_ID = @@IDENTITY
    </cfquery>
    <cfcatch>
      <cfdump var=#cfcatch#>
      <cfabort>
</cfcatch>
</cftry>

This message was edited by souvrae on 4-26-13 @ 1:49 PM

WolfShade
04-26-2013 @ 2:17 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Member
Posts:
Joined: Apr 2013

I think part of the problem might be:

<cfif sheet.Sort EQ "null"><cfset sheet.Sort = ""></cfif>

If sheet.Sort is supposed to be an integer, then setting it to "" converts it to string.



^_^

cfSearchin
04-26-2013 @ 2:19 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 608
Joined: Feb 2008

First, never trust user supplied data. Remember this is spreadsheet - not a database table ;-) In a spreadsheet, there is nothing preventing users from entering a mix of value types in the same column, or nothing at all. Just because one of the values is numeric, does not mean ALL of them are numeric. So you must validate all of the values before running your query (or you will keep running into problems like this.)

Decide what you want to do when you hit an invalid value. If the user leaves a "numeric" cell empty, you might just want to convert it to 0, or you might want to throw an error. It all depends on your app. But the code should anticipate common problems like this and decide how to handle them up front.

quote:

This isn't making any sense. There are exactly 11 columns in the insert statement and 11 values.


Post the generated sql from the error message. The test page is showing a different error right now.

Nothing to do with your error, but you really, really, really should use cfqueryparam in your INSERT. It provides some performance benefits, but primarily because the insert query exposes your database to SQL injection.

This message was edited by cfSearchin on 4-26-13 @ 2:34 PM

souvrae
04-26-2013 @ 3:11 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Member
Posts:
Joined: Apr 2013

The error is different because since my last post, I ran the query and row 3 was inserted. Before it throws the error, the data gets entered until I get to the image names in column O
So now it has become a duplicate.
I updated it to row 4, which I have not run and I am getting the old error again.

[Macromedia][SQLServer JDBC Driver][SQLServer]There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

I am now thinking that the error might be at the cfloop that inserts the skus and the images because nothing after that point goes into the database.

I know the excel sheet is right because I am the person who put the data in there.

As for the cfqueryparam, you are right. Id like to at least get it running before I struggle with that.

Here is the loop that I speak of:

<cfloop list="#rs_skus.columnList#" index="colname">
        <cfset MerchSKUID = sheet.Product_ID>
        
        <cfquery datasource="#dsn#" name="select_1">
             Select * from tbl_skuoptions where option_Type_ID = #optionArray[1]#
        </cfquery>
        <cfquery datasource="#dsn#" name="select_2">
             Select * from tbl_skuoptions where option_Type_ID = #optionArray[2]#
        </cfquery>
        <cfquery datasource="#dsn#" name="select_3">
             select LEFT(optiontype_Name,2) as opt
            from tbl_list_optiontypes
            where optiontype_ID = #optionArray[1]#
        </cfquery>
        <cfquery datasource="#dsn#" name="select_4">
             select LEFT(optiontype_Name,2) as opt
            from tbl_list_optiontypes
            where optiontype_ID = #optionArray[2]#
        </cfquery>
        <cfset optionIDArray = ListToArray(colname,"_")>
        
        <cfset MerchSKUID = MerchSKUID & '-' & select_3.opt>
        <cfset MerchSKUID = MerchSKUID & '-' & optionIDArray[1]>
        <cfset MerchSKUID = MerchSKUID & '-' & select_4.opt>
        <cfset MerchSKUID = MerchSKUID & '-' & optionIDArray[2]>
        
        <cfset newprice = evaluate('sheet.#colname#')>
        <cfset newprice = replace(newprice,'$',','ALL')>
        
          <cfif newprice GT 0 AND newprice NEQ "null" AND newprice NEQ "">
            <cfquery datasource="#dsn#" name="insert5">
                insert into tbl_skus (SKU_MerchSKUID,SKU_ProductID,SKU_Price,SKU_Stock,SKU_ShowWeb)
                values('#MerchSKUID#',#insert1.product_ID#,#newprice#,999,1)
                SELECT sku_id FROM tbl_skus WHERE sku_id = @@IDENTITY
            </cfquery>
    
            <cfquery datasource="#dsn#" name="insert6">
                insert into tbl_skuoption_rel (optn_rel_SKU_ID,optn_rel_Option_ID)
                values(#insert5.sku_id#,#optionIDArray[1]#)
            </cfquery>
            
            <cfquery datasource="#dsn#" name="insert7">
                 insert into tbl_skuoption_rel(optn_rel_SKU_ID,optn_rel_Option_ID)
                values(#insert5.sku_id#,#optionIDArray[2]#)
            </cfquery>
          </cfif>
    </cfloop>
    
    <cfloop from="1" to="4" index="i">
          <cfquery datasource="#dsn#">
            insert into tbl_prdtimages
            (
                prdctImage_ProductID,
                prdctImage_ImgTypeID
                ,prdctImage_FileName
            )
            values
            (
                #insert1.product_ID#,
                #i#,
                <cfswitch expression="#i#">
                    <cfcase value="1">
                        '#sheet.ImageThumb#'
                    </cfcase>
                    <cfcase value="2">
                        '#sheet.ImageFull#'
                    </cfcase>
                    <cfcase value="3">
                        '#sheet.ImageExpanded#'
                    </cfcase>
                    <cfcase value="4">
                        '#sheet.ImageSmall#'
                    </cfcase>
                    <cfdefaultcase>
                        '
                    </cfdefaultcase>
                </cfswitch>
            )
          </cfquery>
     </cfloop>
    <!---</cftransaction>--->
</cfloop>


souvrae
04-26-2013 @ 3:12 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Member
Posts:
Joined: Apr 2013

Thanks for that, yes, I have changed the "" to "0"

cfSearchin
04-26-2013 @ 4:01 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 608
Joined: Feb 2008

First, it looks like your cfdefaultcase is missing a closing quote. Not sure if that is a typo ...

Second, it is hard to identify the problem without knowing which one of those queries is throwing the error. If you enable full debugging (or add a try/catch) it might give you the generated sql.


quote:

So now it has become a duplicate.


Tip, assuming you are on a development machine, you could wrap the entire thing in one big cftransaction. Then do a roll it all back at the very end. That way nothing gets committed and you can continue testing without worrying about duplicate keys. Just do not forget to remove the rollback once everything is fixed, or the data will never show up!

This message was edited by cfSearchin on 5-10-13 @ 10:41 AM


Website Designed and Developed by Pablo Varando.