Skip to main content
J
jamie61880
Inspiring
October 29, 2015
Answered

Access to SQL issues when updating a number field

  • October 29, 2015
  • 1 reply
  • 595 views

Hi everyone. I have an edit page that updates some fields in a database. We just converted everything from Access to SQL. I can update the page just fine for the most part, except when I try to update the Sales Order Number for some reason. I'm not sure why. I think it has something to do with the SQL database, but I don't know what. The field is called Sales_Order_Number. When I try to enter a number in this field, I get a 500 Internal Server error. Below is my query that I use to display the info. on the action page, and also the Updating of the table.

We have also updated our ColdFusion from MX7 to 11. Not sure if that would make a difference or not otherwise. Can someone please help? Thanks.

Andy

<CFQUERY NAME="ShowContact" Datasource="#application.DataSource#">

SELECT Company, Company_Name, First_Name, Middle, Last_Name,

ContactID, Contact_Num, ItemID, Item_Num, RFQID, RFQID_SPEC, Entry_Date 

FROM

(((Contacts LEFT JOIN RFQ_Numbers ON Contacts.ContactID = RFQ_Numbers.Contact_Num)

LEFT JOIN Companies ON Contacts.Company = Companies.Company_Name)

LEFT JOIN RFQ_SPEC ON RFQ_Numbers.RFQID = RFQ_SPEC.RFQID_SPEC)

Where ItemID=#form.ItemID#

<!--- And Contacts.RFQ_Num >=0  --->

ORDER BY ItemID

</CFQUERY>

<cfset Form.RFQ_Expected_Value = ReReplace(Form.RFQ_Expected_Value,"[$,abcdefghijklmnopqrstuvwxyz]","","All")>

<cfquery Datasource="#application.DataSource#">

Update RFQ_SPEC

Set <!--- <cfif Entry_Date IS "">Entry_Date=Null,

  <cfelse>Entry_Date='#form.Entry_Date#',</cfif> --->

  M2M_Quote_Number= <cfif isDefined("form.M2M_Quote_Number") and #form.M2M_Quote_Number# neq

  ""> '#form.M2M_Quote_Number#',

  <cfelse>

  NULL,

  </cfif>

  Rev='#form.Rev#',

  Standard_Part_Number='#form.Standard_Part_Number#',

  Generic_Description='#form.Generic_Description#',

  <!--- <CFIF IsDefined("form.GHz_Socket")>GHz_Socket =

  #form.GHz_Socket#,</CFIF> --->

  Received_By='#form.Received_By_Initials#',

  Assigned_By='#form.Assigned_By_Initials#',

  RFQ_Leader='#form.RFQ_Leader_Initials#',

  Jsquad_Coordinator='#form.Jsquad_Initials#',

  <cfif Complete_Date IS "">Complete_Date=Null,

  <cfelse>Complete_Date='#form.Complete_Date#',</cfif>

  Highest_Quantity_Request= <cfif isDefined("form.Highest_Quantity_Request") and

  #form.Highest_Quantity_Request# neq

  "">

  '#form.Highest_Quantity_Request#',

  <cfelse>

  NULL,

  </cfif>

  <cfif New_Custom_PO_Received_Date IS "">New_Custom_PO_Received_Date=Null,

  <cfelse>New_Custom_PO_Received_Date='#form.New_Custom_PO_Received_Date#',</cfif>

  Sales_Order_Number= <cfif isDefined("form.Sales_Order_Number") and

  #form.Sales_Order_Number# neq

  ""> '#form.Sales_Order_Number#',

  <cfelse>

  NULL,

  </cfif>

  <CFIF IsDefined("form.Order_Request")>Order_Request =

  #form.Order_Request#,</CFIF>

  Customer_Reason_Rejecting='#form.Customer_Reason_Rejecting#',

  Budgetary_Quote='#form.Budgetary_Quote#',

  RFQ_Expected_Value= <cfif isDefined("form.RFQ_Expected_Value") and #form.RFQ_Expected_Value# neq

  ""> '#form.RFQ_Expected_Value#',

  <cfelse>

  NULL,

  </cfif>

  Possible_Status='#form.Possible_Order#',

  <!--- Code for if a Sales Order Number is entered, then the Sales Status field will be entered as "Ordered". If the Sales Order Number field is blank, then the Sales Status field will be entered as whatever the drop down menu item that was chosen.

--->

  <cfif Sales_Order_Number IS Not "">Sales_Status="Ordered",

  <cfelseif Sales_Order_Number IS "">Sales_Status='#form.Sales_Options#',

  </cfif>

  Additional_Comments='#form.Additional_Comments#',

  End_Customer_Name='#form.End_Customer_Name#'

Where ItemID=#form.ItemID#

