Problem: erratic success of an entry form to a SQL database.

Report · Jul 09, 2009

Description: ColdFusion 8 form with 169 fields inserts into a SQL database running 2000 SQL. on Windows 2003 server.

1. Worked consistently between the hours of 8:30am and 1:00pm and then ceases to insert until the next morning.

2. On July 8, 2009, repeated the same pattern and as a test, tried to enter a record from offsite at 12:20am July 9 to ascertain when the application would again successfully insert a record. The entry form succeeded!

3. At 7:38am July 9 the entry failed.

4. No error message, simply returns the last successful entry.

Any ideas?

Report · Jul 09, 2009

Things to check in ColdFusion:

1. Is there any information in the ColdFusion logs?

2. Can you post your code?

3. Can other queries of the SQL Server database and/or target table be run from your ColdFusion code before 8:30 and after 1:00?

Things to check in SQL Server:

1. Does your table have any triggers that change how inserts are handled?

2. Are there any long running queries, jobs, or maintainence tasks that are locking the table and preventing inserts?

3. Try running the SQL profiler tool. This will show the queries being executed on your SQL server.

http://www.petefreitag.com/item/674.cfm

http://msdn.microsoft.com/en-us/library/ms979207.aspx

http://msdn.microsoft.com/en-us/library/aa173918(SQL.80).aspx

4. Is there any information in the SQL logs or Windows event logs?

Report · Jul 09, 2009

There is no information in the ColdFusion logs, and the entry form is

1842 lines and the action page is 457 lines long, so I'm attaching just

the insert query and the update query. Last night I was able to

successfully insert a record at 12:30 am, but now (since 7:30 am) I have

been unable to successfully insert any new records (current time 3:26

pm). I can still view and edit previous records successfully at any

time, however, I have not re-written the update query with the

cfqueryparams.

I will check the SQL server as you suggest... thanks for all your help

and direction.

Report · Jul 09, 2009

Oops! here is the insert query:

