Issues with datagridview last record updating sql database

awade@gulfstatesmanufacturers.com

Hi Rich,

Oh I like that... as I play with your sample app, and try and apply
the practices to my own... I end up with all kinds of questions.
Luckily, I end up answering some of them myself just by re-reading and
following your code.

All of this to say... i'm sure i'll have some more questions to
follow... but thank you for taking your time to help me. I hope you,
and your family, have a safe and happy holiday season.

OK... now let me go look this thing over some more... and I'll get
back to you after a bit.


On Dec 14, 10:34 am, Rich <> wrote:
> Hi Andrew,
>
> Think of the datagridview as a sort of subform - like an MS Access subform -
> except with 100+ times more capablities.
>
> In the main form's load event -- after (or before) you set the datasource
> for your datagridview (dgrv1) -- add this line of code
>
> dgrv1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
>
> This will select/highlight the entire row of the datagridview. I added a
> button to my sample app which scrolls through the memory table tmpX using the
> CurrencyManager object (curMgr). I added a couple of textboxes to my form
> (txt0, txt1) and will populate these as I scroll through the dataTable tmpX
> -- which also scrolls through the datagridview.
>
> BTW, it is a good practice to use Panel controls to separate textboxes from
> datagridviews. Add a Panel control to the top of your form and set its
> ordering to SendBack (if you have to place the panel over already existing
> textboxs). Dock the Panel to the top of the form and then resize it as
> needed to enclose your textboxes and buttons. Underneath the Panel add your
> datagridview and set its dock property to Fill. The dock property for all
> controls is usually located near the bottom of the Property sheet (VS2005).
> What this does is to resize the datagridview as you resize your form. And
> there is an order in which you have to place the controls. First add a Panel
> (or panels). Then add the status strip (I always add a status strip because
> you can display all kinds of information). The status strip automatically
> docks to the bottom of the form. Then add the datagridview last. If you
> load the datagridview before the statusstrip -- a lot of times it will cover
> the statusstrip. These are the primary controls. You can add textboxes and
> buttons,... to the panel(s) afterwards -- they are secondary controls.
> Anyway, here is what button1 in my sample app does:
>
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> curMgr.Position += 1
> tssL2.Text = (curMgr.Position + 1).ToString
> dgrv1.Rows(curMgr.Position).Cells(0).Selected = True
>
> Dim SelectedRows As DataGridViewSelectedRowCollection
> SelectedRows = dgrv1.SelectedRows
> txt0.Text = SelectedRows(0).Cells("RecordID").Value.ToString
> txt1.Text = SelectedRows(0).Cells("SubscrID").Value.ToString
>
> End Sub
>
> Button1 will select one row at a time here
>
> dgrv1.Rows(curMgr.Position).Cells(0).Selected = True
>
> then you set SelectedRows to this row. SelectedRows is a collection object.
> It will only contain one row in this sample because I only selected one row.
> Then you can set the contents of SelectedRows to your textboxes.
>
> Give this a try,
> Rich

Rich

The UpdateCommand basically transfers the edits on the memory table to the
server table. In my sample I edit one field and update it immediately. That
was just to demonstrate Editing/Updating. In reality, my users will edit
dozens of fields in a row and hundreds of rows before calling the
UpdateCommand. But the setup is a little bit different.

For the users I support, they will edit lots of stuff and then consciously
click a button that performs the updates. You can automatically perform an
update on the datagridview's RowLeave event. So you can edit several cells
on a row and call the updateCommand when you leave the row. You will notice
that the Datagridview controls has hundreds of events (and hundreds of
properties) very powerful control. You can custom color cells, fonts, change
fonts size per cell per row, ...

Anyway, if you want to update multiple fields just add a parameter to the
UpdateCommand for each field you want to edit. All the UpdateCommand does is
to transfer whatever fields were edited in the memory table tmpX to the
server table. --Important Note: if you edit cells in the datagridview but
do not pass on these edits to the memory table - the memory table has no
knowledge that any edits were performed. This is were drF() comes into the
picture:

Scenario: dgrv1 was just re-ordered on column EntryDate. The row order in
dgrv1 is now different than the row order of tmpX. You edited row 15 in
dgrv1. But row 15 is actually row 337 in tmpX. How does the app know which
row to update in tmpX?

