Highlighted

Access to SQL issues when updating a number field

Engaged ,
Oct 29, 2015

Copy link to clipboard

Copied

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,

     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.

Views

237

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Access to SQL issues when updating a number field

Engaged ,
Oct 29, 2015

Copy link to clipboard

Copied

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,

     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.

Views

238

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Oct 29, 2015 0
Adobe Community Professional ,
Oct 31, 2015

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 31, 2015 0
Engaged ,
Nov 03, 2015

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 03, 2015 0
LEGEND ,
Nov 03, 2015

Copy link to clipboard

Copied

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

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 03, 2015 0
Advocate ,
Nov 05, 2015

Copy link to clipboard

Copied

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!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 05, 2015 0