473,699 Members | 2,027 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

how to update dataset w/ multiple table?

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 dtSchYrSemCours e As DataTable
Private dvSchYrSem As DataView
Private dvSchYrSemCours e As DataView
Private sda As SqlDataAdapter

here's some of the code to fill the dataset

Dim scnnNW As New SqlConnection(s trConn)

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

Dim scmd As New SqlCommand(strS QL, scnnNW)

sda = New SqlDataAdapter( scmd)

Dim scb As New SqlCommandBuild er(sda)

scb.GetUpdateCo mmand()

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

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

sda.Fill(dsStud entCourse, "SchYrSem")

scmd.CommandTex t = _
"SELECT SchYrSemCourseI D, SchYrSemID,
Course.CourseID , CourseTitle, CourseDesc, Unit, [Hours/Week] FROM
Course INNER JOIN SchYrSemCourse ON Course.CourseID =
SchYrSemCourse. CourseID"

sda.Fill(dsStud entCourse, "SchYrSemCourse ")

dtStudents = dsStudentCourse .Tables(0)
dtSchYrSem = dsStudentCourse .Tables(1)
dtSchYrSemCours e = dsStudentCourse .Tables(2)

dvSchYrSem = dtSchYrSem.Defa ultView
dvSchYrSemCours e = dtSchYrSemCours e.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.
Nov 20 '05 #1
9 12980
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(My DS.Tables(0))

MyAD2.Update(My DS.Tables(1))

