Well, I've been working several weeks on my first real VB project. I have learned a lot, but I am stumped on this one.
A little background, the application saves downtime information from several machines and saves the details to a SQL database. I needed to pull that data back from the SQL table for the supervisors to update some missing information (downtime reason). I created a Datasource and dataset with a datagridview. I was able to save the updated downtime reason with the binding navigator.
Now I wanted to filter my data with checkboxes, (ie 1st shift, 2nd shift, etc). I took a step backwards and created a dataview, removed the binding source, and populated the datagridview with the dataview information. The filters are all working, but I can no longer save the information back to the database. I am wondering if I have something bound incorrectly, or if the code for the binding navigator is wrong.
I have included the code for the entire form. Please excuse my inexperience. Thank you for any assistance you have!
Public Class DataEntry2
Private Sub ProductionDownTimeTableBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles ProductionDownTimeTableBindingNavigatorSaveItem.Click
Me.Validate()
Me.ProductionDownTimeTableBindingSource.EndEdit()
'Me.TableAdapterManager.UpdateAll(Me.ProductionDownTimeDataSet)
Me.TableAdapterManager.UpdateAll(Me.ProductionDownTimeDataSet)
End Sub
Private Sub DataEntry2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'ProductionDownTimeDataSet.ProductionDownTimeTable' table. You can move, or remove it, as needed.
'Me.ProductionDownTimeTableTableAdapter.Fill(Me.ProductionDownTimeDataSet.ProductionDownTimeTable)
End Sub
Private Sub DataEntry2_Closing(sender As Object, e As CancelEventArgs) Handles Me.Closing
Selection.Close()
SelectDataByLineForm.Close()
End Sub
Private Sub xP2PopulateDataBtn_Click(sender As Object, e As EventArgs) Handles xP2PopulateDataBtn.Click
'Create Connection and Dataview
Dim connetionString As String
Dim connection As SqlConnection
Dim command As SqlCommand
Dim adapter As New SqlDataAdapter
Dim ds As New DataSet
Dim dv As DataView
Dim sql As String
connetionString = "Data Source=Controls-PC;Initial Catalog=ProductionDownTime;User ID=XX;Password=XXXX"
sql = "Select * from ProductionDownTimeTable"
connection = New SqlConnection(connetionString)
Try
connection.Open()
command = New SqlCommand(sql, connection)
adapter.SelectCommand = command
adapter.Fill(ds, "Create DataView")
adapter.Dispose()
command.Dispose()
connection.Close()
'dv = ds.Tables(0).DefaultView
dv = ds.Tables(0).AsDataView
'Filters for a specific string (word) in a specicfic column.
'dv.RowFilter = String.Format("DTEventReason Like '%{0}%'", "Engineering")
'Filters for "Null" in a specicfic column.
'dv.RowFilter = "DTEventReason is Null"
'Filters looks for any column that contains NULL
'dv.RowFilter = ("DTReasonBadgeNo is Null Or DTEventReason Is Null Or DTReasonDateTime is Null")
'Filters for a shift number
'dv.RowFilter = "Shift = 2"
'Filters for LineID formatted as a string
'dv.RowFilter = String.Format("LineID Like '%{0}%'", "1N")
'Filters for LineID formatted as a string numbers only
'dv.RowFilter = String.Format("[LineID]= '1'")
'filters for multiple criteria
'dv.RowFilter = ("(DTReasonBadgeNo Is Null Or DTEventReason Is Null Or DTReasonDateTime Is Null)")
'dv.RowFilter = dv.RowFilter & "and Shift = 1"
'dv.RowFilter = dv.RowFilter & String.Format("and [LineID]= '2'")
'CheckBox Filtering code=Search for Incomplete Cells Line 2 Only
If xP2IncompleteCellsChkBox.Checked = True And _
xP2FirstShiftChkBox.Checked = False And _
xP2SecondShiftChkBox.Checked = False Then
dv.RowFilter = ("(DTReasonBadgeNo Is Null Or DTEventReason Is Null Or DTReasonDateTime Is Null)")
dv.RowFilter = dv.RowFilter & String.Format("and [LineID]= '2'")
dv.Sort = "ProductionUpDateTime"
End If
'CheckBox Filtering code=Search for Incomplete Cells on first and second shift only Line 2 Only
If xP2IncompleteCellsChkBox.Checked = True And _
xP2FirstShiftChkBox.Checked = True And _
xP2SecondShiftChkBox.Checked = True Then
dv.RowFilter = ("(DTReasonBadgeNo Is Null Or DTEventReason Is Null Or DTReasonDateTime Is Null)")
dv.RowFilter = dv.RowFilter & "and Shift=1 or Shift=2"
dv.RowFilter = dv.RowFilter & String.Format("and [LineID]= '2'")
dv.Sort = "ProductionUpDateTime"
End If
'CheckBox Filtering code=Search for Incomplete Cells on first shift only Line 2 Only
If xP2IncompleteCellsChkBox.Checked = True And _
xP2FirstShiftChkBox.Checked = True And _
xP2SecondShiftChkBox.Checked = False Then
dv.RowFilter = ("(DTReasonBadgeNo Is Null Or DTEventReason Is Null Or DTReasonDateTime Is Null)")
dv.RowFilter = dv.RowFilter & "and Shift = 1"
dv.RowFilter = dv.RowFilter & String.Format("and [LineID]= '2'")
dv.Sort = "ProductionUpDateTime"
End If
'CheckBox Filtering code=Search for Incomplete Cells on second shift only Line 2 Only
If xP2IncompleteCellsChkBox.Checked = True And _
xP2FirstShiftChkBox.Checked = False And _
xP2SecondShiftChkBox.Checked = True Then
dv.RowFilter = ("(DTReasonBadgeNo Is Null Or DTEventReason Is Null Or DTReasonDateTime Is Null)")
dv.RowFilter = dv.RowFilter & "and Shift = 2"
dv.RowFilter = dv.RowFilter & String.Format("and [LineID]= '2'")
dv.Sort = "ProductionUpDateTime"
End If
'CheckBox Filtering Code=Incomplete and Complete Cells on First Shift Only Line 2 Only
If xP2IncompleteCellsChkBox.Checked = False And _
xP2FirstShiftChkBox.Checked = True And _
xP2SecondShiftChkBox.Checked = False Then
dv.RowFilter = "Shift = 1"
dv.RowFilter = dv.RowFilter & String.Format("and [LineID]= '2'")
dv.Sort = "ProductionUpDateTime"
End If
'CheckBox Filtering Code=Incomplete and Complete Cells on Second Shift Only Line 2 Only
If xP2IncompleteCellsChkBox.Checked = False And _
xP2FirstShiftChkBox.Checked = False And _
xP2SecondShiftChkBox.Checked = True Then
dv.RowFilter = "Shift = 2"
dv.RowFilter = dv.RowFilter & String.Format("and [LineID]= '2'")
dv.Sort = "ProductionUpDateTime"
End If
'CheckBox Filtering Code=Show All Line 2 Only
If xP2IncompleteCellsChkBox.Checked = False And _
xP2FirstShiftChkBox.Checked = False And _
xP2SecondShiftChkBox.Checked = False Then
dv.RowFilter = "1 = 1"
dv.RowFilter = dv.RowFilter & String.Format("and [LineID]= '2'")
dv.Sort = "ProductionUpDateTime"
End If
'CheckBox Filter Code Show all Data from First and Second Shift Line 2 Only
If xP2IncompleteCellsChkBox.Checked = False And _
xP2FirstShiftChkBox.Checked = True And _
xP2SecondShiftChkBox.Checked = True Then
dv.RowFilter = "Shift=1 or Shift=2"
dv.RowFilter = dv.RowFilter & String.Format("and [LineID]= '2'")
dv.Sort = "ProductionUpDateTime"
End If
ProductionDownTimeTableDataGridView.DataSource = ProductionDownTimeTableBindingSource
ProductionDownTimeTableBindingSource.DataSource = dv
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
There's rarely a need to create a DataView explicitly unless you need multiple views of the same DataTable. Every DataTable is already associated with a DataView via it's DefaultView property. When you bind a DataTable, e.g. directly to a DataGridView or to a BindingSource, the data exposed actually comes from the DefaultView, which is why you're able to sort the data by clicking a column header in a grid.
If you have bound a DataTable to a BindingSource then, as @Plutonix suggests, you can sort and filter the data via that BindingSource using its Sort and Filter properties. These have the same effect as the Sort and RowFilter properties of the underlying DataView, which is the DefaultView of the bound DataTable.