dim drF() As DataRow = ds.Tables("tmpX").Select("SomeKeyField = "
someKeyvalue)

then Dim dr As DataRow = drF(0)
dr.BeginEdit
dr(0) = dgrv1.Rows(15).Cells(0).Value
dr(1) = dgrv1.Rows(15).Cells(1).Value
dr(7) = dgrv1.Rows(15).Cells(7).Value
dr(18) = dgrv1.Rows(15).Cells(18).Value
dr.EndEdit
da.Update(ds, "tmpX")

Here I am only editing 4 fields in the datagridview. You can actually
create a loop if you have a large table and set the values of each field to
the value of the cells in a given row in dgrv1 even if the cell has not been
edited - no difference - you are just passing in the current value of the
cells.

dr.BeginEdit
For Each dc As DataColumn in ds.Tables("tmpX").Columns
If dc.ColumnName <> "yourKeyField" Then
dr(dc.ColumnName) = dgrv1.Rows(15).Cells(dc.ColumnName)
End If
Next
dr.EndEdit
da.Update(ds, "tmpX")

Note: dr does not contain dataColums, it only contains values reference by
the datacolumns. So you have to base the columns loop on the actual memory
table
ds.Tables("tmpX") when using a For Each Next type of loop. You could also
use a For Next Loop, but you still have to base the column count on
ds.Tables("tmpX").Columns.Count

For i As Integer = 0 To ds.Tables("tmpX").Columns.Count - 1
....
Next



"" wrote:

> OK... more questions about your example code...
>
> the dataadapter, da, updatecommand that is in the form_load section...
> what does this do? Is just setting what parameters, aka fields, that
> I can use when I actually build my update string? Either fields i
> will update, or fields i will reference.
>
> Form_load
>
> strJobNo = "05" & txtJobNo.Text
> strJobFilter = "BOM_JOBNBR = '" & strJobNo & "' and BOM_PL_SEQ
> = '10'"
>
> conn = New SqlConnection
> conn.ConnectionString = "Data Source=GSMAPP\SQLEXPRESS;Initial
> Catalog=GSM;Persist Security Info=True;User ID=<id
> here>;Password=<pass here>"
> da = New SqlDataAdapter
> da.SelectCommand = New SqlCommand
> da.SelectCommand.Connection = conn
> ds = New DataSet
> ' These are the fields that I need to either display,
> reference, or update
> da.SelectCommand.CommandText = "Select BOM_MARK, BOM_COL,
> BOM_LENGTH, BOM_DESC, BOM_KEY, BOM_PL_QTY, BOM_COMPLETION_QTY,
> BOM_COMPLETION_DATE, BOM_COMPLETED From BOM Where " & strJobFilter
> da.Fill(ds, "tmpX")
> StructuralList.DataSource = ds.Tables("tmpX")
>
> curMgr = CType(Me.BindingContext(ds.Tables("tmpX")),
> CurrencyManager)
> tssl1.Text = (curMgr.Position + 1).ToString
> tssl2.Text = curMgr.Count.ToString
>
> ' So I currently have one reference field - BOM_KEY... and one
> field that I would update - BOM_COMPLETION_QTY? And if I wanted to
> add more fields to update, then I would add more
> UpdateCommand.Parameters.Add lines of code for each one.... right?
> da.UpdateCommand = New SqlCommand
> da.UpdateCommand.Connection = conn
> da.UpdateCommand.Parameters.Add("@BOM_COMPLETION_Q TY",
> SqlDbType.VarChar, 7, "BOM_COMPLETION_QTY")
> da.UpdateCommand.Parameters.Add("@BOM_KEY", SqlDbType.VarChar,
> 38, "BOM_KEY")
> da.UpdateCommand.CommandText = "Update BOM Set
> BOM_COMPLETION_QTY = @BOM_COMPLETION_QTY Where BOM_KEY = @BOM_KEY"
>
> End sub
>
> ** You're probably wondering why my Quantity field is a string... all
> of this data has to be in string format because this database table is
> being read from an VSE mainframe. :-)
>
> OK... The CellEndEdit is what actually updates the database, right?
> In our example, we're only dealing with updating one field in the
> datagrid (and our database)... i'm trying to wrap my brain around
> updating 3 different fields when one field changes (not a biggie if
> i'm understanding this correctly). When my Quantity gets updated... I
> can update my other fields (today's date and such) off of that one
> trigger... soooo... if i want my trigger to be a key_press.enter event
> off of a text box... i use the same code?
>
> I hope that some of that makes sense...
>
> Thanks,
>
> -andrew
>
>
>
>
> On Dec 14, 9:24 am, wrote:
> > Rich,
> >
> > Let me pick your brain for a bit... can I fill 3 text boxes and the
> > datagrid all from our memory table, tmpX? Where the text boxes show
> > the data of the same record that is current in the datagridview
> > object?
> >
> > -andrew
>
>

awade@gulfstatesmanufacturers.com

Hi Rich,

Wow... I'm so close to having the sample app do like i need my main
one. What I've done:

First, I added another field in the database called "Description",
varChar(50). In the form load i've changed the UpdateCommand and added
populating the text boxes as you had done earlier with your code:

da.UpdateCommand = New SqlCommand
da.UpdateCommand.Connection = conn
da.UpdateCommand.Parameters.Add("@RecordID", SqlDbType.Int, 4,
"RecordID")
da.UpdateCommand.Parameters.Add("@SubscrID",
SqlDbType.VarChar, 50, "SubscrID")
da.UpdateCommand.Parameters.Add("@Description",
SqlDbType.VarChar, 50, "Description")
da.UpdateCommand.CommandText = "Update tmp3 Set SubscrID =
@SubscrID, Description = @Description Where RecordID = @RecordID"

Dim SelectedRows As DataGridViewSelectedRowCollection
SelectedRows = dgrv1.SelectedRows
txt0.Text = SelectedRows(0).Cells("RecordID").Value.ToString
txt1.Text = SelectedRows(0).Cells("SubscrID").Value.ToString
txt2.Text =
SelectedRows(0).Cells("Description").Value.ToStrin g

I've also added a third text box (txt2) and added this code:

Private Sub txt2_KeyPress(ByVal sender As Object, ByVal e As
System.Windows.Forms.KeyEventArgs) Handles txt2.KeyUp
Dim SelectedRows As DataGridViewSelectedRowCollection

If e.KeyCode = Keys.Enter Then
SelectedRows = dgrv1.SelectedRows
SelectedRows(0).Cells("Description").Value = txt2.Text
'update tmpx
curMgr.Position += 1
tssL2.Text = (curMgr.Position).ToString
dgrv1.Rows(curMgr.Position).Cells(0).Selected = True
SelectedRows = dgrv1.SelectedRows
txt0.Text =
SelectedRows(0).Cells("RecordID").Value.ToString
txt1.Text =
SelectedRows(0).Cells("SubscrID").Value.ToString
txt2.Text =
SelectedRows(0).Cells("Description").Value.ToStrin g
Else
If e.KeyCode = Keys.Down Then
curMgr.Position += 1
tssL2.Text = (curMgr.Position).ToString
dgrv1.Rows(curMgr.Position).Cells(0).Selected = True
SelectedRows = dgrv1.SelectedRows
txt0.Text =
SelectedRows(0).Cells("RecordID").Value.ToString
txt1.Text =
SelectedRows(0).Cells("SubscrID").Value.ToString
txt2.Text =
SelectedRows(0).Cells("Description").Value.ToStrin g
Else
If e.KeyCode = Keys.Up Then
curMgr.Position -= 1
tssL2.Text = (curMgr.Position).ToString
dgrv1.Rows(curMgr.Position).Cells(0).Selected =
True
SelectedRows = dgrv1.SelectedRows
txt0.Text =
SelectedRows(0).Cells("RecordID").Value.ToString
txt1.Text =
SelectedRows(0).Cells("SubscrID").Value.ToString
txt2.Text =
SelectedRows(0).Cells("Description").Value.ToStrin g
End If
End If
End If
End Sub

Now, I've tried cleaning that up once using a case statement... but
then it wouldn't let me type anything into my text box. So, I'll
revisit that later. I set the tab order in my form to give txt2 a tab
stop of 0. Now i'm on txt2 when the form loads... my text boxes are
all populated with the current record in the datagrid... i can
navigate up or down with my up and down arrow keys... I can enter a
data for the description in txt2 and it updates the grid..... BUT...
now i'm having issues with the updating back to the database.

I noticed that on the CellEndEdit uses the index of the row... and
then the name of the column... If I wanted to update the database
after each field has been edited, couldn't I get my index and column
name to update tmpX? This is assuming that no reordering of my
datagrid has been done. I saw in your earlier post how you said I
should loop through and match my "KeyField"... and I agree... but do I
have to put the "KeyField", like our RecordID, in my datagrid. I'm
guessing the answer is yes, and then I hide it.

Its slowly sinking in... i'm going to work some more on the update of
tmpX from my Key_Press event.

Thanks again for you help...

-andrew

On Dec 14, 11:38 am, Rich <> wrote:
> The UpdateCommand basically transfers the edits on the memory table to the
> server table. In my sample I edit one field and update it immediately. That
> was just to demonstrate Editing/Updating. In reality, my users will edit
> dozens of fields in a row and hundreds of rows before calling the
> UpdateCommand. But the setup is a little bit different.
>
> For the users I support, they will edit lots of stuff and then consciously
> click a button that performs the updates. You can automatically perform an
> update on the datagridview's RowLeave event. So you can edit several cells
> on a row and call the updateCommand when you leave the row. You will notice
> that the Datagridview controls has hundreds of events (and hundreds of
> properties) very powerful control. You can custom color cells, fonts, change
> fonts size per cell per row, ...
>
> Anyway, if you want to update multiple fields just add a parameter to the
> UpdateCommand for each field you want to edit. All the UpdateCommand does is
> to transfer whatever fields were edited in the memory table tmpX to the
> server table. --Important Note: if you edit cells in the datagridview but
> do not pass on these edits to the memory table - the memory table has no
> knowledge that any edits were performed. This is were drF() comes into the
> picture:
>
> Scenario: dgrv1 was just re-ordered on column EntryDate. The row order in
> dgrv1 is now different than the row order of tmpX. You edited row 15 in
> dgrv1. But row 15 is actually row 337 in tmpX. How does the app know which
> row to update in tmpX?
>
> dim drF() As DataRow = ds.Tables("tmpX").Select("SomeKeyField = "
> someKeyvalue)
>
> then Dim dr As DataRow = drF(0)
> dr.BeginEdit
> dr(0) = dgrv1.Rows(15).Cells(0).Value
> dr(1) = dgrv1.Rows(15).Cells(1).Value
> dr(7) = dgrv1.Rows(15).Cells(7).Value
> dr(18) = dgrv1.Rows(15).Cells(18).Value
> dr.EndEdit
> da.Update(ds, "tmpX")
>
> Here I am only editing 4 fields in the datagridview. You can actually
> create a loop if you have a large table and set the values of each field to
> the value of the cells in a given row in dgrv1 even if the cell has not been
> edited - no difference - you are just passing in the current value of the
> cells.
>
> dr.BeginEdit
> For Each dc As DataColumn in ds.Tables("tmpX").Columns
> If dc.ColumnName <> "yourKeyField" Then
> dr(dc.ColumnName) = dgrv1.Rows(15).Cells(dc.ColumnName)
> End If
> Next
> dr.EndEdit
> da.Update(ds, "tmpX")
>
> Note: dr does not contain dataColums, it only contains values reference by
> the datacolumns. So you have to base the columns loop on the actual memory
> table
> ds.Tables("tmpX") when using a For Each Next type of loop. You could also
> use a For Next Loop, but you still have to base the column count on
> ds.Tables("tmpX").Columns.Count
>
> For i As Integer = 0 To ds.Tables("tmpX").Columns.Count - 1
> ...
> Next

awade@gulfstatesmanufacturers.com

In follow up to my last post....

I added the following under my comment 'update tmpx in the key_press
event

Dim dr, drF() As DataRow
drF = ds.Tables("tmpX").Select("RecordID=" &
dgrv1.Rows(SelectedRows(0).Index).Cells("RecordID" ).Value.ToString)
dr = drF(0)
dr.BeginEdit()
dr("SubscrID") =
dgrv1.Rows(SelectedRows(0).Index).Cells("SubscrID" ).Value
dr("Description") =
dgrv1.Rows(SelectedRows(0).Index).Cells("Descripti on").Value
dr.EndEdit()
da.Update(ds, "tmpX")

now when I press enter (txt2), it updates my database for that
row.... :-)

You've been a big help... i'll post again after i've played with this
in my main code a bit...

Thanks again,

-andrew


On Dec 14, 3:55 pm, wrote:
> Hi Rich,
>
> Wow... I'm so close to having the sample app do like i need my main
> one. What I've done:
>
> First, I added another field in the database called "Description",
> varChar(50). In the form load i've changed the UpdateCommand and added
> populating the text boxes as you had done earlier with your code:
>
> da.UpdateCommand = New SqlCommand
> da.UpdateCommand.Connection = conn
> da.UpdateCommand.Parameters.Add("@RecordID", SqlDbType.Int, 4,
> "RecordID")
> da.UpdateCommand.Parameters.Add("@SubscrID",
> SqlDbType.VarChar, 50, "SubscrID")
> da.UpdateCommand.Parameters.Add("@Description",
> SqlDbType.VarChar, 50, "Description")
> da.UpdateCommand.CommandText = "Update tmp3 Set SubscrID =
> @SubscrID, Description = @Description Where RecordID = @RecordID"
>
> Dim SelectedRows As DataGridViewSelectedRowCollection
> SelectedRows = dgrv1.SelectedRows
> txt0.Text = SelectedRows(0).Cells("RecordID").Value.ToString
> txt1.Text = SelectedRows(0).Cells("SubscrID").Value.ToString
> txt2.Text =
> SelectedRows(0).Cells("Description").Value.ToStrin g
>
> I've also added a third text box (txt2) and added this code:
>
> Private Sub txt2_KeyPress(ByVal sender As Object, ByVal e As
> System.Windows.Forms.KeyEventArgs) Handles txt2.KeyUp
> Dim SelectedRows As DataGridViewSelectedRowCollection
>
> If e.KeyCode = Keys.Enter Then
> SelectedRows = dgrv1.SelectedRows
> SelectedRows(0).Cells("Description").Value = txt2.Text
> 'update tmpx
> curMgr.Position += 1
> tssL2.Text = (curMgr.Position).ToString
> dgrv1.Rows(curMgr.Position).Cells(0).Selected = True
> SelectedRows = dgrv1.SelectedRows
> txt0.Text =
> SelectedRows(0).Cells("RecordID").Value.ToString
> txt1.Text =
> SelectedRows(0).Cells("SubscrID").Value.ToString
> txt2.Text =
> SelectedRows(0).Cells("Description").Value.ToStrin g
> Else
> If e.KeyCode = Keys.Down Then
> curMgr.Position += 1
> tssL2.Text = (curMgr.Position).ToString
> dgrv1.Rows(curMgr.Position).Cells(0).Selected = True
> SelectedRows = dgrv1.SelectedRows
> txt0.Text =
> SelectedRows(0).Cells("RecordID").Value.ToString
> txt1.Text =
> SelectedRows(0).Cells("SubscrID").Value.ToString
> txt2.Text =
> SelectedRows(0).Cells("Description").Value.ToStrin g
> Else
> If e.KeyCode = Keys.Up Then
> curMgr.Position -= 1
> tssL2.Text = (curMgr.Position).ToString
> dgrv1.Rows(curMgr.Position).Cells(0).Selected =
> True
> SelectedRows = dgrv1.SelectedRows
> txt0.Text =
> SelectedRows(0).Cells("RecordID").Value.ToString
> txt1.Text =
> SelectedRows(0).Cells("SubscrID").Value.ToString
> txt2.Text =
> SelectedRows(0).Cells("Description").Value.ToStrin g
> End If
> End If
> End If
> End Sub
>
> Now, I've tried cleaning that up once using a case statement... but
> then it wouldn't let me type anything into my text box. So, I'll
> revisit that later. I set the tab order in my form to give txt2 a tab
> stop of 0. Now i'm on txt2 when the form loads... my text boxes are
> all populated with the current record in the datagrid... i can
> navigate up or down with my up and down arrow keys... I can enter a
> data for the description in txt2 and it updates the grid..... BUT...
> now i'm having issues with the updating back to the database.
>
> I noticed that on the CellEndEdit uses the index of the row... and
> then the name of the column... If I wanted to update the database
> after each field has been edited, couldn't I get my index and column
> name to update tmpX? This is assuming that no reordering of my
> datagrid has been done. I saw in your earlier post how you said I
> should loop through and match my "KeyField"... and I agree... but do I
> have to put the "KeyField", like our RecordID, in my datagrid. I'm
> guessing the answer is yes, and then I hide it.
>
> Its slowly sinking in... i'm going to work some more on the update of
> tmpX from my Key_Press event.
>
> Thanks again for you help...
>
> -andrew
>
> On Dec 14, 11:38 am, Rich <> wrote:
>
> > The UpdateCommand basically transfers the edits on the memory table to the
> > server table. In my sample I edit one field and update it immediately. That
> > was just to demonstrate Editing/Updating. In reality, my users will edit
> > dozens of fields in a row and hundreds of rows before calling the
> > UpdateCommand. But the setup is a little bit different.
>
> > For the users I support, they will edit lots of stuff and then consciously
> > click a button that performs the updates. You can automatically perform an
> > update on the datagridview's RowLeave event. So you can edit several cells
> > on a row and call the updateCommand when you leave the row. You will notice
> > that the Datagridview controls has hundreds of events (and hundreds of
> > properties) very powerful control. You can custom color cells, fonts, change
> > fonts size per cell per row, ...
>
> > Anyway, if you want to update multiple fields just add a parameter to the
> > UpdateCommand for each field you want to edit. All the UpdateCommand does is
> > to transfer whatever fields were edited in the memory table tmpX to the
> > server table. --Important Note: if you edit cells in the datagridview but
> > do not pass on these edits to the memory table - the memory table has no
> > knowledge that any edits were performed. This is were drF() comes into the
> > picture:
>
> > Scenario: dgrv1 was just re-ordered on column EntryDate. The row order in
> > dgrv1 is now different than the row order of tmpX. You edited row 15 in
> > dgrv1. But row 15 is actually row 337 in tmpX. How does the app know which
> > row to update in tmpX?
>
> > dim drF() As DataRow = ds.Tables("tmpX").Select("SomeKeyField = "
> > someKeyvalue)
>
> > then Dim dr As DataRow = drF(0)
> > dr.BeginEdit
> > dr(0) = dgrv1.Rows(15).Cells(0).Value
> > dr(1) = dgrv1.Rows(15).Cells(1).Value
> > dr(7) = dgrv1.Rows(15).Cells(7).Value
> > dr(18) = dgrv1.Rows(15).Cells(18).Value
> > dr.EndEdit
> > da.Update(ds, "tmpX")
>
> > Here I am only editing 4 fields in the datagridview. You can actually
> > create a loop if you have a large table and set the values of each field to
> > the value of the cells in a given row in dgrv1 even if the cell has not been
> > edited - no difference - you are just passing in the current value of the
> > cells.
>
> > dr.BeginEdit
> > For Each dc As DataColumn in ds.Tables("tmpX").Columns
> > If dc.ColumnName <> "yourKeyField" Then
> > dr(dc.ColumnName) = dgrv1.Rows(15).Cells(dc.ColumnName)
> > End If
> > Next
> > dr.EndEdit
> > da.Update(ds, "tmpX")
>
> > Note: dr does not contain dataColums, it only contains values reference by
> > the datacolumns. So you have to base the columns loop on the actual memory
> > table
> > ds.Tables("tmpX") when using a For Each Next type of loop. You could also
> > use a For Next Loop, but you still have to base the column count on
> > ds.Tables("tmpX").Columns.Count
>
> > For i As Integer = 0 To ds.Tables("tmpX").Columns.Count - 1
> > ...
> > Next