I'm completely stuck.............

[Aaron Smith]

I post a message about this, but noone responded.. Well, I've played
around as much as I can and I'm completely stuck and out of ideas.

This should be a really simple task, but it seems that it is beyond me
why this has to be so hard to do.

I have two tables, the ID is an Autonumber in Table 1. Table two is the
child to the previous table. Table 2 is in a grid. I enter information
in table 1, then I go to the grid and enter a row there. Then I do to
save, which does the Update, and it fails on table one saying that an
object hasn't been instantied, but it still saves the record, but also
creates another record, with a new ID from the SQL server. The previous
record still has 0 in the ID, and has the table 2 record related to it.
The new record in table 1 has no child records. If I just simply hit
save again, it comes up with this Foreign Key constraint error that says
that parent records must exist for this child record.

I've tried removing the ID in the insert and update statments for the
child, I've tried saving the parent before entering the grid. (I'm doing
an EndCurrentEdit right now) Nothing seems to fix it. A lot of changes
make it worse.

How the heck are you supposed to do this? It seems like a simple task,
but it's becoming a severe head ache.
--
---
Aaron Smith
Remove -1- to E-Mail me. Spam Sucks.

[Nick Malik [Microsoft]]

My advice:

don't use Identity columns as primary keys. Use GUIDs.
Your app can generate the primary key value without any help from SQL Server
(no need for a round trip).

So, when a user wants to add a parent row, create the guid and associate it
with the new data.
When the user wants to add a child row, use the Guid as the foreign key.

Save to the database. SQL doesn't autogenerate the Guids, so there's no
conflict, and no need for a round trip.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
"Aaron Smith" <th**********@smithcentral.net> wrote in message
news:5q*****************@newssvr31.news.prodigy.co m...

I post a message about this, but noone responded.. Well, I've played
around as much as I can and I'm completely stuck and out of ideas.

This should be a really simple task, but it seems that it is beyond me
why this has to be so hard to do.

I have two tables, the ID is an Autonumber in Table 1. Table two is the
child to the previous table. Table 2 is in a grid. I enter information
in table 1, then I go to the grid and enter a row there. Then I do to
save, which does the Update, and it fails on table one saying that an
object hasn't been instantied, but it still saves the record, but also
creates another record, with a new ID from the SQL server. The previous
record still has 0 in the ID, and has the table 2 record related to it.
The new record in table 1 has no child records. If I just simply hit
save again, it comes up with this Foreign Key constraint error that says
that parent records must exist for this child record.

I've tried removing the ID in the insert and update statments for the
child, I've tried saving the parent before entering the grid. (I'm doing
an EndCurrentEdit right now) Nothing seems to fix it. A lot of changes
make it worse.

How the heck are you supposed to do this? It seems like a simple task,
but it's becoming a severe head ache.
--
---
Aaron Smith
Remove -1- to E-Mail me. Spam Sucks.

[Aaron Smith]

Nick Malik [Microsoft] wrote:

My advice:

don't use Identity columns as primary keys. Use GUIDs.
Your app can generate the primary key value without any help from SQL Server
(no need for a round trip).

So, when a user wants to add a parent row, create the guid and associate it
with the new data.
When the user wants to add a child row, use the Guid as the foreign key.

Save to the database. SQL doesn't autogenerate the Guids, so there's no
conflict, and no need for a round trip.

Took your advice. Started to use Guids. The error messages have gone
away. One problem... No child records are being saved to the SQL Server
now. Even if I create a parent, save, close the program and open it,
then add child records. No errors. No children in the server. It does an
update, the DataSet.HasChanges() returns false. Look at the table on the
server, no children.

--
---
Aaron Smith
Remove -1- to E-Mail me. Spam Sucks.

[Aaron Smith]

Nick Malik [Microsoft] wrote:

fascinating.

Code would help. It's probably a minor oversight somewhere.

It's fixed.

After messing with it all day (for the last 4 days) I have finally
solved the issue. Now, keep in mind I haven't been using VB.Net for very
long.. With that said. Here is my original update procedure:

Public Function UpdateData() As Boolean
Dim bErr As Boolean = True
Try
Me.CourseDA.Update(CoursesDS1, "Courses")
Me.InstClassDA.Update(CoursesDS1, "InstClasses")
Catch ex As Exception
Misc.ShowError(ex.Message())
bErr = False
End Try
Return bErr
End Function

Courses is parent, InstClasses is child.

Bad idea, I guess. When the update is done for Courses, all the
changed/added/deleted rows in InstClasses have their RowState set to
Unchanged. I guess when update is done, it sets all the rowstates to
Unchanged for every table in the DataSet.

I now do this:

Public Function UpdateData() As Boolean
Dim bErr As Boolean = True
Try
Dim cdt As DataTable = CoursesDS1.Courses.GetChanges()
Dim cldt As DataTable = CoursesDS1.InstClasses.GetChanges()
If Not cdt Is Nothing Then
Me.CourseDA.Update(cdt)
End If
If Not cldt Is Nothing Then
Me.InstClassDA.Update(cldt)
End If
CoursesDS1.Merge(cdt)
CoursesDS1.Merge(cldt)
CoursesDS1.AcceptChanges()
Catch ex As Exception
Misc.ShowError(ex.Message())
bErr = False
End Try
Return bErr
End Function

And it saves everything just fine. Now I just have to figure out how to
do the deletes because you have to delete the child first... Then I
should be ok.

--
---
Aaron Smith
Remove -1- to E-Mail me. Spam Sucks.

[Nick Malik [Microsoft]]

clever. I'm glad you figured it out. I just got back online. Sorry I
couldn't help sooner.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
"Aaron Smith" <th**********@smithcentral.net> wrote in message
news:In*****************@newssvr31.news.prodigy.co m...

Nick Malik [Microsoft] wrote:

fascinating.

Code would help. It's probably a minor oversight somewhere.

It's fixed.

After messing with it all day (for the last 4 days) I have finally
solved the issue. Now, keep in mind I haven't been using VB.Net for very
long.. With that said. Here is my original update procedure:

Public Function UpdateData() As Boolean
Dim bErr As Boolean = True
Try
Me.CourseDA.Update(CoursesDS1, "Courses")
Me.InstClassDA.Update(CoursesDS1, "InstClasses")
Catch ex As Exception
Misc.ShowError(ex.Message())
bErr = False
End Try
Return bErr
End Function

Courses is parent, InstClasses is child.

Bad idea, I guess. When the update is done for Courses, all the
changed/added/deleted rows in InstClasses have their RowState set to
Unchanged. I guess when update is done, it sets all the rowstates to
Unchanged for every table in the DataSet.

I now do this:

Public Function UpdateData() As Boolean
Dim bErr As Boolean = True
Try
Dim cdt As DataTable = CoursesDS1.Courses.GetChanges()
Dim cldt As DataTable = CoursesDS1.InstClasses.GetChanges()
If Not cdt Is Nothing Then
Me.CourseDA.Update(cdt)
End If
If Not cldt Is Nothing Then
Me.InstClassDA.Update(cldt)
End If
CoursesDS1.Merge(cdt)
CoursesDS1.Merge(cldt)
CoursesDS1.AcceptChanges()
Catch ex As Exception
Misc.ShowError(ex.Message())
bErr = False
End Try
Return bErr
End Function

And it saves everything just fine. Now I just have to figure out how to
do the deletes because you have to delete the child first... Then I
should be ok.

--
---
Aaron Smith
Remove -1- to E-Mail me. Spam Sucks.