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:
This is the code I use to update the database:
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?
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?