Need example of Editing Datagridview in code

[CJwater]

Hi!

I trying to figure out the datagriview events and getting nowhere. I'd like to update the SQL database on the cell change.

Does anyone have a simple example?

Thanks
CJ

 

[ousoonerjoe]

While this may not be the best way to go about it, it has worked well for me (sample below). There are several different ways to go about it. Bind the grid to the database and let the grid manage it all is another way.

    Private Sub dgChecks_CellValidating(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellValidatingEventArgs) Handles dgChecks.CellValidating
        Dim ValDate As Date = Nothing
        Dim ValLong As Long = 0
        Dim valDec As Decimal = 0.0
        Me.Cursor = Cursors.WaitCursor
        Try
            If dgChecks.Item(e.ColumnIndex, e.RowIndex).IsInEditMode = True Then
                Select Case dgChecks.Columns(e.ColumnIndex).Name
                    Case "CheckNum"
                        If e.FormattedValue <> "" Then
                            If Long.TryParse(e.FormattedValue, ValLong) = False Then
                                e.Cancel = True
                                dgChecks.Item(e.ColumnIndex, e.RowIndex).ErrorText = "Invalid Check Number (" & _
                                        dgChecks.Item(e.ColumnIndex, e.RowIndex).Value & "). Must be a numeric value to continue."
                            Else
                                e.Cancel = False
                                dgChecks.Item(e.ColumnIndex, e.RowIndex).ErrorText = String.Empty
                            End If
                        End If
                    Case "DateCleared" ', "IssueDate"
                        If e.FormattedValue <> "" Then
                            If Date.TryParse(e.FormattedValue, ValDate) = True Then
                                If ValDate.Ticks < 1 Then
                                    e.Cancel = True
                                    dgChecks.Item(e.ColumnIndex, e.RowIndex).ErrorText = "Invalid Date(" & _
                                            dgChecks.Item(e.ColumnIndex, e.RowIndex).Value & "). Must be a valid date using the format 'mm/dd/yyyy'."
                                Else
                                    e.Cancel = False
                                    dgChecks.Item(e.ColumnIndex, e.RowIndex).ErrorText = String.Empty
                                End If
                            Else
                                e.Cancel = True
                                dgChecks.Item(e.ColumnIndex, e.RowIndex).ErrorText = "Invalid Date(" & _
                                        dgChecks.Item(e.ColumnIndex, e.RowIndex).Value & "). Must be a valid date using the format 'mm/dd/yyyy'."
                            End If
                        End If
                    Case "Amount"
                        If e.FormattedValue <> "" Then
                            If Decimal.TryParse(e.FormattedValue, valDec) = False Then
                                e.Cancel = True
                                dgChecks.Item(e.ColumnIndex, e.RowIndex).ErrorText = "Invalid Check Amount (" & _
                                        dgChecks.Item(e.ColumnIndex, e.RowIndex).Value & "). Must be a numeric value to continue."
                            Else
                                e.Cancel = False
                                dgChecks.Item(e.ColumnIndex, e.RowIndex).ErrorText = String.Empty
                            End If
                        End If
                End Select
                'End If
            End If
            txtMsg.Text = dgChecks.Item(e.ColumnIndex, e.RowIndex).ErrorText
        [COLOR=green]'Put your database update statement here if e.cancel = false.[/color]
        Catch ex As Exception
            ex.Source = System.Reflection.MethodBase.GetCurrentMethod.Name
            ErrorLog(Err.Number, Err.Description, Me.Name, ex.Source, Err.Erl)
            txtMsg.Text = "Error: " & ex.Message
        End Try
        Me.Cursor = Cursors.Default
    End Sub

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[Sorwen]

I agree with ousoonerjoe that just binding it is an option it all depends on what you do with the data. If you really just want to up date after each Cell is changed then I strongly suggest just going the DataBinding route. Also while ousoonerjoe gave a good piece of code that shows a way of validating it leaves out exactly what you asked for. Since you didn't post any code I don't have any idea how much you know so I suggest starting here

http://msdn.microsoft.com/en-us/library/h43ks021(VS.71).aspx

it includes a simple sample of updating. Not my preferred choice as I like a DataAdapter over a DataReader when dealing with databases, but it gives you a place to start.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[CJwater]

Thank you both for responding, I will rephrase my question Which "event in the datagridview" do I need to trap for each cell change the user makes to update SQL.

I should trap for this event?
"grdLookup_CellValidating(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellValidatingEventArgs) Handles grdLookup.CellValidating"

If so can I do it better than the following:

I am currently using a button click to update the datagridview using a stored procedure. But I want to update on each cell/record change.

PS: Here's my code, I'm faily new to VB.net (still thinking VB) by all means if there's an easier more efficient way please let me know.

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
' need to update the fields for the appropriate rec ID
Dim ItemNo, Loc, promoMap, strUserName, action As String
Dim StartDt, EndDt As Date
Dim ID As Int16

