Question First Record of DataTable Failing to Update

SamuelK

New member
Joined
Jul 26, 2012
Messages
3
Programming Experience
3-5
I have a datagridview that is being populated from an SQL Server database. Whenever the user right clicks on a row in the DGV, said row is selected and a context menu appears, which includes a menu item named Status which contains several subitems. When a subitem is selected, the database should be updated to reflect the new order status. My code is working fine except that it will not update the first record. The DGV row is updated but the changes are not saved to the database.

This is the code I'm using to populate the DGV:

    Private Sub DisplaySchedule(DataGrid As DataGridView)

        dtSchedule.Clear()

        If optAll.Checked = True Then
            StatusFilter = "Status > 0"
        ElseIf optUrgent.Checked = True Then
            StatusFilter = "Status = 2 Or Status = 3"
        ElseIf optDone.Checked = True Then
            StatusFilter = "Status = 0"
        ElseIf optOnHold.Checked = True Then
            StatusFilter = "Status > 5"
        Else
            StatusFilter = "Status > 0"
        End If

        Try
            Conn = GetConnect()
            Conn.Open()
            cmdSQL = Conn.CreateCommand
            cmdSQL.CommandText = "SELECT OrderDate As 'Order Date', EnterDate As 'WO Received', JobStartDate As 'Job Start', JobCompleteDate As 'Job Complete',
 LoadDate As 'Load Date', OrderID as 'Order ID', CI.Customer_Name As Customer, JobName as 'Job', U.Firstname + ' ' +" _
                & "U.Lastname As 'Estimator', Sch.Status FROM DoorDept_Orders_Open As Sch LEFT JOIN Users As U ON U.login = Sch.Estimator
 LEFT JOIN Customer_Information As CI ON Sch.CustomerID = CI.Customer_ID WHERE Scheduled = 1 And " & StatusFilter
            daSchedule.SelectCommand = cmdSQL
            'daSchedule.Fill(dsSchedule, "Doors_Schedule")
            daSchedule.Fill(dtSchedule)
            dgSchedule.AutoGenerateColumns = False

            If dgSchedule.ColumnCount = 0 Then
                dgSchedule.Columns.AddRange(New DataGridViewColumn() _
                   { _
                      New DataGridViewTextBoxColumn With {.DataPropertyName = "Order Date", .HeaderText = "Order Date", .Name = "Order Date"}, _
                      New DataGridViewTextBoxColumn With {.DataPropertyName = "WO Received", .HeaderText = "WO Received", .Name = "WO Received"}, _
                      New CalendarColumn With {.DataPropertyName = "Job Start", .HeaderText = "Job Start", .Name = "Job Start"}, _
                      New CalendarColumn With {.DataPropertyName = "Job Complete", .HeaderText = "Job Complete", .Name = "Job Complete"}, _
                      New CalendarColumn With {.DataPropertyName = "Load Date", .HeaderText = "Load Date", .Name = "Load Date"}, _
                      New DataGridViewTextBoxColumn With {.DataPropertyName = "Order ID", .HeaderText = "Order ID", .Name = "Order ID"}, _
                      New DataGridViewTextBoxColumn With {.DataPropertyName = "Customer", .HeaderText = "Customer", .Name = "Customer"}, _
                      New DataGridViewTextBoxColumn With {.DataPropertyName = "Job", .HeaderText = "Job", .Name = "Job"}, _
                      New DataGridViewTextBoxColumn With {.DataPropertyName = "Estimator", .HeaderText = "Estimator", .Name = "Estimator"}, _
                      New DataGridViewTextBoxColumn With {.DataPropertyName = "Status", .HeaderText = "Status", .Name = "Status"}
                   } _
                )
            End If

            dgSchedule.DataSource = dtSchedule
            dgSchedule.ReadOnly = True

        Catch ex As Exception
            MessageBox.Show("Error: " & ex.Source & ": " & ex.Message, "Connection Error", MessageBoxButtons.OK)
        End Try

        Call ColorByStatus(dgSchedule)

        dgSchedule.Font = New Font("Verdana", 8)

        Call ResizeScheduleColumns()

        dgSchedule.ClearSelection()

    End Sub


This is the code I use to update the database:

    Public Sub UpdateOrderStatus(OrderID As String, NewStatus As Integer)
        Try
            Conn = GetConnect()
            Conn.Open()

            cmdSQL = New SqlCommand("Update DoorDept_Orders_Open Set Status = @pStatus Where OrderID = @pOrder", daSchedule.SelectCommand.Connection)

            cmdSQL.Parameters.Add(New SqlParameter("@pStatus", SqlDbType.Int))
            cmdSQL.Parameters("@pStatus").SourceVersion = DataRowVersion.Current
            cmdSQL.Parameters("@pStatus").SourceColumn = "Status"

            cmdSQL.Parameters.Add(New SqlParameter("@pOrder", SqlDbType.VarChar))
            cmdSQL.Parameters("@pOrder").SourceVersion = DataRowVersion.Original
            cmdSQL.Parameters("@pOrder").SourceColumn = "Order ID"

            daSchedule.UpdateCommand = cmdSQL

            daSchedule.Update(dtSchedule)

        Catch ex As Exception
            MessageBox.Show("Error: " & ex.Source & ": " & ex.Message, "Connection Error", MessageBoxButtons.OK)

        Finally
            Conn.Close()

        End Try

    End Sub


I've tried several different things, including switching from a DataSet to a DataTable to populate the DGV, but the results always come out the same, every row but the first updates correctly.

Can anyone understand what it is about the first row that is refusing to update?
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,580
Location
Sydney, Australia
Programming Experience
10+
Have you actually looked in the DataTable just before saving to see if the data you expect is there? It's important to know exactly where the issue is occurring, i.e. grid to DataTable or DataTable to database.
 

SamuelK

New member
Joined
Jul 26, 2012
Messages
3
Programming Experience
3-5
I wasn't aware of how to view an entire table at once but looked it up after your post. Assuming I'm doing it correctly


  1. breaking at daSchedule.Update(dtSchedule)
  2. hovering over the dtSchedule
  3. clicking the magnifying glass)

then the data that shows up in the DataSet Visualizer has the correct value set for the first row.

(Is it normal for it to be saying DataSet Visualizer and not DataTable and that the dropdown list labeled Table is blank?)

Edit: I tried commenting out the SourceVersion & SourceColumn lines and replaced them with
cmdSQL.Parameters("@pStatus").Value = NewStatus
cmdSQL.Parameters("@pOrder").Value = OrderID


but that didn't change anything, the first row still won't update (though the datatable reflects the changes) but the rest of the rows do.
 
Last edited:

SamuelK

New member
Joined
Jul 26, 2012
Messages
3
Programming Experience
3-5
In addition, I've noticed that if I sort the DataGridView by clicking on a column header, not only will whatever ends up in the first row not update, neither will the data that used to be in the first row. Other rows will continue to update properly, however.
 
Top Bottom