Question filter out some rows in datagridview

hassanintern

New member
Joined
Jun 4, 2013
Messages
4
Programming Experience
Beginner
Hi,
I am populating datagridview with a table which is designed in sql express server 2008.
it is populating but i want to filter out some rows which have IsDeleted Column = True?
how can i do that? if anyone can add a line or 2 in below code to do that, it will be great.
IsDeleted is one of the column of boolean type in the Document Table which is in DocumentManager Database.

the code is:
Imports System.Data.SqlClient
Public Class Form1
    Dim adapter As New SqlDataAdapter()
    Dim ds As New DataSet()
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
        Dim conn As New SqlConnection("Data Source=Hassan-PC\SQLEXPRESS2008;Initial Catalog=DocumentManager;Integrated Security=True")
        Dim cmd As SqlCommand = conn.CreateCommand()
        cmd.CommandText = "SELECT DocumentTitle FROM Document"
        adapter.SelectCommand = cmd
        conn.Open()
        adapter.Fill(ds, "Document")
        conn.Close()
        DataGridView1.DataSource = ds
        DataGridView1.DataMember = "Document"
    End Sub
End Class
 
Last edited by a moderator:
Firstly, if all you need is one DataTable then just create a DataTable rather than a DataSet.

As for the question, are you going to use the records where IsDeleted is True at all? If not then you should filter them out using the query, in which case nothing but the SQL code will change:
VB.NET:
SELECT DocumentTitle FROM Document WHERE IsDeleted = False
If you will use them then you can retrieve all the data as you are and then filter locally. To do that, add a BindingSource to your form and then bind the DataTable to the BindingSource and bind the BindingSource to the grid. You can then set the Filter property of the BindingSource to filter the data, e.g.
myBindingSource.Filter = "IsDeleted = False"
 
Thank you for previous reply, its behaving exactly the way i wanted.
One more question:
I want a delete button to change the IsDeleted Column in database to True (boolean type) for the selected Row in DataGridView only. The problem with my code is that it is changing for every row.
any ideas?
code:
Private Sub delete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles delete.Click
        Dim conn As New SqlConnection("Data Source=Hassan-PC\SQLEXPRESS2008;Initial Catalog=DocumentManager;Integrated Security=True")
        Dim cmd As SqlCommand = conn.CreateCommand()
        cmd.CommandText = "UPDATE Document SET IsDeleted = 'True'"
        adapter.SelectCommand = cmd
        conn.Open()
        adapter.Fill(ds, "Document")
        conn.Close()
        DataGridView1.DataSource = ds
        DataGridView1.DataMember = "Document"
    End Sub
 
Last edited by a moderator:
I already fixed the formatting on your code snippet in post #1 and now I have done it for post #3 as well. Please do it for us yourself in future.

As for the question, the immediate issue is that you have no WHERE clause in your SQL statement. It updates every record that matches the specified criteria and if there are no criteria then every record matches.

That said, you shouldn't be doing it like that anyway. That code is bad for several reasons. You've already retrieved the data from the database into a DataTable so you should be editing the data in that DataTable. As you are now using a BindingSource you can get the current record from its Current property. You then set its IsDeleted field to True. If you've already set the Filter then that row will disappear from the grid.
Dim row = DirectCast(myBindingSource.Current, DataRowView)

row("IsDeleted") = True
When you're done, you use the same data adapter to save the changes back to the database as a batch. The Fill method retrieves data and the Updated method saves changes. Click here for my own ADO.NET examples, which include using a data adapter with and without a command builder.
 
Back
Top