how to update dataset w/ multiple table?

  • Thread starter Thread starter jaYPee
  • Start date Start date
J

jaYPee

I have search a lot of thread in google newsgroup and read a lot of
articles but still i don't know how to update the dataset that has 3
tables.

my 3 tables looks like the 3 tables from northwind database that has
an employees, orders, and order details.

the following are the 3 tables in my sql database
students
schyrsem
SchYrSemCourse

here's some variables i have

Private dsStudentCourse As DataSet
Private dtStudents As DataTable
Private dtSchYrSem As DataTable
Private dtSchYrSemCourse As DataTable
Private dvSchYrSem As DataView
Private dvSchYrSemCourse As DataView
Private sda As SqlDataAdapter

here's some of the code to fill the dataset

Dim scnnNW As New SqlConnection(strConn)

Dim strSQL As String = _
"SELECT IDNo, LastName, FirstName " & _
"FROM Students"

Dim scmd As New SqlCommand(strSQL, scnnNW)

sda = New SqlDataAdapter(scmd)

Dim scb As New SqlCommandBuilder(sda)

scb.GetUpdateCommand()

dsStudentCourse = New DataSet()
sda.Fill(dsStudentCourse, "Students")

scmd.CommandText = _
"SELECT SchYrSemID, IDNo, SchYr, Sem FROM
SchYrSem"

sda.Fill(dsStudentCourse, "SchYrSem")

scmd.CommandText = _
"SELECT SchYrSemCourseID, SchYrSemID,
Course.CourseID, CourseTitle, CourseDesc, Unit, [Hours/Week] FROM
Course INNER JOIN SchYrSemCourse ON Course.CourseID =
SchYrSemCourse.CourseID"

sda.Fill(dsStudentCourse, "SchYrSemCourse")

dtStudents = dsStudentCourse.Tables(0)
dtSchYrSem = dsStudentCourse.Tables(1)
dtSchYrSemCourse = dsStudentCourse.Tables(2)

dvSchYrSem = dtSchYrSem.DefaultView
dvSchYrSemCourse = dtSchYrSemCourse.DefaultView

i have also search the msdn for updating a multiple table but all i
can see is on how to save a single table only.

i would be very if someone can enlighten me on how to save a dataset
that has a multiple table in it.
 
Hello jay

'in the example code below it is assumed that each data

'adapter (MyAD1, MyAD2, MyAd3) have commands for update

'delete and inserts for the respective tables they are

'managing.

MyAD1.Update(MyDS.Tables(0))

MyAD2.Update(MyDS.Tables(1))

MyAD3.Update(MyDS.Tables(2))

'This is a very simplistic code example. If there

'are parent/child relationships involed here then you

'have to break up the process lets say the tables

'are 1 parent to two that is parent to 3. If you have

'updates, inserts, and deletes happening in all of

'the datase's tables then yoyu have to submit things

'in order.

'submit all the inserts first starting from the

'top table on down

MyAD1.Update(MyDS.Tables(0).Select("", "", DataViewRowState.Added))

MyAD1.Update(MyDS.Tables(1).Select("", "", DataViewRowState.Added))

MyAD1.Update(MyDS.Tables(2).Select("", "", DataViewRowState.Added))

'now submit the updated rows

MyAD1.Update(MyDS.Tables(0).Select("", "",
DataViewRowState.ModifiedCurrent))

MyAD1.Update(MyDS.Tables(1).Select("", "",
DataViewRowState.ModifiedCurrent))

MyAD1.Update(MyDS.Tables(2).Select("", "",
DataViewRowState.ModifiedCurrent))

'finally submit the deletions from the bottom up

MyAD3.Update(MyDS.Tables(2).Select("", "", DataViewRowState.Deleted))

MyAD2.Update(MyDS.Tables(1).Select("", "", DataViewRowState.Deleted))

MyAD1.Update(MyDS.Tables(0).Select("", "", DataViewRowState.Deleted))

'the above will work in most situations. This code is untested.



jaYPee said:
I have search a lot of thread in google newsgroup and read a lot of
articles but still i don't know how to update the dataset that has 3
tables.

my 3 tables looks like the 3 tables from northwind database that has
an employees, orders, and order details.

