I'm creating a DataGrid in VB.Net 2003 which gets data from SQL. All columns with SQL data are BoundColumn with an additional ButtonColumn at the end to change status of a column.
In this state I can click the button of a row and the corresponding data on that row can change without issue. But my problem comes when I have to add a filter function to the DataGrid.
Once the data has ben filtered, and I try to click on the button of the 1st row, I see no changes. It is only until I remove the filter and notice that the data changed on the 1st unfiltered row.
1. Before filter data:
001 | QW34 | OK | <BUTTON>
002 | XC00 | OK | <BUTTON>
003 | GH66 | OK | <BUTTON>
2. After filter data:
002 | XC00 | OK | <BUTTON>
3. Click the button to change OK to NG and vice versa.
4. Filter data remains the same. Return back to original table state.
001 | QW34 | NG | <BUTTON>
002 | XC00 | OK | <BUTTON>
003 | GH66 | OK | <BUTTON>
Hopefully this can help explain my point. Basically, since the button column is just a normal ButtonColumn, it is not bound to the rows of the BoundColumn, so whenever there is a change in the rows from the filter, the Button column remains the same.
How can I get my Button Column to have the same properties as Bound Column? Is there addiitoanl properties I'm not aware of in VB.Net 2003 that can do this?
Here is the sub to bind my SQL data into my DataGrid. Along with the sub to filter data.
Public Sub BindGrid()
Dim strSql As String = "Select * from [SQL3].[dbo].[Final] order by [Course Code]"
Dim dtb As New DataTable
SQLDB.Open()
Dim dad As New SqlDataAdapter(strSql, SQL)
dad.Fill(dtb)
SQL.Close()
DataGrid1.DataSource = dtb
DataGrid1.DataBind()
dtb.Clear()
End Sub
Public Sub filter()
If DropDownList1.SelectedItem.ToString = "[ALL]" Then
Call BindGrid()
Else
Dim strSelectCmd As String = "Select * from [SQL3].[dbo].[Final] where result = '" & DropDownList1.SelectedItem.ToString & "'"
Dim dtb As New DataTable
SQL.Open()
Dim dad As New SqlDataAdapter(strSelectCmd, SQL)
dad.Fill(dtb)
SQL.Close()
DataGrid1.DataSource = dtb
DataGrid1.DataBind()
End If
End Sub
And here is the sub for button column in each row to change flag status and update to SQL.
Private Sub CellClick_CellValueChanged(ByVal sender As Object, ByVal e As DataGridCommandEventArgs) Handles DataGrid1.ItemCommand
Session("Employee_No") = e.Item.Cells(2).Text
Session("Course_Code") = e.Item.Cells(0).Text
Session("Flag") = e.Item.Cells(8).Text
'Change Flag Status in SQL
If Session("Flag") = "Active" Then
Dim updateState As String = "update [SQLDB3].[dbo].[ZOJT_EMP_COURSE]" _
& "set [Flag] = 'Inactive'" _
& "where Employee_No = '" & Session("Employee_No") & "'" _
& "and CourseCode = '" & Session("Course_Code") & "'"
Dim conn = SQLDB
Dim command As New SqlCommand(updateState, conn)
conn.open()
command.ExecuteNonQuery()
conn.close()
Label3.Text = Session("Course_Code")
Call filter()
conn.dispose()
command.Dispose()
'Response.Redirect(HttpContext.Current.Request.Url.ToString(), True)
ElseIf Session("Flag") = "Inactive" Then
Dim updateState As String = "update [SQLDB3].[dbo].[ZOJT_EMP_COURSE]" _
& "set [Flag] = 'Active'" _
& "where Employee_No = '" & Session("Employee_No") & "'" _
& "and CourseCode = '" & Session("Course_Code") & "'"
Dim conn = SQLDB
Dim command As New SqlCommand(updateState, conn)
conn.open()
command.ExecuteNonQuery()
conn.close()
Label3.Text = Session("Course_Code")
Call filter()
conn.dispose()
command.Dispose()
End If
End Sub