<CFIF Isdefined("Form.RecordID")> <CFUPDATE dataSource="vic" tableName="CRIME_VIC_WEB" formFields="#Form.FieldList#"> <CFLOCATION url="CrimeVictim_RecordView.cfm?RecordID=#Form.RecordID#"> <CFELSE>  <cfparam name="form.VCTM_REIMBRS_TYPE" default="None"> <cfparam name="form.VCTM_REIMBRS_INSUR" default=""> <cfparam name="form.MILEAGE" default=""> <cfparam name="form.VIC_KNOW_SUS" default=""> <cfparam name="form.LIVE_SAME_HOUSE" default=""> <cfparam name="form.SUS_CHARGED" default=""> <cfparam name="form.SUS_AJ_1" default=""> <cfparam name="form.SUS_AJ_2" default=""> <cfparam name="form.SUS_AJ_3" default=""> <cfparam name="form.SUS_AJ_4" default=""> <cfparam name="form.VCTM_HOSPITALIZED" default=""> <cfparam name="form.VCTM_EMP_DOI" default=""> <cfparam name="form.VCTM_EMP_E_LOSS" default=""> <cfparam name="form.VCTM_EMP_W_LOSS" default=""> <CFQUERY dataSource="vic"> INSERT INTO CRIME_VIC_WEB ( VCTM_SALUTATION, VCTM_NAME_F, VCTM_NAME_MI, VCTM_NAME_L, VCTM_NAME_EXT, VCTM_ADD1, VCTM_CITY, VCTM_COUNTY, VCTM_STATE, VCTM_ZIP, VCTM_PHONE_HOME, VCTM_PHONE_WORK, VCTM_PHONE_CELL, VCTM_PHONE_PAGER, VCTM_PHONE_FAX, VCTM_EMAIL, VCTM_DOB, VCTM_SSS_NO, VCTM_SEX, VCTM_MARITAL_STATUS, VCTM_EMP_NAME, VCTM_EMP_ADDRESS, VCTM_EMP_CITY, VCTM_EMP_STATE, VCTM_EMP_ZIP, CLMT_VICTIM_FLAG, CLMT_SALUTATION, CLMT_NAME_F, CLMT_NAME_MI, CLMT_NAME_L, CLMT_NAME_EXT, CLMT_ADD1, CLMT_CITY, CLMT_COUNTY, CLMT_STATE, CLMT_ZIP, CLMT_PHONE_HOME, CLMT_PHONE_WORK, CLMT_PHONE_CELL, CLMT_PHONE_PAGER, CLMT_PHONE_FAX, CLMT_EMAIL1, CLMT_DOB, CLMT_SSS_NO, CLMT_SEX, CLMT_MARITAL_STATUS, CLMT_RELATION, CLMT_EMP_NAME, CLMT_EMP_ADDRESS, CLMT_EMP_CITY, CLMT_EMP_STATE, CLMT_EMP_ZIP, CRM_DATE, CRM_TIME, CRM_LOCATION, CRM_COUNTY, CRM_TYPE, POL_AGENCY, POL_ADDRESS, POL_CITY, POL_STATE, POL_ZIP, RPT_DATE, RPT_TIME, RPT_72HOURS, RPT_WHO, LIVE_SAME_HOUSE, SUS_NAME1, SUS_AJ_1, SUS_NAME2, SUS_AJ_2, SUS_NAME3, SUS_AJ_3, SUS_NAME4, SUS_AJ_4, VIC_KNOW_SUS, VIC_IN_WHAT_WAY, SUS_CHARGED, SUS_EXPLAIN, NARRATIVE, INJURIES, VCTM_HOS_NAME, VCTM_HOS_ADDRESS, VCTM_HOS_CITY, VCTM_HOS_STATE, VCTM_HOS_ZIP, VCTM_HOS_PHONE, VCTM_HOS_PATIENT_NO, VCTM_HOS_ADM_DATE, VCTM_HOS_DIS_DATE, VCTM_HOS_NAME2, VCTM_HOS_ADDRESS2, VCTM_HOS_CITY2, VCTM_HOS_STATE2, VCTM_HOS_ZIP2, VCTM_FUN_NAME, VCTM_FUN_ADDRESS, VCTM_FUN_CITY, VCTM_FUN_STATE, VCTM_FUN_ZIP, VCTM_FUN_DOD, ATY_SAL, ATY_NAME_F, ATY_NAME_MI, ATY_NAME_L, ATY_NAME_EXT, ATY_FIRM, ATY_ADDRESS, ATY_ADDRESS2, ATY_CITY, ATY_STATE, ATY_ZIP, ATY_PHONE, ATY_EMAIL, ATY_RECORD, VCTM_DEP_1FNAME, VCTM_DEP_1INTIAL, VCTM_DEP_1LNAME, VCTM_DEP_1ADD, VCTM_DEP_1CITY, VCTM_DEP_1STATE, VCTM_DEP_1ZIP, VCTM_DEP_1RELATION, VCTM_DEP_1DOB, VCTM_DEP_2FNAME, VCTM_DEP_2INTIAL, VCTM_DEP_2LNAME, VCTM_DEP_2ADD, VCTM_DEP_2CITY, VCTM_DEP_2STATE, VCTM_DEP_2ZIP, VCTM_DEP_2RELATION, VCTM_DEP_2DOB, VCTM_DEP_3FNAME, VCTM_DEP_3INTIAL, VCTM_DEP_3LNAME, VCTM_DEP_3ADD, VCTM_DEP_3CITY, VCTM_DEP_3STATE, VCTM_DEP_3ZIP, VCTM_DEP_3RELATION, VCTM_DEP_3DOB, MILEAGE, MILEAGE_SPECIFY, VCTM_EMP_DOI, VCTM_EMP_E_LOSS, VCTM_EMP_P_LOSS, VCTM_EMP_W_LOSS, VCTM_EMP_D_LOSS, VCTM_EMP_REMARKS, VCTM_REIMBRS_TYPE, VCTM_REIMBRS_INSUR, VCTM_INSUR_NAME, VCTM_INSUR_ADD, VCTM_INSUR_CITY, VCTM_INSUR_STATE, VCTM_INSUR_ZIP, VCTM_EXEC_SALUTATION, VCTM_EXEC_NAME_F, VCTM_EXEC_NAME_MI, VCTM_EXEC_NAME_L, VCTM_EXEC_ADD, VCTM_EXEC_CITY, VCTM_EXEC_STATE, VCTM_EXEC_ZIP, VCTM_HOSPITALIZED, VCTM_EXEC_NAME_EXT, VCTM_EMP_PHONE, Submission_ID ) VALUES ( <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_SALUTATION#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_NAME_F#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_NAME_MI#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_NAME_L#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_NAME_EXT#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_ADD1#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_CITY#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_COUNTY#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_STATE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_ZIP#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_PHONE_HOME#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_PHONE_WORK#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_PHONE_CELL#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_PHONE_PAGER#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_PHONE_FAX#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EMAIL#">, <CFQUERYPARAM CFSQLType="CF_SQL_DATE" Value="#Form.VCTM_DOB#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_SSS_NO#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_SEX#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_MARITAL_STATUS#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EMP_NAME#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EMP_ADDRESS#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EMP_CITY#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EMP_STATE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EMP_ZIP#">, <CFQUERYPARAM CFSQLType="CF_SQL_BIT" Value="#Form.CLMT_VICTIM_FLAG#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_SALUTATION#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_NAME_F#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_NAME_MI#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_NAME_L#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_NAME_EXT#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_ADD1#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_CITY#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_COUNTY#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_STATE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_ZIP#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_PHONE_HOME#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_PHONE_WORK#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_PHONE_CELL#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_PHONE_PAGER#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_PHONE_FAX#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_EMAIL1#">, <CFQUERYPARAM CFSQLType="CF_SQL_DATE" Value="#Form.CLMT_DOB#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_SSS_NO#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_SEX#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_MARITAL_STATUS#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_RELATION#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_EMP_NAME#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_EMP_ADDRESS#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_EMP_CITY#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_EMP_STATE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CLMT_EMP_ZIP#">, <CFQUERYPARAM CFSQLType="CF_SQL_DATE" Value="#Form.CRM_DATE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CRM_TIME#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CRM_LOCATION#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CRM_COUNTY#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.CRM_TYPE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.POL_AGENCY#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.POL_ADDRESS#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.POL_CITY#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.POL_STATE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.POL_ZIP#">, <CFQUERYPARAM CFSQLType="CF_SQL_DATE" Value="#Form.RPT_DATE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.RPT_TIME#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.RPT_72HOURS#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.RPT_WHO#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.LIVE_SAME_HOUSE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.SUS_NAME1#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.SUS_AJ_1#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.SUS_NAME2#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.SUS_AJ_2#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.SUS_NAME3#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.SUS_AJ_3#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.SUS_NAME4#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.SUS_AJ_4#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VIC_KNOW_SUS#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VIC_IN_WHAT_WAY#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.SUS_CHARGED#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.SUS_EXPLAIN#">, <CFQUERYPARAM CFSQLType="CF_SQL_CLOB" Value="#Form.NARRATIVE#">, <CFQUERYPARAM CFSQLType="CF_SQL_CLOB" Value="#Form.INJURIES#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_HOS_NAME#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_HOS_ADDRESS#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_HOS_CITY#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_HOS_STATE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_HOS_ZIP#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_HOS_PHONE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_HOS_PATIENT_NO#">, <CFQUERYPARAM CFSQLType="CF_SQL_DATE" Value="#Form.VCTM_HOS_ADM_DATE#">, <CFQUERYPARAM CFSQLType="CF_SQL_DATE" Value="#Form.VCTM_HOS_DIS_DATE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_HOS_NAME2#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_HOS_ADDRESS2#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_HOS_CITY2#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_HOS_STATE2#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_HOS_ZIP2#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_FUN_NAME#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_FUN_ADDRESS#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_FUN_CITY#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_FUN_STATE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_FUN_ZIP#">, <CFQUERYPARAM CFSQLType="CF_SQL_DATE" Value="#Form.VCTM_FUN_DOD#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.ATY_SAL#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.ATY_NAME_F#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.ATY_NAME_MI#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.ATY_NAME_L#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.ATY_NAME_EXT#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.ATY_FIRM#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.ATY_ADDRESS#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.ATY_ADDRESS2#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.ATY_CITY#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.ATY_STATE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.ATY_ZIP#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.ATY_PHONE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.ATY_EMAIL#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.ATY_RECORD#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_1FNAME#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_1INTIAL#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_1LNAME#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_1ADD#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_1CITY#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_1STATE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_1ZIP#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_1RELATION#">, <CFQUERYPARAM CFSQLType="CF_SQL_DATE" Value="#Form.VCTM_DEP_1DOB#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_2FNAME#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_2INTIAL#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_2LNAME#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_2ADD#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_2CITY#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_2STATE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_2ZIP#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_2RELATION#">, <CFQUERYPARAM CFSQLType="CF_SQL_DATE" Value="#Form.VCTM_DEP_2DOB#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_3FNAME#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_3INTIAL#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_3LNAME#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_3ADD#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_3CITY#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_3STATE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_3ZIP#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_DEP_3RELATION#">, <CFQUERYPARAM CFSQLType="CF_SQL_DATE" Value="#Form.VCTM_DEP_3DOB#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.MILEAGE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.MILEAGE_SPECIFY#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EMP_DOI#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EMP_E_LOSS#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EMP_P_LOSS#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EMP_W_LOSS#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EMP_D_LOSS#">, <CFQUERYPARAM CFSQLType="CF_SQL_CLOB" Value="#Form.VCTM_EMP_REMARKS#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_REIMBRS_TYPE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_REIMBRS_INSUR#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_INSUR_NAME#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_INSUR_ADD#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_INSUR_CITY#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_INSUR_STATE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_INSUR_ZIP#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EXEC_SALUTATION#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EXEC_NAME_F#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EXEC_NAME_MI#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EXEC_NAME_L#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EXEC_ADD#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EXEC_CITY#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EXEC_STATE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EXEC_ZIP#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_HOSPITALIZED#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EXEC_NAME_EXT#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#Form.VCTM_EMP_PHONE#">, <CFQUERYPARAM CFSQLType="CF_SQL_Varchar" Value="#DateFormat(Now(),"yyyymmdd")##TimeFormat(Now(),"hhmmss")#"> ) </CFQUERY>

Report · Jul 09, 2009

Your insert query does not show up on the forum.

here is no information in the ColdFusion logs, and the entry form is 1842 lines and the action page is 457 lines long

With pages this large is it possible there is a logic error hiding somewhere in your code that prevents the CFQUERY insert block from being executed? I'd use SQL profiler to see if the insert statement is even being sent to the SQL Server.

Report · Jul 09, 2009

Will do...thanks!

Report · Jul 15, 2009

Martin,

As a simple test of whether this is SQL or ColdFusion related, when you are not able to make an update through the code, login as the ColdFusion user to the SQL database and attempt to insert a record. If that works, then try running a stored procedure that wasn't working. If there are issues, it should show you the error code or you can run profiler while running these queries to see what is happening.

If both of those work try the ColdFusion side again. If it still doesn't work, you know that the problem is in ColdFusion, not SQL related. If the problem is ColdFusion related, remove your error handling code to see what the problem might be.

This should start narrowing things down a little for you. Let us know which is causing the problem and any error codes you are receiving.

Report · Jul 15, 2009

I have now solved the problem.... I am embarrassed to say that the

insert was in fact working all along. Using the SQL profiler we were

able to see that the insert query was working, but the ID was improperly

formatted and therefore was returning the wrong record. The

submission_id was set to insert the date and time as the id but the mask

was written #DateFormat(Now(),"yyyymmdd")##TimeFormat(Now(),"hhmmss")#

instead of #DateFormat(Now(),"yyyymmdd")##TimeFormat(Now(),"HHmmss")#.

Therefore every day at 1:00pm and 1:00 am record id's were being sorted

out of sequence, returning the last record inserted prior to the change

from 12:59 to 1:00.

All of the suggestions I received were very helpful in narrowing down my

stupid mistake... many thanks!

Adobe Community

Problem: erratic success of an entry form to a SQL database.