the following are the 3 tables in my sql database
students
schyrsem
SchYrSemCourse

here's some variables i have

Private dsStudentCourse As DataSet
Private dtStudents As DataTable
Private dtSchYrSem As DataTable
Private dtSchYrSemCourse As DataTable
Private dvSchYrSem As DataView
Private dvSchYrSemCourse As DataView
Private sda As SqlDataAdapter

here's some of the code to fill the dataset

Dim scnnNW As New SqlConnection(strConn)

Dim strSQL As String = _
"SELECT IDNo, LastName, FirstName " & _
"FROM Students"

Dim scmd As New SqlCommand(strSQL, scnnNW)

sda = New SqlDataAdapter(scmd)

Dim scb As New SqlCommandBuilder(sda)

scb.GetUpdateCommand()

dsStudentCourse = New DataSet()
sda.Fill(dsStudentCourse, "Students")

scmd.CommandText = _
"SELECT SchYrSemID, IDNo, SchYr, Sem FROM
SchYrSem"

sda.Fill(dsStudentCourse, "SchYrSem")

scmd.CommandText = _
"SELECT SchYrSemCourseID, SchYrSemID,
Course.CourseID, CourseTitle, CourseDesc, Unit, [Hours/Week] FROM
Course INNER JOIN SchYrSemCourse ON Course.CourseID =
SchYrSemCourse.CourseID"

sda.Fill(dsStudentCourse, "SchYrSemCourse")

dtStudents = dsStudentCourse.Tables(0)
dtSchYrSem = dsStudentCourse.Tables(1)
dtSchYrSemCourse = dsStudentCourse.Tables(2)

dvSchYrSem = dtSchYrSem.DefaultView
dvSchYrSemCourse = dtSchYrSemCourse.DefaultView

i have also search the msdn for updating a multiple table but all i
can see is on how to save a single table only.

i would be very if someone can enlighten me on how to save a dataset
that has a multiple table in it.
Click to expand...
 
Hi,

Thank u very much for the reply.

i tried your source code just the update only to simplify my problem

here's the source code modified from what you have told me to do.

sda.Update(dsStudentCourse.Tables(0).Select("", "",
DataViewRowState.ModifiedCurrent))
sda.Update(dsStudentCourse.Tables(1).Select("", "",
DataViewRowState.ModifiedCurrent))
sda.Update(dsStudentCourse.Tables(2).Select("", "",
DataViewRowState.ModifiedCurrent))

however having no errors during code execution when i call this code
but still after i close the form and open again i can't find the
changes i made after i save the data w/ the code above.

and yes this is a parent-child form

Hello jay

'in the example code below it is assumed that each data

'adapter (MyAD1, MyAD2, MyAd3) have commands for update

'delete and inserts for the respective tables they are

'managing.

MyAD1.Update(MyDS.Tables(0))

MyAD2.Update(MyDS.Tables(1))

MyAD3.Update(MyDS.Tables(2))

'This is a very simplistic code example. If there

'are parent/child relationships involed here then you

'have to break up the process lets say the tables

'are 1 parent to two that is parent to 3. If you have

'updates, inserts, and deletes happening in all of

'the datase's tables then yoyu have to submit things

'in order.

'submit all the inserts first starting from the

'top table on down

MyAD1.Update(MyDS.Tables(0).Select("", "", DataViewRowState.Added))

MyAD1.Update(MyDS.Tables(1).Select("", "", DataViewRowState.Added))

MyAD1.Update(MyDS.Tables(2).Select("", "", DataViewRowState.Added))

'now submit the updated rows

MyAD1.Update(MyDS.Tables(0).Select("", "",
DataViewRowState.ModifiedCurrent))

MyAD1.Update(MyDS.Tables(1).Select("", "",
DataViewRowState.ModifiedCurrent))

MyAD1.Update(MyDS.Tables(2).Select("", "",
DataViewRowState.ModifiedCurrent))

'finally submit the deletions from the bottom up

MyAD3.Update(MyDS.Tables(2).Select("", "", DataViewRowState.Deleted))

MyAD2.Update(MyDS.Tables(1).Select("", "", DataViewRowState.Deleted))

