Urgent:Newbie:Autoincrement violates foreign key con1straints

[al]

Hi,

I'm reading an xml file into a dataset and entering the data into an
SQL server express database.

I've got an employee table (empid as primary key) and an employee photo
table (empid as foreign key)

My xml file contains an employee element and multiple employee photo
elements.

I'm using auto increments and setting the seed and step to -1 in both
the database and in my code (Vb.net - i'm setting this using the drag
and drop dataset - as i said i'm a newbie and just getting trying to
get a quick solution)

if i have just one one photo element, it enters into my datagrid
(windows form) ok

as

photoid empid photofilename
-1 -1 emp1.jpg

i think the problem arises when i use multiple employeephoto elements

the photoid will increment okay but as its still the same employee
there will be a a constraint on the foreign key (at least this is
guess!!)

if anyone has any suggestions on how i could get around this, it would
be GREATLY APPRECIATED.
i've been looking at this for the past few days and getting
increasingly frustrated.

thanks in advance,

alan

 

[Chris Chilvers]

photoid empid photofilename
-1 -1 emp1.jpg

Just a quick question; empid is not set to auto increment in this table?

As a side note, the autoincrement value in your code won't matter as
after writing a new row your code needs to read what value the database
assigned to the auto increment column (see 'scope_identity()').

 

[Otis Mukinfus]

Hi,

I'm reading an xml file into a dataset and entering the data into an
SQL server express database.

I've got an employee table (empid as primary key) and an employee photo
table (empid as foreign key)

My xml file contains an employee element and multiple employee photo
elements.

I'm using auto increments and setting the seed and step to -1 in both
the database and in my code (Vb.net - i'm setting this using the drag
and drop dataset - as i said i'm a newbie and just getting trying to
get a quick solution)

if i have just one one photo element, it enters into my datagrid
(windows form) ok

as

photoid empid photofilename
-1 -1 emp1.jpg

i think the problem arises when i use multiple employeephoto elements

the photoid will increment okay but as its still the same employee
there will be a a constraint on the foreign key (at least this is
guess!!)

if anyone has any suggestions on how i could get around this, it would
be GREATLY APPRECIATED.
i've been looking at this for the past few days and getting
increasingly frustrated.

thanks in advance,

alan

The employeeID in the phototable cannot be set to unique. Make sure you haven't
done that. You should not set the employeeID column in the photo table to
autoincrement. Make sure you haven't done that. If I'm telling you something
you already know, it's because I don't know your experience level.

You should not be setting any of the autoincrement columns in the database to
-1. Set the steps in the database to 1.

Sceppa's book ADO.NET has some good examples and explanations of your situation.


Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[al]

thank you for your quick reply...
i set the increment and seed to 1 in the database but to -1 in the vb
code...
which i presume is so it does not overwrite the settings in the
database
(when adding the datatables to the dataset)
i've very little experience with vb and have been tutorials from all
angles to get up to speed..
i've experience in javascript/html ...
i'll use what you've said and have another go....
thanks again for the help

alan

 

[Otis Mukinfus]

thank you for your quick reply...
i set the increment and seed to 1 in the database but to -1 in the vb
code...
which i presume is so it does not overwrite the settings in the
database
(when adding the datatables to the dataset)
i've very little experience with vb and have been tutorials from all
angles to get up to speed..
i've experience in javascript/html ...
i'll use what you've said and have another go....
thanks again for the help

alan

Well, unless I was being forced to use VB and had Java experience, I wouldn't be
trying to learn VB. Not because there is anything wrong with VB. I just prefer
the C style syntax.

You would probably have no trouble with C#, but the framework is the same in
both, so the learning of the framework is the biggest step in learning .NET.

By the way, Sceppa's book has both VB and C# examples. It came out during .NET
1.0 beta, but the information on what you are doing is still good. I think he
has a new version coming out in July.


Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[al]

