This might seem like a simple matter of trying to insert a row with ID=20 when there's already one with that ID, but the problem is a bit more complicated. The table is supposed to auto-increment the value for the primary key when a new record is inserted. But no matter what I do, I can't seem to insert more than one record into this table.
Expand|Select|Wrap|Line Numbers
- USE [opinion8]
- GO
- /****** Object: Table [dbo].[surveyQuestions] Script Date: 01/20/2009 00:00:09 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- SET ANSI_PADDING ON
- GO
- CREATE TABLE [dbo].[surveyQuestions](
- [surveyQuestionID] [int] IDENTITY(1,1) NOT NULL,
- [surveyQuestionTypeID] [smallint] NOT NULL,
- [created] [datetime] NOT NULL CONSTRAINT [DF__surveyQue__creat__656C112C] DEFAULT (getdate()),
- [approvedFlag] [bit] NOT NULL,
- [activeFlag] [bit] NOT NULL,
- [surveyPageID] [int] NOT NULL,
- [displayOrder] [smallint] NOT NULL,
- [requiredFlag] [bit] NOT NULL,
- [parentQuestionID] [int] NOT NULL CONSTRAINT [DF__surveyQue__paren__6754599E] DEFAULT ((0)),
- [clientID] [int] NOT NULL,
- [surveyID] [int] NOT NULL,
- [question] [varchar](5000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_surveyQuestions_question] DEFAULT ('[none]'),
- [instructions] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_surveyQuestions_instructions] DEFAULT ('[none]'),
- [requiredText] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_surveyQuestions_requiredText] DEFAULT ('[none]'),
- CONSTRAINT [PK_surveyQuestions] PRIMARY KEY CLUSTERED
- (
- [surveyQuestionID] ASC
- )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
- CONSTRAINT [IX_surveyQuestions] UNIQUE NONCLUSTERED
- (
- [parentQuestionID] ASC
- )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- SET ANSI_PADDING OFF
- GO
- ALTER TABLE [dbo].[surveyQuestions] WITH CHECK ADD CONSTRAINT [FK_surveyQuestions_clients] FOREIGN KEY([clientID])
- REFERENCES [dbo].[clients] ([clientID])
- GO
- ALTER TABLE [dbo].[surveyQuestions] CHECK CONSTRAINT [FK_surveyQuestions_clients]
- GO
- ALTER TABLE [dbo].[surveyQuestions] WITH CHECK ADD CONSTRAINT [FK_surveyQuestions_surveyPages] FOREIGN KEY([surveyPageID])
- REFERENCES [dbo].[surveyPages] ([surveyPageID])
- GO
- ALTER TABLE [dbo].[surveyQuestions] CHECK CONSTRAINT [FK_surveyQuestions_surveyPages]
- GO
- ALTER TABLE [dbo].[surveyQuestions] WITH CHECK ADD CONSTRAINT [FK_surveyQuestions_surveyPages1] FOREIGN KEY([surveyPageID])
- REFERENCES [dbo].[surveyPages] ([surveyPageID])
- GO
- ALTER TABLE [dbo].[surveyQuestions] CHECK CONSTRAINT [FK_surveyQuestions_surveyPages1]
- GO
- ALTER TABLE [dbo].[surveyQuestions] WITH CHECK ADD CONSTRAINT [FK_surveyQuestions_surveys] FOREIGN KEY([surveyID])
- REFERENCES [dbo].[surveys] ([surveyID])
- GO
- ALTER TABLE [dbo].[surveyQuestions] CHECK CONSTRAINT [FK_surveyQuestions_surveys]
Expand|Select|Wrap|Line Numbers
- <cftransaction>
- <cfquery name="qInsertQuestion" datasource="#application.DSN#">
- insert into surveyQuestions (
- question,
- instructions,
- surveyQuestionTypeID,
- approvedFlag,
- activeFlag,
- surveyPageID,
- displayOrder,
- requiredFlag,
- requiredText,
- parentQuestionID,
- clientID,
- surveyID,
- created
- ) values (
- <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="5000" value="#left(arguments.oQuestion.getValue('question',true),5000)#">,
- <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="500" value="#left(arguments.oQuestion.getValue('instructions',true),500)#">,
- <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('surveyQuestionTypeID',true)#">,
- <cfqueryparam cfsqltype="cf_sql_bit" value="#arguments.oQuestion.getValue('approvedFlag',true)#">,
- <cfqueryparam cfsqltype="cf_sql_bit" value="#arguments.oQuestion.getValue('activeFlag',true)#">,
- <cfqueryparam cfsqltype="cf_sql_smallint" value="#arguments.oQuestion.getValue('surveyPageID',true)#">,
- <cfqueryparam cfsqltype="cf_sql_smallint" value="#arguments.oQuestion.getValue('displayOrder',true)#">,
- <cfqueryparam cfsqltype="cf_sql_bit" value="#arguments.oQuestion.getValue('requiredFlag',true)#">,
- <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="200" value="#left(arguments.oQuestion.getValue('requiredText',true),200)#">,
- <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('parentQuestionID',true)#">,
- <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('clientID',true)#">,
- <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('surveyID',true)#">,
- <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
- )
- select @@identity as questionID
- </cfquery>
- <cfset newID = qInsertQuestion.questionID>
- <cfquery name="qInsertQuestionModule" datasource="#application.DSN#">
- insert into surveyQuestionsTextbox (
- surveyQuestionID,
- maxLength,
- fieldSize,
- formatHint,
- pickType,
- clientID
- ) values (
- <cfqueryparam cfsqltype="cf_sql_integer" value="#newID#">,
- <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('maxLength',true)#">,
- <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('fieldSize',true)#">,
- <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="100" value="#arguments.oQuestion.getValue('formatHint',true)#">,
- <cfqueryparam cfsqltype="cf_sql_char" maxlength="2" value="#arguments.oQuestion.getValue('pickType',true)#">,
- <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('clientID',true)#">
- )
- </cfquery>
- </cftransaction>
Expand|Select|Wrap|Line Numbers
- USE [opinion8]
- GO
- /****** Object: Table [dbo].[surveyQuestionsTextbox] Script Date: 01/20/2009 00:02:57 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- SET ANSI_PADDING ON
- GO
- CREATE TABLE [dbo].[surveyQuestionsTextbox](
- [id] [int] IDENTITY(1,1) NOT NULL,
- [formatHint] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_surveyQuestionsTextbox_formatHint] DEFAULT ('[none]'),
- [fieldSize] [int] NOT NULL CONSTRAINT [DF_surveyQuestionsTextbox_fieldSize] DEFAULT ((40)),
- [maxLength] [int] NOT NULL CONSTRAINT [DF_surveyQuestionsTextbox_maxLength] DEFAULT ((200)),
- [pickType] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_surveyQuestionsTextbox_pickType] DEFAULT ('RP'),
- [clientID] [int] NOT NULL,
- [surveyQuestionID] [int] NOT NULL,
- CONSTRAINT [PK_surveyQuestionsTextbox] PRIMARY KEY CLUSTERED
- (
- [id] ASC
- )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
- CONSTRAINT [IX_surveyQuestionsTextbox] UNIQUE NONCLUSTERED
- (
- [id] ASC
- )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- SET ANSI_PADDING OFF
- GO
- ALTER TABLE [dbo].[surveyQuestionsTextbox] WITH CHECK ADD CONSTRAINT [FK_surveyQuestionsTextbox_clients] FOREIGN KEY([clientID])
- REFERENCES [dbo].[clients] ([clientID])
- GO
- ALTER TABLE [dbo].[surveyQuestionsTextbox] CHECK CONSTRAINT [FK_surveyQuestionsTextbox_clients]
- GO
- ALTER TABLE [dbo].[surveyQuestionsTextbox] WITH CHECK ADD CONSTRAINT [FK_surveyQuestionsTextbox_surveyQuestions] FOREIGN KEY([surveyQuestionID])
- REFERENCES [dbo].[surveyQuestions] ([surveyQuestionID])
- GO
- ALTER TABLE [dbo].[surveyQuestionsTextbox] CHECK CONSTRAINT [FK_surveyQuestionsTextbox_surveyQuestions]