MyAD1.Update(MyDS.Tables(0).Select("", "", DataViewRowState.Deleted))

'the above will work in most situations. This code is untested.



jaYPee said:
I have search a lot of thread in google newsgroup and read a lot of
articles but still i don't know how to update the dataset that has 3
tables.

my 3 tables looks like the 3 tables from northwind database that has
an employees, orders, and order details.

the following are the 3 tables in my sql database
students
schyrsem
SchYrSemCourse

here's some variables i have

Private dsStudentCourse As DataSet
Private dtStudents As DataTable
Private dtSchYrSem As DataTable
Private dtSchYrSemCourse As DataTable
Private dvSchYrSem As DataView
Private dvSchYrSemCourse As DataView
Private sda As SqlDataAdapter

here's some of the code to fill the dataset

Dim scnnNW As New SqlConnection(strConn)

Dim strSQL As String = _
"SELECT IDNo, LastName, FirstName " & _
"FROM Students"

Dim scmd As New SqlCommand(strSQL, scnnNW)

sda = New SqlDataAdapter(scmd)

Dim scb As New SqlCommandBuilder(sda)

scb.GetUpdateCommand()

dsStudentCourse = New DataSet()
sda.Fill(dsStudentCourse, "Students")

scmd.CommandText = _
"SELECT SchYrSemID, IDNo, SchYr, Sem FROM
SchYrSem"

sda.Fill(dsStudentCourse, "SchYrSem")

scmd.CommandText = _
"SELECT SchYrSemCourseID, SchYrSemID,
Course.CourseID, CourseTitle, CourseDesc, Unit, [Hours/Week] FROM
Course INNER JOIN SchYrSemCourse ON Course.CourseID =
SchYrSemCourse.CourseID"

sda.Fill(dsStudentCourse, "SchYrSemCourse")

dtStudents = dsStudentCourse.Tables(0)
dtSchYrSem = dsStudentCourse.Tables(1)
dtSchYrSemCourse = dsStudentCourse.Tables(2)

dvSchYrSem = dtSchYrSem.DefaultView
dvSchYrSemCourse = dtSchYrSemCourse.DefaultView

i have also search the msdn for updating a multiple table but all i
can see is on how to save a single table only.

i would be very if someone can enlighten me on how to save a dataset
that has a multiple table in it.
Click to expand...
Click to expand...
 
Hello Jay

You have to have command ibjects associated with each Update, Insert,
and Delete action before anything will happen. Create three
command objects for each dataAdapter
specify their respective stored procedures or SQL text

Then associate them to the DataAdapter's InsertCommand, UpdateCommand,
and DeleteCommand properties.

Ibrahim




jaYPee said:
Hi,

Thank u very much for the reply.

i tried your source code just the update only to simplify my problem

here's the source code modified from what you have told me to do.

sda.Update(dsStudentCourse.Tables(0).Select("", "",
DataViewRowState.ModifiedCurrent))
sda.Update(dsStudentCourse.Tables(1).Select("", "",
DataViewRowState.ModifiedCurrent))
sda.Update(dsStudentCourse.Tables(2).Select("", "",
DataViewRowState.ModifiedCurrent))

however having no errors during code execution when i call this code
but still after i close the form and open again i can't find the
changes i made after i save the data w/ the code above.

and yes this is a parent-child form

Hello jay

'in the example code below it is assumed that each data

'adapter (MyAD1, MyAD2, MyAd3) have commands for update

'delete and inserts for the respective tables they are

'managing.

MyAD1.Update(MyDS.Tables(0))

MyAD2.Update(MyDS.Tables(1))

MyAD3.Update(MyDS.Tables(2))

'This is a very simplistic code example. If there

'are parent/child relationships involed here then you

'have to break up the process lets say the tables

'are 1 parent to two that is parent to 3. If you have

'updates, inserts, and deletes happening in all of

'the datase's tables then yoyu have to submit things

'in order.

'submit all the inserts first starting from the

'top table on down

MyAD1.Update(MyDS.Tables(0).Select("", "", DataViewRowState.Added))

MyAD1.Update(MyDS.Tables(1).Select("", "", DataViewRowState.Added))