MyAD3.Update(My DS.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(My DS.Tables(0).Se lect("", "", DataViewRowStat e.Added))

MyAD1.Update(My DS.Tables(1).Se lect("", "", DataViewRowStat e.Added))

MyAD1.Update(My DS.Tables(2).Se lect("", "", DataViewRowStat e.Added))

'now submit the updated rows

MyAD1.Update(My DS.Tables(0).Se lect("", "",
DataViewRowStat e.ModifiedCurre nt))

MyAD1.Update(My DS.Tables(1).Se lect("", "",
DataViewRowStat e.ModifiedCurre nt))

MyAD1.Update(My DS.Tables(2).Se lect("", "",
DataViewRowStat e.ModifiedCurre nt))

'finally submit the deletions from the bottom up

MyAD3.Update(My DS.Tables(2).Se lect("", "", DataViewRowStat e.Deleted))

MyAD2.Update(My DS.Tables(1).Se lect("", "", DataViewRowStat e.Deleted))

MyAD1.Update(My DS.Tables(0).Se lect("", "", DataViewRowStat e.Deleted))

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

"jaYPee" <hi******@yahoo .com> wrote in message
news:jc******** *************** *********@4ax.c om...
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 dtSchYrSemCours e As DataTable
Private dvSchYrSem As DataView
Private dvSchYrSemCours e As DataView
Private sda As SqlDataAdapter

here's some of the code to fill the dataset

Dim scnnNW As New SqlConnection(s trConn)

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

Dim scmd As New SqlCommand(strS QL, scnnNW)

sda = New SqlDataAdapter( scmd)

Dim scb As New SqlCommandBuild er(sda)

scb.GetUpdateCo mmand()

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

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

sda.Fill(dsStud entCourse, "SchYrSem")

scmd.CommandTex t = _
"SELECT SchYrSemCourseI D, SchYrSemID,
Course.CourseID , CourseTitle, CourseDesc, Unit, [Hours/Week] FROM
Course INNER JOIN SchYrSemCourse ON Course.CourseID =
SchYrSemCourse. CourseID"

sda.Fill(dsStud entCourse, "SchYrSemCourse ")

dtStudents = dsStudentCourse .Tables(0)
dtSchYrSem = dsStudentCourse .Tables(1)
dtSchYrSemCours e = dsStudentCourse .Tables(2)

dvSchYrSem = dtSchYrSem.Defa ultView
dvSchYrSemCours e = dtSchYrSemCours e.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.

Nov 20 '05 #2
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(dsSt udentCourse.Tab les(0).Select(" ", "",
DataViewRowStat e.ModifiedCurre nt))
sda.Update(dsSt udentCourse.Tab les(1).Select(" ", "",
DataViewRowStat e.ModifiedCurre nt))
sda.Update(dsSt udentCourse.Tab les(2).Select(" ", "",
DataViewRowStat e.ModifiedCurre nt))

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

On Mon, 5 Jan 2004 00:05:05 -0700, "IbrahimMal luf"
<Ib*****@malluf .com> wrote:
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(M yDS.Tables(0))

MyAD2.Update(M yDS.Tables(1))

MyAD3.Update(M yDS.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(M yDS.Tables(0).S elect("", "", DataViewRowStat e.Added))

MyAD1.Update(M yDS.Tables(1).S elect("", "", DataViewRowStat e.Added))

MyAD1.Update(M yDS.Tables(2).S elect("", "", DataViewRowStat e.Added))

'now submit the updated rows

MyAD1.Update(M yDS.Tables(0).S elect("", "",
DataViewRowSta te.ModifiedCurr ent))

MyAD1.Update(M yDS.Tables(1).S elect("", "",
DataViewRowSta te.ModifiedCurr ent))

MyAD1.Update(M yDS.Tables(2).S elect("", "",
DataViewRowSta te.ModifiedCurr ent))

'finally submit the deletions from the bottom up

MyAD3.Update(M yDS.Tables(2).S elect("", "", DataViewRowStat e.Deleted))

MyAD2.Update(M yDS.Tables(1).S elect("", "", DataViewRowStat e.Deleted))

MyAD1.Update(M yDS.Tables(0).S elect("", "", DataViewRowStat e.Deleted))

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

"jaYPee" <hi******@yahoo .com> wrote in message
news:jc******* *************** **********@4ax. com...
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 dtSchYrSemCours e As DataTable
Private dvSchYrSem As DataView
Private dvSchYrSemCours e As DataView
Private sda As SqlDataAdapter

here's some of the code to fill the dataset

Dim scnnNW As New SqlConnection(s trConn)

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

Dim scmd As New SqlCommand(strS QL, scnnNW)

sda = New SqlDataAdapter( scmd)

Dim scb As New SqlCommandBuild er(sda)

scb.GetUpdateCo mmand()

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

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

sda.Fill(dsStud entCourse, "SchYrSem")

scmd.CommandTex t = _
"SELECT SchYrSemCourseI D, SchYrSemID,
Course.CourseID , CourseTitle, CourseDesc, Unit, [Hours/Week] FROM
Course INNER JOIN SchYrSemCourse ON Course.CourseID =
SchYrSemCourse. CourseID"

sda.Fill(dsStud entCourse, "SchYrSemCourse ")

dtStudents = dsStudentCourse .Tables(0)
dtSchYrSem = dsStudentCourse .Tables(1)
dtSchYrSemCours e = dsStudentCourse .Tables(2)

dvSchYrSem = dtSchYrSem.Defa ultView
dvSchYrSemCours e = dtSchYrSemCours e.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.


Nov 20 '05 #3
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" <hi******@yahoo .com> wrote in message
news:36******** *************** *********@4ax.c om...
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(dsSt udentCourse.Tab les(0).Select(" ", "",
DataViewRowStat e.ModifiedCurre nt))
sda.Update(dsSt udentCourse.Tab les(1).Select(" ", "",
DataViewRowStat e.ModifiedCurre nt))
sda.Update(dsSt udentCourse.Tab les(2).Select(" ", "",
DataViewRowStat e.ModifiedCurre nt))

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

On Mon, 5 Jan 2004 00:05:05 -0700, "IbrahimMal luf"
<Ib*****@malluf .com> wrote:
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(M yDS.Tables(0))

MyAD2.Update(M yDS.Tables(1))

MyAD3.Update(M yDS.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(M yDS.Tables(0).S elect("", "", DataViewRowStat e.Added))

MyAD1.Update(M yDS.Tables(1).S elect("", "", DataViewRowStat e.Added))

MyAD1.Update(M yDS.Tables(2).S elect("", "", DataViewRowStat e.Added))

'now submit the updated rows

MyAD1.Update(M yDS.Tables(0).S elect("", "",
DataViewRowSta te.ModifiedCurr ent))

MyAD1.Update(M yDS.Tables(1).S elect("", "",
DataViewRowSta te.ModifiedCurr ent))

MyAD1.Update(M yDS.Tables(2).S elect("", "",
DataViewRowSta te.ModifiedCurr ent))

'finally submit the deletions from the bottom up

MyAD3.Update(M yDS.Tables(2).S elect("", "", DataViewRowStat e.Deleted))

MyAD2.Update(M yDS.Tables(1).S elect("", "", DataViewRowStat e.Deleted))

MyAD1.Update(M yDS.Tables(0).S elect("", "", DataViewRowStat e.Deleted))

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

"jaYPee" <hi******@yahoo .com> wrote in message
news:jc******* *************** **********@4ax. com...
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 dtSchYrSemCours e As DataTable
Private dvSchYrSem As DataView
Private dvSchYrSemCours e As DataView
Private sda As SqlDataAdapter

here's some of the code to fill the dataset

Dim scnnNW As New SqlConnection(s trConn)

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

Dim scmd As New SqlCommand(strS QL, scnnNW)

sda = New SqlDataAdapter( scmd)

Dim scb As New SqlCommandBuild er(sda)

scb.GetUpdateCo mmand()

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

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

sda.Fill(dsStud entCourse, "SchYrSem")

scmd.CommandTex t = _
"SELECT SchYrSemCourseI D, SchYrSemID,
Course.CourseID , CourseTitle, CourseDesc, Unit, [Hours/Week] FROM
Course INNER JOIN SchYrSemCourse ON Course.CourseID =
SchYrSemCourse. CourseID"

sda.Fill(dsStud entCourse, "SchYrSemCourse ")

dtStudents = dsStudentCourse .Tables(0)
dtSchYrSem = dsStudentCourse .Tables(1)
dtSchYrSemCours e = dsStudentCourse .Tables(2)

dvSchYrSem = dtSchYrSem.Defa ultView
dvSchYrSemCours e = dtSchYrSemCours e.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.

Nov 20 '05 #4
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.upd ated (propoed row
version is ignored in update):

protected void update()
{
it ( grid.EndEdit( null, grid.CurrentRow Index, false )
{
((CurrencyManag er) grid.BindingCon text[ grid.DataSource , grid.DataMember ]).EndCurrentEdi t();
}
dataAdapter.Upd ate( dataSet.DataTab le );
}

HTH, ulrich.

"jaYPee" <hi******@yahoo .com> schrieb im Newsbeitrag news:36******** *************** *********@4ax.c om...
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(dsSt udentCourse.Tab les(0).Select(" ", "",
DataViewRowStat e.ModifiedCurre nt))
sda.Update(dsSt udentCourse.Tab les(1).Select(" ", "",
DataViewRowStat e.ModifiedCurre nt))
sda.Update(dsSt udentCourse.Tab les(2).Select(" ", "",
DataViewRowStat e.ModifiedCurre nt))

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

On Mon, 5 Jan 2004 00:05:05 -0700, "IbrahimMal luf"
<Ib*****@malluf .com> wrote:
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(M yDS.Tables(0))

MyAD2.Update(M yDS.Tables(1))

MyAD3.Update(M yDS.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(M yDS.Tables(0).S elect("", "", DataViewRowStat e.Added))

MyAD1.Update(M yDS.Tables(1).S elect("", "", DataViewRowStat e.Added))

MyAD1.Update(M yDS.Tables(2).S elect("", "", DataViewRowStat e.Added))

'now submit the updated rows

MyAD1.Update(M yDS.Tables(0).S elect("", "",
DataViewRowSta te.ModifiedCurr ent))

MyAD1.Update(M yDS.Tables(1).S elect("", "",
DataViewRowSta te.ModifiedCurr ent))

MyAD1.Update(M yDS.Tables(2).S elect("", "",
DataViewRowSta te.ModifiedCurr ent))

'finally submit the deletions from the bottom up

MyAD3.Update(M yDS.Tables(2).S elect("", "", DataViewRowStat e.Deleted))

MyAD2.Update(M yDS.Tables(1).S elect("", "", DataViewRowStat e.Deleted))

MyAD1.Update(M yDS.Tables(0).S elect("", "", DataViewRowStat e.Deleted))

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

"jaYPee" <hi******@yahoo .com> wrote in message
news:jc******* *************** **********@4ax. com...
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 dtSchYrSemCours e As DataTable
Private dvSchYrSem As DataView
Private dvSchYrSemCours e As DataView
Private sda As SqlDataAdapter

here's some of the code to fill the dataset

Dim scnnNW As New SqlConnection(s trConn)

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

Dim scmd As New SqlCommand(strS QL, scnnNW)

sda = New SqlDataAdapter( scmd)

Dim scb As New SqlCommandBuild er(sda)

scb.GetUpdateCo mmand()

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

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

sda.Fill(dsStud entCourse, "SchYrSem")

scmd.CommandTex t = _
"SELECT SchYrSemCourseI D, SchYrSemID,
Course.CourseID , CourseTitle, CourseDesc, Unit, [Hours/Week] FROM
Course INNER JOIN SchYrSemCourse ON Course.CourseID =
SchYrSemCourse. CourseID"

sda.Fill(dsStud entCourse, "SchYrSemCourse ")

dtStudents = dsStudentCourse .Tables(0)
dtSchYrSem = dsStudentCourse .Tables(1)
dtSchYrSemCours e = dsStudentCourse .Tables(2)

dvSchYrSem = dtSchYrSem.Defa ultView
dvSchYrSemCours e = dtSchYrSemCours e.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.

Nov 20 '05 #5
> 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.upd ated (propoed row version is ignored in update):

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

Protected Sub update()
Private Function grid.EndEdit(By ValNothing, ByValgrid.Curre ntRowIndex,
ByValFalse) As it(
(CType(grid.Bin dingContext( grid.DataSource , grid.DataMember ),
CurrencyManager )).EndCurrentEd it()
End Function
dataAdapter.Upd ate(dataSet.Dat aTable)
End Sub
Nov 20 '05 #6
Sorry, typing errors:

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

CType( grid.BindingCon text[ grid.DataSource , grid.DataMember ],
CurrencyManager ).EndCurrentEdi t();
}
dataAdapter.Upd ate( dataSet.DataTab le );
}

I convert the BindingManagerB ase, 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


"scorpion53 061" <Its the end of the world as we know it@here.com> schrieb im Newsbeitrag
news:#l******** ******@tk2msftn gp13.phx.gbl...
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.upd ated (propoed row
version is ignored in update):

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

Protected Sub update()
Private Function grid.EndEdit(By ValNothing, ByValgrid.Curre ntRowIndex,
ByValFalse) As it(
(CType(grid.Bin dingContext( grid.DataSource , grid.DataMember ),
CurrencyManager )).EndCurrentEd it()
End Function
dataAdapter.Upd ate(dataSet.Dat aTable)
End Sub

Nov 20 '05 #7
Hi,
it's dirty, very dirty, but I cannot find another simple solution:

private void Update()
{
System.Windows. Forms.Control ac = this.ActiveCont rol;
SendKeys.SendWa it( "^{TAB}" );
this.oleDbDataA dapter.Update( this.datasetCus tomers.Customer s);
ac.SelectNextCo ntrol( ac, false, true, false, true );
}

HTH, ulrich
"scorpion53 061" <Its the end of the world as we know it@here.com> schrieb im Newsbeitrag
news:#l******** ******@tk2msftn gp13.phx.gbl...
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.upd ated (propoed row
version is ignored in update):

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

Protected Sub update()
Private Function grid.EndEdit(By ValNothing, ByValgrid.Curre ntRowIndex,
ByValFalse) As it(
(CType(grid.Bin dingContext( grid.DataSource , grid.DataMember ),
CurrencyManager )).EndCurrentEd it()
End Function
dataAdapter.Upd ate(dataSet.Dat aTable)
End Sub

Nov 20 '05 #8
Sorry, forgot some context:

public class Win32
{
[System.Runtime. InteropServices .DllImport("use r32.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.SendMessa ge( this.button1.Ha ndle, Win32.WM_SETFOC US, 0, 0 );
this.oleDbDataA dapter.Update( this.datasetCus tomers.Customer s);
Win32.SendMessa ge( this.grid.Handl e, Win32.WM_SETFOC US, 0, 0 );
}

This is a slightly better solution than the previous, which used SendKeys.SendWa it() 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.Fo cus()" 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.
"scorpion53 061" <Its the end of the world as we know it@here.com> schrieb im Newsbeitrag
news:#l******** ******@tk2msftn gp13.phx.gbl...
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.upd ated (propoed row
version is ignored in update):

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

Protected Sub update()
Private Function grid.EndEdit(By ValNothing, ByValgrid.Curre ntRowIndex,
ByValFalse) As it(
(CType(grid.Bin dingContext( grid.DataSource , grid.DataMember ),
CurrencyManager )).EndCurrentEd it()
End Function
dataAdapter.Upd ate(dataSet.Dat aTable)
End Sub

Nov 20 '05 #9
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.End Edit()
(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.GetCellBou nds( grid.CurrentRow Index + 1, 0 );
this.grid.EndEd it( null, this.grid.Curre ntRowIndex, false );
((DataRowView) this.cm.Current ).EndEdit();
}
catch
{
// nothing to do...
}
finally
{
this.oleDbDataA dapter.Update( this.datasetCus tomers.Customer s);
}
}

Please forgive me the previous attempts - I didn't know better...
ulrich
"scorpion53 061" <Its the end of the world as we know it@here.com> schrieb im Newsbeitrag
news:#l******** ******@tk2msftn gp13.phx.gbl...
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.upd ated (propoed row
version is ignored in update):

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

Protected Sub update()
Private Function grid.EndEdit(By ValNothing, ByValgrid.Curre ntRowIndex,
ByValFalse) As it(
(CType(grid.Bin dingContext( grid.DataSource , grid.DataMember ),
CurrencyManager )).EndCurrentEd it()
End Function
dataAdapter.Upd ate(dataSet.Dat aTable)
End Sub

Nov 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
2435
by: PAUL EDWARDS | last post by:
I have a windows form that is bound to a datatable. In VB6 I could just update the field contents and it would be updated in the database, however if I update the text property of the control from code it is 50% chance that the update will make it back to the dataset. If I update the dataset instead of the form, it does not show on the form. Is there a method that should be used?
1
2993
by: Wing | last post by:
Hi all, I have created 2 tables in sql database and join these 2 tables before assign the result to the dataset, and display the result in datagrid. Everything is fine up to this point. The problem come up when I want to delete one of rows in datagrid and update the change to the corresponding table. The error msg show up "Dynamic SQL generation is not supported against multiple base tables."
3
9757
by: Justin | last post by:
I have created a dataset with two tables and an insert command, I need to be able to retreive the Key Identity after inserting into table "A" for use in table "B". Should I use ExecuteScalar() or is there a better solution? Thanks, Justin.
1
1565
by: Foef | last post by:
When I have a stored procedure, with multiple tables in MS Access, in a dataset and I change it in my DataGrid, I get the next message when I want to update my DataSet: InvalidOperationException: "Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information." I tried to use an update query but when I fill my DataTable using myDataAdapter.Fill(myDataSet, myTable)
5
3537
by: PAUL | last post by:
Hello, I have 2 tables with a relationship set up in the dataset with vb ..net. I add a new record to the parent table then edit an existing child record to have the new parent ID. However when I do the update the changed parentid in the child table fails to change. No error is given its just that the change is not written to the Database. When I step through the records for the child table the one I would expect to be changed has a row...
8
2695
by: Zorpiedoman | last post by:
I keep getting a concurrency exception the second time I make a change and attempt to update a dataadapter. It appears this is by design, so there must be something I can do to avoid it. Example: I have a dataadapter that contains one table with one row. I change the value of the 'FisrtName' column in that row from Jack to John. I call ..update on the dataadapter it goes through fine. Now I change that same column in that same row...
1
1789
by: AndiSHFR | last post by:
Hi. How do i "bulk update" data rows on a dataset table from another one DataTable? i have a dataset ds1 with a table "Groups" and a table "newgroupdata" and i want to update the records in ds1.Tables from the data in ds1.Tables. Additional i want to insert groups wich are not already in "Groups" too.
1
2081
by: andrewcw | last post by:
I have just 1 table that I am updating, the SQL I use to generate the DataTable is complex using multiple tables, however when I view the fieldnames of the DataTable from the DataSet - everything matches: What dont I understand here: ( The error I get is : Dynamic SQL generation is not supported against multiple base tables ). But My DataSet is 1 Table, and my target is 1 table, Why the error ?? Thanks : try {
5
4072
by: Bogdan | last post by:
Hi, I have a stored procedure that uses JOINs to return columns from multiple tables. I also have another stored proc that that takes a series of params and updates multiple tables. I used the framework to auto-generate a table adapter specifying both stored procs as Get/Fill and Update. The problem is that columns from the JOINed table seem to marked as 'read-only' so trying to update a row results in an exception. BTW, by default a...
0
8697
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8621
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
8929
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8891
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7759
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5878
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4380
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4634
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2357
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.