Cannot enter data into database

13 Aug 2005 6:19 PM

Karolus

I'm a SQL newbie and am totally perplexed. I have tried to enter the
following data and I get the subsequent message.

INSERT INTO t_Answer ( AnswerID, AssessmentID, QuestionID, EvaluationValue,
AnswerText, CreationUser_ID, RevisionUser_ID )
VALUES ( 457 , 130, 4, '14', 'testing what I want to do better', 152, 152 )

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

As you can see, I'm not entering any datetime data. I do, however, have two
fields that are date time fields.

I don't know what to do next so that i can enter data in the database.

I've tried to enter info into another table and got the same message.

Thanks for your help,

Karolus

13 Aug 2005 8:24 PM

John Bell

Hi

At http://www.aspfaq.com/etiquette.asp?id=5006 there is instructions to post
DDL which would tell us what datatypes your columns are.

Check if there are any triggers on this table or invalid defaults e.g.

CREATE TABLE MyDate ( ID INT, ADATE datetime NOT NULL DEFAULT 'Help' )

INSERT INTO MyDate (id) values (1)

gives your error.

John

Show quote

"Karolus" wrote:

> I'm a SQL newbie and am totally perplexed. I have tried to enter the
> following data and I get the subsequent message.
>
> INSERT INTO t_Answer ( AnswerID, AssessmentID, QuestionID, EvaluationValue,
> AnswerText, CreationUser_ID, RevisionUser_ID )
> VALUES ( 457 , 130, 4, '14', 'testing what I want to do better', 152, 152 )
>
> Server: Msg 241, Level 16, State 1, Line 1
> Syntax error converting datetime from character string.
>
> As you can see, I'm not entering any datetime data. I do, however, have two
> fields that are date time fields.
>
> I don't know what to do next so that i can enter data in the database.
>
> I've tried to enter info into another table and got the same message.
>
> Thanks for your help,
>
> Karolus

14 Aug 2005 10:06 PM

Karolus