MyAD1.Update(MyDS.Tables(2).Select("", "", DataViewRowState.Added))

'now submit the updated rows

MyAD1.Update(MyDS.Tables(0).Select("", "",
DataViewRowState.ModifiedCurrent))

MyAD1.Update(MyDS.Tables(1).Select("", "",
DataViewRowState.ModifiedCurrent))

MyAD1.Update(MyDS.Tables(2).Select("", "",
DataViewRowState.ModifiedCurrent))

'finally submit the deletions from the bottom up

MyAD3.Update(MyDS.Tables(2).Select("", "", DataViewRowState.Deleted))

MyAD2.Update(MyDS.Tables(1).Select("", "", DataViewRowState.Deleted))

MyAD1.Update(MyDS.Tables(0).Select("", "", DataViewRowState.Deleted))

'the above will work in most situations. This code is untested.



jaYPee said:
I have search a lot of thread in google newsgroup and read a lot of
articles but still i don't know how to update the dataset that has 3
tables.

my 3 tables looks like the 3 tables from northwind database that has
an employees, orders, and order details.

the following are the 3 tables in my sql database
students
schyrsem
SchYrSemCourse

here's some variables i have

Private dsStudentCourse As DataSet
Private dtStudents As DataTable
Private dtSchYrSem As DataTable
Private dtSchYrSemCourse As DataTable
Private dvSchYrSem As DataView
Private dvSchYrSemCourse As DataView
Private sda As SqlDataAdapter

here's some of the code to fill the dataset

Dim scnnNW As New SqlConnection(strConn)

Dim strSQL As String = _
"SELECT IDNo, LastName, FirstName " & _
"FROM Students"

Dim scmd As New SqlCommand(strSQL, scnnNW)

sda = New SqlDataAdapter(scmd)

Dim scb As New SqlCommandBuilder(sda)

scb.GetUpdateCommand()

dsStudentCourse = New DataSet()
sda.Fill(dsStudentCourse, "Students")

scmd.CommandText = _
"SELECT SchYrSemID, IDNo, SchYr, Sem FROM
SchYrSem"

sda.Fill(dsStudentCourse, "SchYrSem")

scmd.CommandText = _
"SELECT SchYrSemCourseID, SchYrSemID,
Course.CourseID, CourseTitle, CourseDesc, Unit, [Hours/Week] FROM
Course INNER JOIN SchYrSemCourse ON Course.CourseID =
SchYrSemCourse.CourseID"

sda.Fill(dsStudentCourse, "SchYrSemCourse")

dtStudents = dsStudentCourse.Tables(0)
dtSchYrSem = dsStudentCourse.Tables(1)
dtSchYrSemCourse = dsStudentCourse.Tables(2)

dvSchYrSem = dtSchYrSem.DefaultView
dvSchYrSemCourse = dtSchYrSemCourse.DefaultView

i have also search the msdn for updating a multiple table but all i
can see is on how to save a single table only.

i would be very if someone can enlighten me on how to save a dataset
that has a multiple table in it.
Click to expand...
Click to expand...
Click to expand...
 
Hi
may be the grid still holds the current row in the proposed DataRow version. Check the grid if there are pending changes, and end
the current edit if so. This will make the proposed DataRow version current, and thus can be dataadapter.updated (propoed row
version is ignored in update):

