Skip to main content
A
Anonymous
July 9, 2009
Question

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

  • July 9, 2009
  • 2 replies
  • 1275 views

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

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?

This topic has been closed for replies.

2 replies

CFMLGuru
CFMLGuru
Known Participant
July 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.

A
Anonymous
July 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!

J
JR__Bob__Dobbs-qSBHQ2
Inspiring
July 9, 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?

A
Anonymous
July 9, 2009

Oops! here is the insert query:

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 ( , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ) ]]>

J
JR__Bob__Dobbs-qSBHQ2
Inspiring
July 9, 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.

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