October 31, 2007 at 5:02 pm
I have been battling this for 2 days! I could write a book on what does not work.
I have even tried entering the data manually through the 'Open Table' SSMS function from the Object Explorer. I only have 5 records in the table. The first 4 I added a week or so ago during dev from the ASP.Net 2.0 (w VB) website that is the front end for this DB. I use a DAL between the site and DB.
I added two new fields the other day and everything seemed to work fine. Now I get 'Siteid CANNOT BE NULL'.
I abandoned attempts to Insert from the Website. The following was done directly through SSMS.
I switched all of the columns (except PK) to allow nulls. I could add/update a record, no problem, even with directly typed data in some fields. I could also add one with only one field populated (some have defaults, some do not).
Here is the table code:
USE [bro122]
GO
/****** Object: Table [dbo].[tbsaDrem] Script Date: 10/31/2007 18:46:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbsaDrem](
[DremID] [int] IDENTITY(1,1) NOT NULL,
[CustID] [int] NOT NULL CONSTRAINT [DF_tbsaDrem_CustID] DEFAULT ((0)),
[RealityYN] [bit] NULL CONSTRAINT [DF_tbsaDrem_ContractYN] DEFAULT ((0)),
[DreamName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tbsaDrem_DreamName] DEFAULT ('New (Modify Name)'),
[SiteID] [int] NOT NULL,
[SitePremPric] [money] NULL CONSTRAINT [DF_tbsaDrem_SiteBasePric] DEFAULT ((0)),
[NbhdBasePric] [money] NULL CONSTRAINT [DF_tbsaDrem_NbhdBasePric] DEFAULT ((0)),
[PlanCtlgID] [int] NOT NULL,
[PlanPric] [money] NULL CONSTRAINT [DF_tbsaDrem_PlanPric] DEFAULT ((0)),
[SpecTotl] [money] NULL CONSTRAINT [DF_tbsaDrem_SpecTotl] DEFAULT ((0)),
[DremTotl] AS ((([NbhdBasePric]+[SitePremPric])+[PlanPric])+[SpecTotl]) PERSISTED,
[Modified] [datetime] NOT NULL CONSTRAINT [DF_tbsaDrem_Modified] DEFAULT (getdate()),
[ModUser] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbsaDrem_ModUser] DEFAULT ('None'),
[Created] [datetime] NOT NULL CONSTRAINT [DF_tbsaScenario_CreatedDate] DEFAULT (getdate()),
[ts] [timestamp] NOT NULL,
CONSTRAINT [PK_tbsaDremID] PRIMARY KEY CLUSTERED
(
[DremID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This is flagged with a Trigger when a Dream becomes a Reality. ( contract is ratified.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tbsaDrem', @level2type=N'COLUMN',@level2name=N'RealityYN'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Heading Data for Each Dream (Scenario) for Customers. Each Dream can and must have only one Customer, Site, Plan. Dream Items are stored in the tbsaDreamItem table.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tbsaDrem'
GO
ALTER TABLE [dbo].[tbsaDrem] WITH CHECK ADD CONSTRAINT [FK_tbsaDrem_tbjoSite] FOREIGN KEY([SiteID])
REFERENCES [dbo].[tbjoSite] ([SiteID])
GO
ALTER TABLE [dbo].[tbsaDrem] CHECK CONSTRAINT [FK_tbsaDrem_tbjoSite]
GO
ALTER TABLE [dbo].[tbsaDrem] WITH CHECK ADD CONSTRAINT [FK_tbsaDrem_tbpdPlanCtlg] FOREIGN KEY([PlanCtlgID])
REFERENCES [dbo].[tbsaCtlg] ([CtlgID])
GO
ALTER TABLE [dbo].[tbsaDrem] CHECK CONSTRAINT [FK_tbsaDrem_tbpdPlanCtlg]
GO
ALTER TABLE [dbo].[tbsaDrem] WITH CHECK ADD CONSTRAINT [FK_tbsaDrem_tbsaCust] FOREIGN KEY([CustID])
REFERENCES [dbo].[tbsaCust] ([CustID])
GO
ALTER TABLE [dbo].[tbsaDrem] CHECK CONSTRAINT [FK_tbsaDrem_tbsaCust]
Any help is enormously appreciated!!! :crazy:
October 31, 2007 at 5:24 pm
Let me make sure I understand the problem. You are adding a row with a non-null siteid and you get the 'siteid cannot be NULL' message.
Can you create an insert statement, or capture the insert that is failing, and post that and the results of it's execution?
Is there a trigger on this table that is not shown in this script that may be attempting to update another table and that insert or update may be causing the error?
November 1, 2007 at 1:01 am
i'll second David. Post the insert statements, and check for triggers.
Profiler should help you capture insert statements sent from the front end.
p.s. Run a DBCC CheckTable onthat table, just the be sure tha no corruption has crept in.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 1, 2007 at 4:19 am
Here is the Trigger on this table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Steve Holzer
-- Create date: 30 Oct 2007
-- Description:Adds Current Prices to Dream
-- =============================================
ALTER TRIGGER [dbo].[Ins_Drem_Pric_Defaults]
ON [dbo].[tbsaDrem]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO tbsaDrem
(SitePremPric, NbhdBasePric, PlanPric)
SELECT tbjoSite.SiteSellPremium, tbjoNbhd.NbhdSiteSell, tbsaCtlg.Price
FROM inserted, tbsaDrem AS tbsaDrem_1 INNER JOIN
tbjoSite ON tbsaDrem_1.SiteID = tbjoSite.SiteID INNER JOIN
tbjoNbhd ON tbjoSite.NbhdID = tbjoNbhd.NbhdID INNER JOIN
tbsaCtlg ON tbsaDrem_1.PlanCtlgID = tbsaCtlg.CtlgID
WHERE tbsaDrem_1.DremID = inserted.DremID
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
That is it!!!!!!!!!!!!! See what too much coffee, not enough sleep, and being a NEWB will do to a person! I pull my hair out, if I had any.
My trigger is trying to INSERT a new record instead of UPDATING the record I just inserted.
Why can't computers do what we want them to do instead of what we tell them to do?!?!?!
Thank you.
I am going to re-write the trigger. I will let you know if I hve any more challenges!
November 1, 2007 at 4:48 am
I can't thank you enough GilaMonster!!!:D
I rewrote the trigger and it worked!!!!!!!
November 1, 2007 at 5:19 am
And thanks to David also
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 1, 2007 at 11:42 am
OOPS:blush: and to David too!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply