Access to SQL issues when updating a number field

hi everyone. have edit page updates fields in database. converted access sql. can update page fine part, except when try update sales order number reason. i'm not sure why. think has sql database, don't know what. field called sales_order_number. when try enter number in field, 500 internal server error. below query use display info. on action page, , updating of table.

we have updated our coldfusion mx7 11. not sure if make difference or not otherwise. can 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#

<!--- , contacts.rfq_num >=0  --->

order 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 "">entry_date=null,

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

  m2m_quote_number= <cfif isdefined("form.m2m_quote_number") , #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 "">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 "">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") , #form.rfq_expected_value# neq

  ""> '#form.rfq_expected_value#',

  <cfelse>

  null,

  </cfif>

  possible_status='#form.possible_order#',

 

  <!--- code if sales order number entered, sales status field entered "ordered". if sales order number field blank, sales status field entered whatever drop down menu item chosen.

--->

  <cfif sales_order_number not "">sales_status="ordered",

  <cfelseif sales_order_number "">sales_status='#form.sales_options#',

  </cfif>

 

 

  additional_comments='#form.additional_comments#',

  end_customer_name='#form.end_customer_name#'

itemid=#form.itemid#

</cfquery>

take whole. have made suggestions. note full column names, have guessed, , use of cfqueryparam. have guessed datatypes varchar , 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

    

    rfq_numbers.itemid=<cfqueryparam cfsqltype="cf_sql_integer" value="#form.itemid#"> 

 

    order 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 "">

      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 "">

      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 "">

      <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#">,

 

      itemid=<cfqueryparam cfsqltype="cf_sql_integer" value="#form.itemid#">

    </cfquery>

</cfif>

More discussions in ColdFusion

adobe