Database type issue when inserting

  • Thread starter Thread starter Jim P.
  • Start date Start date
J

Jim P.

I've used mySQL extensively but I'm new to SQL 2k (MSDE) and .NET. When
inserting data I'm having data type issue but they are a little weird. I
have 2 tables, Items and Subjects.

Items Type Length Allow Nulls
----------------------------------------------
ID INT 4 NO (Identity, Seed 1,
Increment 1)
Title INT 4 NO
SubjectID INT 4 NO
Description CHAR 100 NO


Subjects Type Length Allow Nulls
----------------------------------------------
ID INT 4 NO (Identity, Seed 1,
Increment 1)
Name CHAR 50 NO
Desc CHAR 100 YES


I have a foreign key relationship between Items.SubjectID and Subjects.ID,
with Subjects.ID as the primary and Items.SubjectID the foreign key.. I
currently have 8 entries in my Subjects table. I have a form that I enter
the Item information into and choose the Subject from a drop down box. When
I submit the changes to the database it fails inserting to the Items table
if SubjectID is higher than 3. Otherwise everything works great.

I know this sounds bizarre, but it also happens in Visual Studio (Server
Explorer) when I attempt to add data to the Items table.. I have no problem
entering a Items.SubjectID as long as it is 3, 2, or 1. Otherwise I get the
following error:
 
This is just a guess, but I'd empty both tables and start from scratch and
see if it happens. . Just to test it, get out of VS and through query
analyzer or EM, try manually adding those values and make sure everything
works there. Since you have an identity on Both keys, what I suspect is
happening is the increment value is kicking in on the child table and they
are out of sync. I think that's probaby your problem.

Cheers,

Bill
 
Heh, Hello Jim

Forgive me for asking the obvious...but do you have more than 3 rows in the
Subjects table? What I mean, do other identities besides 1,2, and 3 exist
in the subject table

:-)
Ibrahim
 
Back
Top