thanks all for the help so far,
i've got the multiple photoelements displaying in the datagrid
now...but no multiple inserts...
i guess what i'm looking for is my photo_details sqldataadapter to
insert more than one record at a time...
i've got a piece of code that reads the xml tags by name and counts
them...maybe i will have to loop through some sqlinsertcommands ?
will i have to do the multiple record insert in a seperate function to
the rest of my dataadapters ?
as i said, these are currently handled in drag and drop fashion
(generated code)
i've got one sqladapter for employee and one for photo...
can a sqladapter handle these multiple inserts ?

thanks again...
oh and had a look for that book you recommended....its out in july...
think its a necessary purchase okay....

 

[Otis Mukinfus]

thanks all for the help so far,
i've got the multiple photoelements displaying in the datagrid
now...but no multiple inserts...
i guess what i'm looking for is my photo_details sqldataadapter to
insert more than one record at a time...
i've got a piece of code that reads the xml tags by name and counts
them...maybe i will have to loop through some sqlinsertcommands ?
will i have to do the multiple record insert in a seperate function to
the rest of my dataadapters ?
as i said, these are currently handled in drag and drop fashion
(generated code)
i've got one sqladapter for employee and one for photo...
can a sqladapter handle these multiple inserts ?

thanks again...
oh and had a look for that book you recommended....its out in july...
think its a necessary purchase okay....

Al,

To be perfectly honest with you, I rarely use the DataSets and DataAdapters
except for getting list data. I find it better (for me) and more efficient to
use SqlCommand objects to do updates inserts and deletes, but to use them with
grids requires you to design objects you can display via lists and that becomes
more code intensive than most folks want.

You can use an SqlCommand object with a stored procedure or SQL text. If you're
used to working with SQL and don't want to go through the learning curve of
using DataSets for the whole ball of wax, I'd recommend taking a look at
SqlCommands. Since you mentioned you are reading XML that may be a whole
different ball game.

To answer your question about multiple inserts; Yes DataAdapters/DataSets can
perform multiple inserts and updates on the database, but if the employee and
his/her photo is in the same table, then it will only be one insert for both for
each row in the DataTable contained in the DataSet.

I wish I could be of more help to you but I'm afraid it's going to be hard for
you to get the answers you want without digging into some books.

Here is another book I think is a good one for ADO.NET 2.0 "Pro ADO.NET 2.0" by
Sahil Malik. His style is a bit different than Sceppas, but his book is good
and already at B&N ;o)

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[al]

oits,

many thanks again for your help....
its looking like sqlcommand alright...
i wanted to do a quick/easy understood approach
i followed online tutorials pretty easily in this fashion....but
sometimes my lack of knowledge means i dont quite know how to phrase my
questions unfortunately....
likewise with the books i should read....
my project is part of a prototype so if i could get a quick piece of
code for demonstration and i get to next stage i could try to do this
properly i suppose...
going by tutorials it would seem that handcoding (sqlcommands etc) are
the correct way to go....
i'm using one dataset containing two tables (employee and photo)
and two sqladapters (one for employee and one for photo)
my database has two tables (one for emp/one for photo)
i have two datagrids (as above emp/photo)

it fails on the second sqldataadapter i think...when there is more than
one row to insert into the photo table...
i shall search for multiple inserts for sqldataadapters and hopefully
this will get me through this stage...

again, thanks for all your help
it is greatly appreciated...

alan

 

[Otis Mukinfus]

oits,

many thanks again for your help....
its looking like sqlcommand alright...
i wanted to do a quick/easy understood approach
i followed online tutorials pretty easily in this fashion....but
sometimes my lack of knowledge means i dont quite know how to phrase my
questions unfortunately....
likewise with the books i should read....
my project is part of a prototype so if i could get a quick piece of
code for demonstration and i get to next stage i could try to do this
properly i suppose...
going by tutorials it would seem that handcoding (sqlcommands etc) are
the correct way to go....
i'm using one dataset containing two tables (employee and photo)
and two sqladapters (one for employee and one for photo)
my database has two tables (one for emp/one for photo)
i have two datagrids (as above emp/photo)

it fails on the second sqldataadapter i think...when there is more than
one row to insert into the photo table...
i shall search for multiple inserts for sqldataadapters and hopefully
this will get me through this stage...

again, thanks for all your help
it is greatly appreciated...

alan

You're welcome Alan.

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com