Thanks, John, I got the document. Here is the table definition. (Thanks for
the advice.)
CREATE TABLE [t_Answer] (
    [AnswerID] [CodeID] NOT NULL ,
    [AssessmentID] [CodeID] NOT NULL ,
    [QuestionID] [CodeID] NOT NULL ,
    [EvaluationValue] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [AnswerText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [RecordStatusID] [RecordStatusID] NOT NULL ,
    [RecordID] [RecordID] IDENTITY (1, 1) NOT NULL ,
    [CreationUser_ID] [KeyForeign] NOT NULL ,
    [CreationDtm] [DateTmCreated] NOT NULL ,
    [RevisionUser_ID] [KeyForeign] NOT NULL ,
    [RevisionDtm] [DateTimeRequired] NOT NULL ,
    CONSTRAINT [COMMENTID] PRIMARY KEY CLUSTERED
    (
        [AnswerID],
        [AssessmentID],
        [QuestionID]
    ) ON [PRIMARY] ,
    CONSTRAINT [fk_Answer_AssessmentID_Assessment] FOREIGN KEY
    (
        [AssessmentID]
    ) REFERENCES [t_Assessment] (
        [AssessmentID]
    ),
    CONSTRAINT [fk_Answer_QuestionID_QuestionID] FOREIGN KEY
    (
        [QuestionID]
    ) REFERENCES [t_QuestionID] (
        [QuestionID]
    )
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I hope this information is helpful. I don't understand what I'm screwing up.

Show quote

"John Bell" wrote:

> Hi
>
> At http://www.aspfaq.com/etiquette.asp?id=5006 there is instructions to post
> DDL which would tell us what datatypes your columns are.
>
> Check if there are any triggers on this table or invalid defaults e.g.
>
> CREATE TABLE MyDate ( ID INT, ADATE datetime NOT NULL DEFAULT 'Help' )
>
> INSERT INTO MyDate (id) values (1)
>
> gives your error.
>
> John
>
> "Karolus" wrote:
>
> > I'm a SQL newbie and am totally perplexed. I have tried to enter the
> > following data and I get the subsequent message.
> >
> > INSERT INTO t_Answer ( AnswerID, AssessmentID, QuestionID, EvaluationValue,
> > AnswerText, CreationUser_ID, RevisionUser_ID )
> > VALUES ( 457 , 130, 4, '14', 'testing what I want to do better', 152, 152 )
> >
> > Server: Msg 241, Level 16, State 1, Line 1
> > Syntax error converting datetime from character string.
> >
> > As you can see, I'm not entering any datetime data. I do, however, have two
> > fields that are date time fields.
> >
> > I don't know what to do next so that i can enter data in the database.
> >
> > I've tried to enter info into another table and got the same message.
> >
> > Thanks for your help,
> >
> > Karolus

15 Aug 2005 3:11 AM

Brian Selzer

Please supply the definitions of the user-defined types: CodeID,
RecordStatusID, RecordID, KeyForeign, DateTmCreated, and DateTimeRequired.
I suspect the problem is to be found there, or in a default attached to a
user defined type.

Show quote

"Karolus" <Karo***@discussions.microsoft.com> wrote in message
news:B32CFD3E-BF21-471D-8E32-F33B5E616DE1@microsoft.com...
> Thanks, John, I got the document. Here is the table definition. (Thanks
for
> the advice.)
> CREATE TABLE [t_Answer] (
> [AnswerID] [CodeID] NOT NULL ,
> [AssessmentID] [CodeID] NOT NULL ,
> [QuestionID] [CodeID] NOT NULL ,
> [EvaluationValue] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AnswerText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [RecordStatusID] [RecordStatusID] NOT NULL ,
> [RecordID] [RecordID] IDENTITY (1, 1) NOT NULL ,
> [CreationUser_ID] [KeyForeign] NOT NULL ,
> [CreationDtm] [DateTmCreated] NOT NULL ,
> [RevisionUser_ID] [KeyForeign] NOT NULL ,
> [RevisionDtm] [DateTimeRequired] NOT NULL ,
> CONSTRAINT [COMMENTID] PRIMARY KEY CLUSTERED
> (
> [AnswerID],
> [AssessmentID],
> [QuestionID]
> ) ON [PRIMARY] ,
> CONSTRAINT [fk_Answer_AssessmentID_Assessment] FOREIGN KEY
> (
> [AssessmentID]
> ) REFERENCES [t_Assessment] (
> [AssessmentID]
> ),
> CONSTRAINT [fk_Answer_QuestionID_QuestionID] FOREIGN KEY
> (
> [QuestionID]
> ) REFERENCES [t_QuestionID] (
> [QuestionID]
> )
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
>
> I hope this information is helpful. I don't understand what I'm screwing
up.
>
> "John Bell" wrote:
>
> > Hi
> >
> > At http://www.aspfaq.com/etiquette.asp?id=5006 there is instructions to
post
> > DDL which would tell us what datatypes your columns are.
> >
> > Check if there are any triggers on this table or invalid defaults e.g.
> >
> > CREATE TABLE MyDate ( ID INT, ADATE datetime NOT NULL DEFAULT 'Help' )
> >
> > INSERT INTO MyDate (id) values (1)
> >
> > gives your error.
> >
> > John
> >
> > "Karolus" wrote:
> >
> > > I'm a SQL newbie and am totally perplexed. I have tried to enter the
> > > following data and I get the subsequent message.
> > >
> > > INSERT INTO t_Answer ( AnswerID, AssessmentID, QuestionID,
EvaluationValue,
> > > AnswerText, CreationUser_ID, RevisionUser_ID )
> > > VALUES ( 457 , 130, 4, '14', 'testing what I want to do better', 152,
152 )
> > >
> > > Server: Msg 241, Level 16, State 1, Line 1
> > > Syntax error converting datetime from character string.
> > >
> > > As you can see, I'm not entering any datetime data. I do, however,
have two
> > > fields that are date time fields.
> > >
> > > I don't know what to do next so that i can enter data in the database.
> > >
> > > I've tried to enter info into another table and got the same message.
> > >
> > > Thanks for your help,
> > >
> > > Karolus

15 Aug 2005 4:58 AM

Kim

Hi Karolus,

I agree with John. You may need to check triggers entering values into
RevisionDtm and CreationDtm.
You have four not null fields for that you didn't provide values in
your insert statement,
so you may have triggers for them.

I hope it help you.

Kim

15 Aug 2005 7:29 AM

John Bell

Hi

CreationDtm and Revisiondtm are not nullable, but you have not supplied a
value in your statement, therefore you would get an error for that but
normally not the one you have stated, therefore I think you may have defaults
in the user defined types DateTimeRequired and DateTmCreated that are
incorrect.

John

Show quote

"Karolus" wrote:

> Thanks, John, I got the document. Here is the table definition. (Thanks for
> the advice.)
> CREATE TABLE [t_Answer] (
>     [AnswerID] [CodeID] NOT NULL ,
>     [AssessmentID] [CodeID] NOT NULL ,
>     [QuestionID] [CodeID] NOT NULL ,
>     [EvaluationValue] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>     [AnswerText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>     [RecordStatusID] [RecordStatusID] NOT NULL ,
>     [RecordID] [RecordID] IDENTITY (1, 1) NOT NULL ,
>     [CreationUser_ID] [KeyForeign] NOT NULL ,
>     [CreationDtm] [DateTmCreated] NOT NULL ,
>     [RevisionUser_ID] [KeyForeign] NOT NULL ,
>     [RevisionDtm] [DateTimeRequired] NOT NULL ,
>     CONSTRAINT [COMMENTID] PRIMARY KEY CLUSTERED
>     (
>         [AnswerID],
>         [AssessmentID],
>         [QuestionID]
>     ) ON [PRIMARY] ,
>     CONSTRAINT [fk_Answer_AssessmentID_Assessment] FOREIGN KEY
>     (
>         [AssessmentID]
>     ) REFERENCES [t_Assessment] (
>         [AssessmentID]
>     ),
>     CONSTRAINT [fk_Answer_QuestionID_QuestionID] FOREIGN KEY
>     (
>         [QuestionID]
>     ) REFERENCES [t_QuestionID] (
>         [QuestionID]
>     )
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
>
> I hope this information is helpful. I don't understand what I'm screwing up.
>
> "John Bell" wrote:
>
> > Hi
> >
> > At http://www.aspfaq.com/etiquette.asp?id=5006 there is instructions to post
> > DDL which would tell us what datatypes your columns are.
> >
> > Check if there are any triggers on this table or invalid defaults e.g.
> >
> > CREATE TABLE MyDate ( ID INT, ADATE datetime NOT NULL DEFAULT 'Help' )
> >
> > INSERT INTO MyDate (id) values (1)
> >
> > gives your error.
> >
> > John
> >
> > "Karolus" wrote:
> >
> > > I'm a SQL newbie and am totally perplexed. I have tried to enter the
> > > following data and I get the subsequent message.
> > >
> > > INSERT INTO t_Answer ( AnswerID, AssessmentID, QuestionID, EvaluationValue,
> > > AnswerText, CreationUser_ID, RevisionUser_ID )
> > > VALUES ( 457 , 130, 4, '14', 'testing what I want to do better', 152, 152 )
> > >
> > > Server: Msg 241, Level 16, State 1, Line 1
> > > Syntax error converting datetime from character string.
> > >
> > > As you can see, I'm not entering any datetime data. I do, however, have two
> > > fields that are date time fields.
> > >
> > > I don't know what to do next so that i can enter data in the database.
> > >
> > > I've tried to enter info into another table and got the same message.
> > >
> > > Thanks for your help,
> > >
> > > Karolus

15 Aug 2005 12:47 PM

Karolus

Thanks all for your help. I VERY much appreciate it. I've come for Sybase
and am overwhelmed by the difference. Here are the domain definitions and
the code that created the table, t_answers. I assumed that I should provide
a datetime, but that didn't work either...so I'm confused.

Here's the information:

/*==============================================================*/
/* Domain: DateTmCreated                                        */
/*==============================================================*/
sp_addtype DateTmCreated, 'datetime' , 'not null'
go

/*==============================================================*/
/* Domain: DateTimeNotMandatory                                 */
/*==============================================================*/
sp_addtype DateTimeNotMandatory, 'datetime'
go

/*==============================================================*/
/* Domain: DateTimeRequired                                     */
/*==============================================================*/
sp_addtype DateTimeRequired, 'datetime' , 'not null'
go

create default D_DateTimeRequired
    as 'CURRENT TIMESTAMP'
go

sp_bindefault D_DateTimeRequired, DateTimeRequired
go

/*==============================================================*/
/* Domain: DateTm                                               */
/*==============================================================*/
sp_addtype DateTm, 'datetime'
go

create default D_DateTm
    as 'CURRENT TIMESTAMP'
go

sp_bindefault D_DateTm, DateTm
go

/*==============================================================*/
/* Table : t_Answer                                             */
/*==============================================================*/
create table t_Answer (
AnswerID             CodeID               not null,
AssessmentID         CodeID               not null,
QuestionID           CodeID               not null,
EvaluationValue      text                 null,
AnswerText           text                 null,
RecordStatusID       RecordStatusID       not null,
RecordID             RecordID             identity,
CreationUser_ID      KeyForeign           not null,
CreationDtm          DateTmCreated        not null,
RevisionUser_ID      KeyForeign           not null,
RevisionDtm          DateTimeRequired     not null,
constraint COMMENTID primary key (AnswerID, AssessmentID, QuestionID)
)
go

Show quote

"John Bell" wrote:

> Hi
>
> CreationDtm and Revisiondtm are not nullable, but you have not supplied a
> value in your statement, therefore you would get an error for that but
> normally not the one you have stated, therefore I think you may have defaults
> in the user defined types DateTimeRequired and DateTmCreated that are
> incorrect.
>
> John
>
> "Karolus" wrote:
>
> > Thanks, John, I got the document. Here is the table definition. (Thanks for
> > the advice.)
> > CREATE TABLE [t_Answer] (
> >     [AnswerID] [CodeID] NOT NULL ,
> >     [AssessmentID] [CodeID] NOT NULL ,
> >     [QuestionID] [CodeID] NOT NULL ,
> >     [EvaluationValue] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >     [AnswerText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >     [RecordStatusID] [RecordStatusID] NOT NULL ,
> >     [RecordID] [RecordID] IDENTITY (1, 1) NOT NULL ,
> >     [CreationUser_ID] [KeyForeign] NOT NULL ,
> >     [CreationDtm] [DateTmCreated] NOT NULL ,
> >     [RevisionUser_ID] [KeyForeign] NOT NULL ,
> >     [RevisionDtm] [DateTimeRequired] NOT NULL ,
> >     CONSTRAINT [COMMENTID] PRIMARY KEY CLUSTERED
> >     (
> >         [AnswerID],
> >         [AssessmentID],
> >         [QuestionID]
> >     ) ON [PRIMARY] ,
> >     CONSTRAINT [fk_Answer_AssessmentID_Assessment] FOREIGN KEY
> >     (
> >         [AssessmentID]
> >     ) REFERENCES [t_Assessment] (
> >         [AssessmentID]
> >     ),
> >     CONSTRAINT [fk_Answer_QuestionID_QuestionID] FOREIGN KEY
> >     (
> >         [QuestionID]
> >     ) REFERENCES [t_QuestionID] (
> >         [QuestionID]
> >     )
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> > GO
> >
> >
> > I hope this information is helpful. I don't understand what I'm screwing up.
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > At http://www.aspfaq.com/etiquette.asp?id=5006 there is instructions to post
> > > DDL which would tell us what datatypes your columns are.
> > >
> > > Check if there are any triggers on this table or invalid defaults e.g.
> > >
> > > CREATE TABLE MyDate ( ID INT, ADATE datetime NOT NULL DEFAULT 'Help' )
> > >
> > > INSERT INTO MyDate (id) values (1)
> > >
> > > gives your error.
> > >
> > > John
> > >
> > > "Karolus" wrote:
> > >
> > > > I'm a SQL newbie and am totally perplexed. I have tried to enter the
> > > > following data and I get the subsequent message.
> > > >
> > > > INSERT INTO t_Answer ( AnswerID, AssessmentID, QuestionID, EvaluationValue,
> > > > AnswerText, CreationUser_ID, RevisionUser_ID )
> > > > VALUES ( 457 , 130, 4, '14', 'testing what I want to do better', 152, 152 )
> > > >
> > > > Server: Msg 241, Level 16, State 1, Line 1
> > > > Syntax error converting datetime from character string.
> > > >
> > > > As you can see, I'm not entering any datetime data. I do, however, have two
> > > > fields that are date time fields.
> > > >
> > > > I don't know what to do next so that i can enter data in the database.
> > > >
> > > > I've tried to enter info into another table and got the same message.
> > > >
> > > > Thanks for your help,
> > > >
> > > > Karolus

15 Aug 2005 1:01 PM

David Portas

Looks like the error is just the CREATE DEFAULT statements.
CURRENT_TIMESTAMP is a keyword and mustn't be quoted as a string:

CREATE DEFAULT D_DateTm
AS CURRENT_TIMESTAMP

--
David Portas
SQL Server MVP
--