protected void update()
{
it ( grid.EndEdit( null, grid.CurrentRowIndex, false )
{
((CurrencyManager) grid.BindingContext[ grid.DataSource, grid.DataMember ]).EndCurrentEdit();
}
dataAdapter.Update( dataSet.DataTable );
}

HTH, ulrich.

jaYPee said:
Hi,

Thank u very much for the reply.

i tried your source code just the update only to simplify my problem

here's the source code modified from what you have told me to do.

sda.Update(dsStudentCourse.Tables(0).Select("", "",
DataViewRowState.ModifiedCurrent))
sda.Update(dsStudentCourse.Tables(1).Select("", "",
DataViewRowState.ModifiedCurrent))
sda.Update(dsStudentCourse.Tables(2).Select("", "",
DataViewRowState.ModifiedCurrent))

however having no errors during code execution when i call this code
but still after i close the form and open again i can't find the
changes i made after i save the data w/ the code above.

and yes this is a parent-child form

Hello jay

'in the example code below it is assumed that each data

'adapter (MyAD1, MyAD2, MyAd3) have commands for update

'delete and inserts for the respective tables they are

'managing.

MyAD1.Update(MyDS.Tables(0))

MyAD2.Update(MyDS.Tables(1))

MyAD3.Update(MyDS.Tables(2))

'This is a very simplistic code example. If there

'are parent/child relationships involed here then you

'have to break up the process lets say the tables

'are 1 parent to two that is parent to 3. If you have

'updates, inserts, and deletes happening in all of

'the datase's tables then yoyu have to submit things

'in order.

'submit all the inserts first starting from the

'top table on down

MyAD1.Update(MyDS.Tables(0).Select("", "", DataViewRowState.Added))

MyAD1.Update(MyDS.Tables(1).Select("", "", DataViewRowState.Added))

MyAD1.Update(MyDS.Tables(2).Select("", "", DataViewRowState.Added))

'now submit the updated rows

MyAD1.Update(MyDS.Tables(0).Select("", "",
DataViewRowState.ModifiedCurrent))

MyAD1.Update(MyDS.Tables(1).Select("", "",
DataViewRowState.ModifiedCurrent))

MyAD1.Update(MyDS.Tables(2).Select("", "",
DataViewRowState.ModifiedCurrent))

'finally submit the deletions from the bottom up

MyAD3.Update(MyDS.Tables(2).Select("", "", DataViewRowState.Deleted))

MyAD2.Update(MyDS.Tables(1).Select("", "", DataViewRowState.Deleted))

MyAD1.Update(MyDS.Tables(0).Select("", "", DataViewRowState.Deleted))

'the above will work in most situations. This code is untested.



jaYPee said:
I have search a lot of thread in google newsgroup and read a lot of
articles but still i don't know how to update the dataset that has 3
tables.

my 3 tables looks like the 3 tables from northwind database that has
an employees, orders, and order details.

the following are the 3 tables in my sql database
students
schyrsem
SchYrSemCourse

here's some variables i have

Private dsStudentCourse As DataSet
Private dtStudents As DataTable
Private dtSchYrSem As DataTable
Private dtSchYrSemCourse As DataTable
Private dvSchYrSem As DataView
Private dvSchYrSemCourse As DataView
Private sda As SqlDataAdapter

here's some of the code to fill the dataset

Dim scnnNW As New SqlConnection(strConn)

Dim strSQL As String = _
"SELECT IDNo, LastName, FirstName " & _
"FROM Students"

Dim scmd As New SqlCommand(strSQL, scnnNW)

sda = New SqlDataAdapter(scmd)

Dim scb As New SqlCommandBuilder(sda)

scb.GetUpdateCommand()

dsStudentCourse = New DataSet()
sda.Fill(dsStudentCourse, "Students")

scmd.CommandText = _
"SELECT SchYrSemID, IDNo, SchYr, Sem FROM
SchYrSem"

sda.Fill(dsStudentCourse, "SchYrSem")

scmd.CommandText = _
"SELECT SchYrSemCourseID, SchYrSemID,
Course.CourseID, CourseTitle, CourseDesc, Unit, [Hours/Week] FROM
Course INNER JOIN SchYrSemCourse ON Course.CourseID =
SchYrSemCourse.CourseID"

sda.Fill(dsStudentCourse, "SchYrSemCourse")

dtStudents = dsStudentCourse.Tables(0)
dtSchYrSem = dsStudentCourse.Tables(1)
dtSchYrSemCourse = dsStudentCourse.Tables(2)

dvSchYrSem = dtSchYrSem.DefaultView
dvSchYrSemCourse = dtSchYrSemCourse.DefaultView

i have also search the msdn for updating a multiple table but all i
can see is on how to save a single table only.

i would be very if someone can enlighten me on how to save a dataset
that has a multiple table in it.
Click to expand...
Click to expand...
Click to expand...
 
may be the grid still holds the current row in the proposed DataRow
version. Check the grid if there are pending changes, and end
the current edit if so. This will make the proposed DataRow version
Click to expand...
current, and thus can be dataadapter.updated (propoed row
version is ignored in update):
Click to expand...


Is this the correct vb.net translation of Ulrich's code?

Protected Sub update()
Private Function grid.EndEdit(ByValNothing, ByValgrid.CurrentRowIndex,
ByValFalse) As it(
(CType(grid.BindingContext( grid.DataSource, grid.DataMember ),
CurrencyManager)).EndCurrentEdit()
End Function
dataAdapter.Update(dataSet.DataTable)
End Sub
 
Sorry, typing errors:

private sub update()
{
if ( grid.EndEdit( nothing, grid.CurrentRowIndex, false )
{

CType( grid.BindingContext[ grid.DataSource, grid.DataMember ],
CurrencyManager).EndCurrentEdit();
}
dataAdapter.Update( dataSet.DataTable );
}

I convert the BindingManagerBase, returned from BindingContext[], to a CurrencyManager, and then call his EndCurrentEdit() method.

But forget the example. I have found out, that grid.EndEdit() returns if the current cell is beeing edited ONLY. That is when the
pen icon shows up. If you move to a different cell in the same row, the method returns false....

This is especially crucial in the grid's insert row: The grid knows, whether some value has been changed through the GUI, but does
not uncover it's knowledge. So as soon as you have moved from a changed cell to another in the same (grid insert) row, I cannot
decide whether to EndEdit() or not....

There might be a solution: As soon as I change a cell in the grid's insert row, the grid creates a new one below. I'm checking it
out.

ulrich
 
Hi,
it's dirty, very dirty, but I cannot find another simple solution:

private void Update()
{
System.Windows.Forms.Control ac = this.ActiveControl;
SendKeys.SendWait( "^{TAB}" );
this.oleDbDataAdapter.Update( this.datasetCustomers.Customers);
ac.SelectNextControl( ac, false, true, false, true );
}

HTH, ulrich
 
Sorry, forgot some context:

public class Win32
{
[System.Runtime.InteropServices.DllImport("user32.dll")]
public static extern bool SendMessage( IntPtr hWnd, Int32 msg, Int32 wParam, Int32 lParam );
public const int WM_SETFOCUS = 0x0007;
}

This declaration gives access to the windows 32 api SendMessage function.

private void Update()
{
Win32.SendMessage( this.button1.Handle, Win32.WM_SETFOCUS, 0, 0 );
this.oleDbDataAdapter.Update( this.datasetCustomers.Customers);
Win32.SendMessage( this.grid.Handle, Win32.WM_SETFOCUS, 0, 0 );
}

This is a slightly better solution than the previous, which used SendKeys.SendWait() to forward the focus to a different control.
This version uses the W32 API SendMessage function, which returns when the target window (the button) has processed the message.
This is essential, a simple "this.button.Focus()" does not work. This makes me believe that the grid's update process is bound to
some internal messaging and message processing following a lost focus event, which cannot take place (or is delayed beyond
Update()'s exit) if you use a method call to set the focus elsewhere.

The focus is placed back on the grid in the last line, however, the current cell looses selection and also does not show the caret,
although the proper cell is active and has the focus...


HTH, ulrich.
 
Hi all,

I TRIED hard to CATCH a solution, and FINALLY I got it:
As I've mentioned previously, if we are in the insert row and have changed one or more cells,
the grid starts editing the current row and adds a new insert row below the current one.

So the problem can be boiled down to the following question:
- If there is a row below, we can safely call DataRowView.EndEdit()
(If there were none, calling EndEdit() would produce empty rows in the database.)

How can we determine if there is a row below the current? It's easy (if you know it): Get the
cell bounds of one of the cells in that row. If there is no such row, we will get an exception
from the grid:

protected void Update() // final version!!!
{
try
{
grid.GetCellBounds( grid.CurrentRowIndex + 1, 0 );
this.grid.EndEdit( null, this.grid.CurrentRowIndex, false );
((DataRowView) this.cm.Current).EndEdit();
}
catch
{
// nothing to do...
}
finally
{
this.oleDbDataAdapter.Update( this.datasetCustomers.Customers);
}
}

Please forgive me the previous attempts - I didn't know better...
ulrich
 
Back
Top