Access to SQL issues when updating a number field

[jamie61880]

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>

[BKBK]

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>

[jamie61880 · Accepted solution]

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]

@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,

^_^

[Steve Sommers]

Please don't put that code into a production environment -- or even a testing environment exposed to the outside world. At a minimum use the CFQUERYPARAM for all the form values used to build the query. Your query above, even with the fix is ripe for a SQL injection attack and most likely the initial screams will be the ColdFusion is insecure!