Insert Error

Report · Jul 27, 2011

Hi

This code works in CF5, but delivers this error in CF8:

Error Executing Database Query.[Macromedia][SQLServer JDBC Driver][SQLServer]Cannot insert the value NULL into column 'last_modified', table 'edebt_international_com.dbo.DEBTORS'; column does not allow nulls

Here is the code:

alert('You have entered an incorrect date, please check your data.')

history.back()

</script>

</cfcatch>

Any help would be appreciated.

Also If I change ODBC to JDBC, I get the next line 'you have entered an incorrect date...'

I am looking to enter todays date.

Thanks

Report · Jul 27, 2011

The code you show us is not the code that is erroring. Also, it's not a CF error, it's a DB error, so I'm not sure you're telling the full story in saying it works on CF5 but not CF8.

Post the code that's actually erroring. Also, check the SQL that's being passed to the DB rather than the CF code that creates the SQL: it's the SQL that's causing the problem.

--

Adam

Report · Jul 27, 2011

Not sure how to check the SQL, its a hosted CF8 & SQL2005 server.

The only other difference is I run SQL2000, as opposed to SQL2005.

Cant see where else the error is coming from....

Report · Jul 27, 2011

Both the SQL being sent to the DB and the exact line number (and a snippet of the erroring code) should be being displayed along with the error message.

You perhaps don't have Robust Exception Handling switched on, which is essential when troubleshooting errors. Is this a production server, or your development server?

If you've changed your DB, you could well have changed your DB schema too (either on purpose of inadvertantly), and perhaps are no longer allowing nulls in that column.

What's the table-create SQL for the table on the SQL Server 2000 server; and what's the same SQL on the new 2005 server?

--

Adam

Report · Jul 28, 2011

I did a dump and your right, the code is generating a date:

FIRST_NAME	[empty string]
LAST_MODIFIED	{ts '2011-07-28 18:20:27'}
LAST_NAME	[empty string]

Just have to figure out now why the database is saying its a 'null'

Also the column has never accepted nulls, and its correct copy on sql 2005.

Report · Jul 28, 2011

I did a dump and your right, the code is generating a date:
FIRST_NAME [empty string]
LAST_MODIFIED {ts '2011-07-28 18:20:27'}
LAST_NAME
[empty string]
Just have to figure out now why the database is saying its a 'null'

Well, first things first, do what I suggested: check the SQL that CF is passing to the DB. If you have robust exception handling switched on, it'll be right in front of your nose on the screen.

Post the whole error message.

--

Adam

Report · Jul 28, 2011

Ok see what you mean now by sql from table.

Will have to wait until tommorrow morning to see if my host can turn on the Robust Exception Handling

/****** Object: Table [dbo].[DEBTORS] Script Date: 07/28/2011 19:23:10 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[DEBTORS](

[debtor_id] [int] IDENTITY(1,1) NOT NULL,

[business_id] [int] NOT NULL,

[debtor_type] [int] NOT NULL,

[first_name] [nvarchar](200) NULL,

[middle_name] [nvarchar](200) NULL,

[last_name] [nvarchar](200) NULL,

[date_of_birth] [datetime] NULL,

[company_name] [nvarchar](200) NULL,

[entity_id] [int] NOT NULL,

[trading_name] [nvarchar](200) NULL,

[phone_prefix] [nvarchar](50) NULL,

[phone] [nvarchar](200) NULL,

[mobile] [nvarchar](200) NULL,

[email] [nvarchar](200) NULL,

[recoverable_costs] [int] NOT NULL,

[postal_address] [nvarchar](200) NULL,

[postal_suburb] [nvarchar](200) NULL,

[postal_city] [nvarchar](200) NULL,

[postcode] [nvarchar](200) NULL,

[last_modified] [datetime] NOT NULL,

[times_used] [int] NOT NULL,

[date_created] [datetime] NULL,

[customer_note] [ntext] NULL,

[admin_note] [ntext] NULL,

[data_checked] [int] NOT NULL,

[state] [nvarchar](50) NULL,

[country_id] [int] NOT NULL,

[dob_day] [nchar](10) NULL,

[dob_month] [nchar](10) NULL,

[dob_year] [nchar](10) NULL,

[btnEdit_OK] [nchar](10) NULL,

CONSTRAINT [PK_DEBTORS] PRIMARY KEY CLUSTERED

(

[debtor_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[DEBTORS] ADD CONSTRAINT [DF_DEBTORS_business_id] DEFAULT (0) FOR [business_id]

GO

ALTER TABLE [dbo].[DEBTORS] ADD CONSTRAINT [DF_DEBTORS_debtor_type] DEFAULT (0) FOR [debtor_type]

GO

ALTER TABLE [dbo].[DEBTORS] ADD CONSTRAINT [DF_DEBTORS_entity_id] DEFAULT (0) FOR [entity_id]

GO

ALTER TABLE [dbo].[DEBTORS] ADD CONSTRAINT [DF_DEBTORS_recoverable_costs] DEFAULT (0) FOR [recoverable_costs]

GO

ALTER TABLE [dbo].[DEBTORS] ADD CONSTRAINT [DF_DEBTORS_times_used] DEFAULT (0) FOR [times_used]

GO

ALTER TABLE [dbo].[DEBTORS] ADD CONSTRAINT [DF_DEBTORS_data_checked] DEFAULT (0) FOR [data_checked]

GO

ALTER TABLE [dbo].[DEBTORS] ADD CONSTRAINT [DF_DEBTORS_country_id] DEFAULT (0) FOR [country_id]

GO

Report · Jul 28, 2011

Will have to wait until tommorrow morning to see if my host can turn on the Robust Exception Handling

OK, so this sounds like this is on your production environment (if it's a managed remote server)? Can you not replicate this in your dev environment? It's all well and good spotting a problem on the prodcution environment - unfortunately that's where they often present themselves! - but you should not be trying to fix it there. Try to replicate the situation in your dev environment.

And you should definitely not be switching on robust exception handling (and definitely not switching on debugging, not that you suggested you were) on a production server.

--

Adam

Report · Jul 28, 2011

Put a CFtry/catch around the CFQUERY so that it doesn't error out to CF, then CFDUMP the

CFERROR scope to see the error info. Also, Try putting a RESULT="X" in the CFQUERY and follow it with a CFDUMP VAR="#X#" to

be able to see the actual SQL that is being passed. Finally, just put the createodbcdatetime in the SQL direction, not via the CFSET. You don't need the parseDate stuff - just do #createODBCdatetime(now())#

Let us know what you get for output, especially the exact string that comes back from SQL on the error where it shows the SQL statement it was trying to execute.

-reed