Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cathymomo
Starting Member
10 Posts |
 Posted - 2009-03-10 : 11:32:25
|
| Extension on the question I asked yesterday. These are my only 2 tables where the inserts are failing, both tables create perfect, they just error when I attempt to insert - if anyone can see what the problem is.create table ExpenseReport(ERNo integer not null IDENTITY,ERDesc varchar(255) not null,ERSubmitDate datetime DEFAULT GETDATE() not null,ERStatusDate datetime DEFAULT GETDATE() not null,ERStatus varchar(10) DEFAULT 'PENDING' not null check (ERStatus='PENDING' OR ERStatus='APPROVED' OR ERStatus='DENIED'),SubmitUserNo integer not null,ApprUserNo integer,CONSTRAINT ApprUserNo CHECK (ApprUserNo IS NOT NULL AND ERStatus IN('APPROVED','DENIED')),CONSTRAINT ERSubmitDate CHECK (ERSTATUS>=ERSubmitDate),PRIMARY KEY (ERNo),FOREIGN KEY (ApprUserNo) REFERENCES Users(UserNo) ON DELETE NO ACTION,FOREIGN KEY (SubmitUserNo) REFERENCES Users(UserNo));create table ExpenseItem(EINo integer not null IDENTITY, ExpDesc varchar(255) not null,ExpenseDate datetime DEFAULT GETDATE() not null,ExpAmt money DEFAULT '0' not null,ExpApprAmt money DEFAULT '0',ERNo integer not null,ECNo integer not null,AssetNo integer,CONSTRAINT ExpApprAmt CHECK (ExpApprAmt<=ExpAmt),PRIMARY KEY (EINo),FOREIGN KEY (ERNo) REFERENCES ExpenseReport ON DELETE CASCADE,FOREIGN KEY (ECNo) REFERENCES ExpCat,FOREIGN KEY (AssetNo) REFERENCES Asset ON DELETE SET NULL );Insert statements would be as follows:INSERT INTO ExpenseReport (ERNo, ERDesc, ERSubmitDate, ERStatusDate, ERStatus, SubmitUserNo, ApprUSerNo)VALUES (5,'Sales Presentation','9/30/2002','10/1/2002','APPROVED',7,4);INSERT INTO ExpenseItem(ExpDesc, ExpenseDate,ExpAmt, ExpApprAmt, ERNo, ECNo, AssetNo) VALUES ('Hilton','8/9/2002',99.00,99.00,1,7,NULL); |
|
|
heavymind
Posting Yak Master
115 Posts |
Posted - 2009-03-10 : 11:43:32
|
| Cannot insert explicit value for identity column in table 'ExpenseReport' when IDENTITY_INSERT is set to OFF.You should not list identity columns in insert statements. they are generated automatically...Thanks, VadymMCITP DBA 2005/2008Chief DBA at http://www.db-staff.com |
 |
|
|
cathymomo
Starting Member
10 Posts |
Posted - 2009-03-10 : 12:31:09
|
| Ya sorry, i had taken out the primary key already but pasted on the older version. It still doesnt work, think its something to do with date/time maybe? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-10 : 13:09:50
|
i think problem is this check constraintCONSTRAINT ERSubmitDate CHECK (ERSTATUS>=ERSubmitDate), here you're comparing between date and varchar value which is not possible. probabaly you meant this insteadCONSTRAINT ERSubmitDate CHECK (ERStatusDate>=ERSubmitDate), |
|
|
|
cathymomo
Starting Member
10 Posts |
Posted - 2009-03-10 : 13:24:23
|
| O can't believe I didn't spot that. That is probably the problem! Thanks a million!!!!!! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-10 : 13:26:51
|
| welcome |
|
|
|
|
|
|
|
|
INSERT Errors