</cfquery>

    This topic has been closed for replies.
    Correct answer jamie61880

    BKBK,

         Thanks, but what I just found out is that I needed to change the input of the word Ordered in single quotes instead of double quotes, so the if statement reads like this now:

    <cfif Sales_Order_Number IS Not "">Sales_Status='Ordered',

      <cfelseif Sales_Order_Number IS "">Sales_Status='#form.Sales_Options#',

      </cfif>

    Thanks again.

    1 reply

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    October 31, 2015

    Take everything as a whole. I have made some suggestions. Note the full column names, which I have guessed, and the use of cfqueryparam. I have also guessed which datatypes are varchar and which are integer.

    <cfif isDefined("form.itemID")>

        <CFQUERY NAME="ShowContact" Datasource="#application.DataSource#">

        SELECT Companies.Company, Companies.Company_Name, Contacts.First_Name, Contacts.Middle, Contacts.Last_Name,

        Contacts.ContactID, Contacts.Contact_Num, RFQ_Numbers.ItemID, RFQ_Numbers.Item_Num, RFQ_SPEC.RFQID, RFQ_SPEC.RFQID_SPEC, RFQ_SPEC.Entry_Date

        

        FROM

        Contacts

        LEFT JOIN RFQ_Numbers ON Contacts.ContactID = RFQ_Numbers.Contact_Num

        LEFT JOIN Companies ON Contacts.Company = Companies.Company_Name

        LEFT JOIN RFQ_SPEC ON RFQ_Numbers.RFQID = RFQ_SPEC.RFQID_SPEC

        

        Where RFQ_Numbers.ItemID=<cfqueryparam cfsqltype="cf_sql_integer" value="#form.ItemID#"> 

        ORDER BY RFQ_Numbers.ItemID

        </CFQUERY>

        

        <cfset Form.RFQ_Expected_Value = ReReplace(Form.RFQ_Expected_Value,"[$,abcdefghijklmnopqrstuvwxyz]","","All")>

        <cfquery Datasource="#application.DataSource#">

        Update RFQ_SPEC

        Set

          M2M_Quote_Number=

          <cfif form.M2M_Quote_Number neq "">

          <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.M2M_Quote_Number#">,

          <cfelse>

          NULL,

          </cfif>

         

          Rev=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Rev#">,

         

          Standard_Part_Number=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Standard_Part_Number#">,

         

          Generic_Description=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Generic_Description#">,

         

          Received_By=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Received_By_Initials#">,

         

          Assigned_By=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Assigned_By_Initials#">,

         

          RFQ_Leader=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.RFQ_Leader_Initials#">,

         

          Jsquad_Coordinator=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Jsquad_Initials#">,

         

          Complete_Date=

          <cfif form.complete_Date IS "">

          NULL,

          <cfelse>

          <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Complete_Date#">,

          </cfif>

         

          Highest_Quantity_Request=

          <cfif form.Highest_Quantity_Request neq "">

           <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Highest_Quantity_Request#">,

          <cfelse>

           NULL,

          </cfif>

         

          New_Custom_PO_Received_Date=

          <cfif New_Custom_PO_Received_Date IS "">

          Null,

          <cfelse>

          <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.New_Custom_PO_Received_Date#">,

          </cfif>

         

          Sales_Order_Number=

          <cfif form.Sales_Order_Number neq "">

          <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Sales_Order_Number#">,

          <cfelse>

          NULL,

          </cfif>

         

          Order_Request = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Order_Request#">,

         

          Customer_Reason_Rejecting=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Customer_Reason_Rejecting#">,

         

          Budgetary_Quote=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Budgetary_Quote#">,

         

          RFQ_Expected_Value=

          <cfif form.RFQ_Expected_Value neq "">

          <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.RFQ_Expected_Value#">,

          <cfelse>

          NULL,

          </cfif>

         

          Possible_Status=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Possible_Order#">,

         

          Sales_Status=

          <cfif form.Sales_Order_Number neq "">

          "Ordered",

          <cfelseif form.Sales_Order_Number IS "">

          <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Sales_Options#">,

          </cfif>

         

          Additional_Comments=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Additional_Comments#">,

         

          End_Customer_Name=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.End_Customer_Name#">,

          Where ItemID=<cfqueryparam cfsqltype="cf_sql_integer" value="#form.ItemID#">

        </cfquery>

    </cfif>

    J
    jamie61880AuthorCorrect answer
    Inspiring
    November 3, 2015

    BKBK,

         Thanks, but what I just found out is that I needed to change the input of the word Ordered in single quotes instead of double quotes, so the if statement reads like this now:

    <cfif Sales_Order_Number IS Not "">Sales_Status='Ordered',

      <cfelseif Sales_Order_Number IS "">Sales_Status='#form.Sales_Options#',

      </cfif>

    Thanks again.

    WolfShade
    WolfShade
    Legend
    November 3, 2015

    @jaime61880, you should always use CFQUERYPARAM for values.  One of the nice things about it is that you don't have to use single quotes - CF will detect the datatype and use whatever is necessary, automagically.  Plus, it's a great defense against SQL injection or XSS attacks.

    V/r,

    ^_^

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices