Re: Issues with datagridview last record updating sql database

awade@gulfstatesmanufacturers.com

Rich,

Thanks for all the help, this is some really good stuff... i'm a
little behind trying to implement some of the suggestions in your last
post... but i'm getting there.

Question: What is the CellContentClick sub used for? Does it have
some function? Or is this something that can just be deleted? It was
in the first set of code you sent me.

Private Sub dgrv1_CellContentClick(ByVal sender As System.Object,
ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles
dgrv1.CellContentClick

End Sub

Thanks....

-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
>
> "" 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