2 Replies Latest reply on May 4, 2015 6:52 PM by curro_bravo

    IDENTITY_INSERT is set to OFF.

    curro_bravo

      I have a dynamic site.

      When I insert an integer (a whole number) in a raw materials purchase table, I get the following message:

       

       

      Error Executing Database Query.

      [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot insert explicit value for identity column in table 'tblRawMatlPurch' when IDENTITY_INSERT is set to OFF.           

       

      When I change to or insert a numeric, decimal, or money value, I get the following message:

       

      Raw Materials Decision

      Company:  Grande

      You have NOT successfully submitted the Raw Materials Decision form

      Please note that the purchase amount must be a whole number, please do not enter a decimal for this number.

       

      Return to correct

       

      I have two programs: one is in the secure section, and the name is secure/raw_materials_decision_action, indicating that the raw material purchase variable (rawMat) is a money variable. Giving this information I set it in SQL as a money variable. Here is the file:

       

      <!---  get Company Name --->

      <cfquery name="getCompany" datasource="#application.dsn#">

        Select companyName

        From tblCompNames

        Where companyID=<cfqueryparam cfsqltype="cf_sql_integer" value="#getAuthUser()#">

      </cfquery>

       

       

      <cfparam name="form.changes" default="">

       

       

      <cfset error = "">

      <!--- All users must have raw materials associated with city and product when user profile created --->

       

       

      <!--- update action --->

      <cfif form.changes NEQ "">

      <!---  go through changes - if they changed cost or selling price, will contain cost_id --->

        <cfloop index="x" list="#form.changes#">

        <cfif trim(x) NEQ "">

        <cfset thisSalesOrderID = trim(x)>

        <cfset thisRawMatID = form["rawMatID_#thisSalesOrderID#"]>

        <cfset thisCity = form["cityID_#thisSalesOrderID#"]>

        <cfset thisProduct = form["productID_#thisSalesOrderID#"]>

        <cfset thisPurchase = form["purchase_#thisSalesOrderID#"]>

        <cfif thisPurchase NEQ "" AND (not isNumeric(thisPurchase) OR thisPurchase CONTAINS ".")>

        <cfset error = "yes">

        </cfif>

        <cfif error EQ "">

        <cfquery name="getRow" datasource="#application.dsn#">

        Select *

        From tblRawMatlPurch

        WHERE SalesOrderID=<cfqueryparam cfsqltype="cf_sql_integer" value="#thisSalesOrderID#">

        </cfquery>

        <cfif getRow.RawMat NEQ thisPurchase>

        <cfif getRow.recordCount NEQ 0 AND trim(thisPurchase) NEQ "">

        <cfquery name="updateRow" datasource="#application.dsn#">

        Update tblRawMatlPurch

        Set CityID = <cfqueryparam cfsqltype="cf_sql_integer" value="#thisCity#">

        , ProductID = <cfqueryparam cfsqltype="cf_sql_integer" value="#thisProduct#">

        , RawMatID = <cfqueryparam cfsqltype="cf_sql_integer" value="#thisRawMatID#">

        , RawMat = <cfqueryparam cfsqltype="cf_sql_money" value="#trim(thisPurchase)#">

        WHERE SalesOrderID=<cfqueryparam cfsqltype="cf_sql_integer" value="#thisSalesOrderID#">

        </cfquery>

        <cfelseif getRow.recordCount NEQ 0 AND trim(thisPurchase) EQ "">

        <cfquery name="deleteRow" datasource="#application.dsn#">

        Delete From tblRawMatlPurch

        WHERE SalesOrderID=<cfqueryparam cfsqltype="cf_sql_integer" value="#thisSalesOrderID#">

        </cfquery>

        <cfelseif getRow.recordCount EQ 0 AND thisPurchase NEQ "">

        <cfquery name="insertRow" datasource="#application.dsn#">

        Insert Into tblRawMatlPurch

        (SalesOrderID,companyID,cityID, productID,RawMatID,RawMat)

        VALUES

        (<cfqueryparam cfsqltype="cf_sql_integer" value="#thisSalesOrderID#">,<cfqueryparam cfsqltype="cf_sql_integer" value="#getAuthUser()#">, <cfqueryparam cfsqltype="cf_sql_integer" value="#thisCity#">, <cfqueryparam cfsqltype="cf_sql_integer" value="#thisProduct#">,<cfqueryparam cfsqltype="cf_sql_integer" value="#thisRawMatID#">,<cfqueryparam cfsqltype="cf_sql_money" value="#trim(thisPurchase)#">)

        </cfquery>

        </cfif>

        </cfif>

        </cfif>

        </cfif>

        </cfloop>

      </cfif>

       

      However, I have a second program in the files section: the raw material decision, which indicates that the variable must be an integer (a whole number).

       

      <!---  Text Content for Raw Materials Decision Form

        DO NOT CHANGE ANY FORM NAMES OR ADD NEW FORM NAMES

        <input type="text" name="DO NOT CHANGE". . .>

       

        See raw_materials_decison_es.html for Spanish version of this file --->

       

      <!---  User can enter just one purchase

        - the next time they use this screen it will show blank city/product/raw material purchases

        error message if they do not enter numeric value --->

       

       

       

       

      <!---  get Materials for this company --->

      <cfquery name="getMaterials" datasource="#application.dsn#">

        SELECT SalesOrderID, CityID, ProductID, RawMatID

        FROM tblRawMatID

        WHERE CompanyID=<cfqueryparam cfsqltype="cf_sql_integer" value="#getAuthUser()#">

        ORDER BY cityID, productID, rawMatID

      </cfquery>

       

       

      <!--- set Company name --->

      <cfset thisCompany = getCompany.companyName>

       

       

      <table width="600" border="0" cellpadding="5" align="center">

        <cfoutput>

        <tr>

        <td colspan="5" align="right">

        <a href="logout.cfm" style="text-decoration:none; font-weight:bold;">LOGOUT</a>

        </td>

        </tr>

        <tr>

        <td colspan="5">

        <h2>Raw Materials Decision</h2>

        <h5>Company:  #thiscompany#</h5>

        </td>

        </tr>

        </cfoutput>

      <!---  Action display --->

      <cfif form.changes NEQ "">

        <!--- Error Message - if there --->

        <cfif error NEQ "">

        <tr>

        <td colspan="5">

        <p>

        You have NOT successfully submitted the Raw Materials Decision form

        </p>

        <p>

        Please note that the purchase amount must be a whole number, please do not enter a decimal for this number.

        <br /><br />

        <a href='Javascript: history.back();'>Return to correct</a>

        </p>

        </td>

        </tr>

        <cfelse>

        <tr>

        <td colspan="5">

        <p>

        Thank you for your submission.

        </p>

        </td>

        </tr>

        </cfif>

      <!---  START FORM --->

      <cfelse>

        <!---  Show Materials already entered when Company set up --->

        <form action="raw_materials_decision.cfm" method="post" name="materials">

        <!---  changes hold the ID for any values that are changed to minimize time to check for changes

        - uses onChange in form input to send ID --->

        <input type="hidden" name="changes" value="" />

        <!--- top column headings for table --->

        <tr>

        <th>

        City

        </th>

        <th>

        Product

        </th>

        <th>Raw Materials</th>

        <th>

        Price</th>

        <th align="center">

        Purchase

        </th>

        </tr>

        <cfoutput query="getMaterials">

        <cfquery name="getCity" datasource="#application.dsn#">

        Select cityName

        From tblCities

        Where cityID = <cfqueryparam cfsqltype="cf_sql_integer" value="#getMaterials.cityID#">

        </cfquery>

        <cfquery name="getProduct" datasource="#application.dsn#">

        Select productName

        From tblProductCategories

        Where productID = <cfqueryparam cfsqltype="cf_sql_integer" value="#getMaterials.productID#">

        </cfquery>

        <cfquery name="getMaterial" datasource="#application.dsn#">

        Select rawMatDescription

        From tblRmatCategory

        Where rawMatID = <cfqueryparam cfsqltype="cf_sql_integer" value="#getMaterials.rawMatID#">

        </cfquery>

        <cfquery name="getPrice" datasource="#application.dsn#">

        Select rawMatPrice

        From tblRawmatPrices

        Where rawMatID = <cfqueryparam cfsqltype="cf_sql_integer" value="#getMaterials.rawMatID#">

        AND cityID= <cfqueryparam cfsqltype="cf_sql_integer" value="#getMaterials.cityID#">

        </cfquery>

       

        <cfquery name="getPurch" datasource="#application.dsn#">

        SELECT RawMat

        FROM tblRawMatlPurch

        WHERE companyID=<cfqueryparam cfsqltype="cf_sql_integer" value="#getAuthUser()#">

        AND cityID = <cfqueryparam cfsqltype="cf_sql_integer" value="#getMaterials.cityID#">

        AND productID = <cfqueryparam cfsqltype="cf_sql_integer" value="#getMaterials.productID#">

        AND rawMatID =  <cfqueryparam cfsqltype="cf_sql_integer" value="#getMaterials.RawMatID#">

        </cfquery>

        <tr>

        <td>

        #getCity.cityName#

        <input type="hidden" name="cityID_#salesOrderID#" value="#cityID#" />

        </td>

        <td>

        #getProduct.productName#

        <input type="hidden" name="productID_#salesOrderID#" value="#productID#" />

        </td>

        <td>

        #getMaterial.RawMatDescription#

        <input type="hidden" name="rawMatID_#salesOrderID#" value="#RawMatID#">

        </td>

        <td>

        #dollarFormat(getPrice.rawMatPrice)#

        </td>

        <td align="center">

        <input  name="purchase_#salesOrderID#" type="text" value="#getPurch.rawMat#" size="12" onchange="document.materials.changes.value=document.materials.changes.value + ', #salesOrderID#';" />

        </td>

        </tr>

        </cfoutput>

        <!--- blank space before submit button --->

        <tr>

        <td colspan="5">

        <br />

        </td>

        </tr>

        <!--- submit buttons --->

        <tr>

        <td colspan="5">

        <input type="submit" value="Submit Raw Materials" />

        <input type="reset" value="Reset">

        </td>

        </tr>

        </form>

      </cfif>

      </table>

       

      I understand there is a conflict between the secure/raw_materials_decision_action program and the raw materials decision program, except, that I do not know how to fix it.

      Perhaps, I could change, the raw materials variable from money to integer. But, I am not sure this will solve the problem.

      Alternatively, I could try to change the raw_materials_decision, specification, to accept a decimal, numeric, or money variable, but, I have been revieweing the file, and I do not know how to make this change.

      It is not obvious to me.

      Is there anyone who could help with this problem to find a best possible solution?

      I would appreciate the assistance very much.

      Thank you.