Dim promocost As Int16
Dim Row_Selected As Int16

strUserName = Environment.UserName.ToString()
Row_Selected = CShort(Me.grdLookup.CurrentRow.Index.ToString)
ItemNo = CStr(Me.grdLookup.Rows(Row_Selected).Cells(0).Value)
StartDt = CDate(Me.grdLookup.Rows(Row_Selected).Cells(1).Value)
EndDt = CDate(Me.grdLookup.Rows(Row_Selected).Cells(2).Value)
promocost = CShort(Me.grdLookup.Rows(Row_Selected).Cells(4).Value)
promoMap = CStr(Me.grdLookup.Rows(Row_Selected).Cells(6).Value)
Loc = CStr(Me.grdLookup.Rows(Row_Selected).Cells(7).Value)
ID = CShort(Me.grdLookup.Rows(Row_Selected).Cells(8).Value)
' add Date validation here

' Add date testing here
If StartDt.Date >= EndDt Then
MsgBox("Invalid data entry: Start Date must be earlier than the End Date!")
Return
End If
' Verify that the start date is not in the past or today!
If StartDt < DateTime.Now.Date Then
MsgBox("Invalid data entry: Start Date cannot be in the past!")
Return
End If

' Call sp to Edit record
action = "E"

UpdateItemPromo(ItemNo, Loc, StartDt, EndDt, promocost, promoMap, strUserName, ID, action)
End Sub

' Update SQL table
Private Sub UpdateItemPromo(ByVal ItemNo As String, ByVal LOC As String, ByVal StartDt As Date, ByVal EndDt As Date, ByVal promoCost As Int16, ByVal promoMap As String, ByVal user As String, ByVal ID As Int16, ByVal Action As String)
Dim Output As String = "0"
Dim Cmd As New SqlCommand
Dim connection As New SqlConnection
connection.ConnectionString = My.Settings.BostonGroup
connection.Open()

Dim params() As SqlParameter = New SqlParameter(9) {}
params(0) = New SqlParameter("@ItemNo", ItemNo)
params(1) = New SqlParameter("@Loc", LOC)
params(2) = New SqlParameter("@StartDt", StartDt)
params(3) = New SqlParameter("@EndDt", EndDt)
params(4) = New SqlParameter("@PromoCost", promoCost)
params(5) = New SqlParameter("@PromoMap", promoMap)
params(6) = New SqlParameter("@UserName", user)
params(7) = New SqlParameter("@ID", ID)
params(8) = New SqlParameter("@Action", Action)
params(9) = New SqlParameter("@Output", Output)

params(9).Direction = ParameterDirection.Output
params(9).SqlDbType = SqlDbType.VarChar

With Cmd
.Connection = connection
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_TempPriceReduction"
.CommandTimeout = 90

For Each param In params
.Parameters.Add(param)
Next

End With

Cmd.ExecuteNonQuery()

Output = params(9).Value.ToString
If Output = "1" Then
MsgBox("Contact IT record not inserted")
Else
MsgBox("Record Saved!")

End If

connection.Close()
connection = Nothing
Cmd = Nothing
params = Nothing


End Sub


Thanks Again
CJ

 

[ousoonerjoe]

Do to the nature of my example, i didn't want to post the actual update routine. I also have all my database actions wrapped up in a class. There's a lot that would be missing for the OP.

That said, I have to agree with Sorwen on this one. If you are truly wanting to update after every cell change, then simply bind your data source to the data grid. You can place all your data validation/verification in the CellValidating event. Below is an example of how to bind a DataSet to a grid.

    Private dList As DataSet
    Public Function GridSets(ByVal DtGrid As DataGridView) As Boolean
        If Cnn Is Nothing Then Cnn = New SqlClient.SqlConnection(CnnStr)
        If Cnn.State <> ConnectionState.Open Then Cnn.Open()
        If Cmd Is Nothing Then Cmd = New SqlClient.SqlCommand(CmdStr)
        If Rst Is Nothing Then Rst = New SqlClient.SqlDataAdapter(Cmd)
        If dList Is Nothing Then dList = New DataSet
        Try
            Rst.SelectCommand.Connection = Cnn
            Rst.SelectCommand = Cmd
            Rst.SelectCommand.CommandType = CmdType
            Rst.SelectCommand.CommandText = CmdStr
            Rst.Fill(dList)
            DtGrid.DataSource = dList.Tables(0)
            GridSets = True
        Catch ex As Exception
            Errs.Description = ex.Message
            Errs.Number = Err.Number
            Errs.Source = Err.Source & ".GridSets"
            Errs.Line = Err.Erl
            GridSets = False
        End Try
    End Function

I pass the grid to the function and populate the grid. I then typically will drop the class/database connections as soon as the grid is filled.

Also, you need to remember, that if you are updating on every cell, then you'll be creating more network traffic and database overhead. Remote users may get annoyed by this delay after every entry. Row updates may be an alternative worth looking at.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------