CODEWITHSUNDEEP
asp.netvb.netgridviewado.netdatatable
phalanx
phalanx

Reputation: 497

How to keep DataTable persistent?

I have a Gridview filled by a dataTable, filled by a DataAdapter. That's how grid is initially loaded in Page_Load. To add a search funcionality, I make the same but passing TextBox.Text as parameter to the SELECT... LIKE ... statement. To add an edit funcionality(a button in every row) I need the previous data in the dataTable, and if I performed a search before editing I need only the result of the search in my dataTable. The problem is, I don't know how to keep its value alive (persistent), and dataTable has 0 columns when I press teh edit button, so it doesn't display anything to edit. I guess it happens because I'm using Using, and dataTable is probably getting cleaned after End Using.

In that case, whta can I do to fix it? I thought removing miconn.Close() but it doesn't solve anything, in fact, I don't know if connection is still open after End Using.

Code:

Dim con As New Connection
Dim table As New DataTable()

Private Sub fill_grid()

    Using miconn As New SqlConnection(con.GetConnectionString())
        Dim sql As String = "SELECT area,lider_usuario FROM AREA"
        Using command As New SqlCommand(sql, miconn)
            Using ad As New SqlDataAdapter(command)
                ad.Fill(table)
                GridView1.DataSource = table
                GridView1.DataBind()
                'miconn.Close()
            End Using
        End Using
    End Using

End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not IsPostBack Then
        fill_grid()
        End If

End Sub

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        Dim miCon As New Connection
        Using miconn As New SqlConnection(miCon.GetConnectionString())
            Dim sql As String = "SELECT area,lider_usuario FROM AREA WHERE area LIKE @area"
            Using command As New SqlCommand(sql, miconn)
                command.Parameters.Clear()
                command.Parameters.Add("@area", SqlDbType.VarChar).Value = "%" + TextBox1.Text + "%"
                Using ad As New SqlDataAdapter(command)
                    ad.Fill(table)
                    GridView1.DataSource = table
                    GridView1.DataBind()
                    'miconn.Close()
                End Using
            End Using
        End Using
End Sub

  Protected Sub EditRow(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
        GridView1.EditIndex = e.NewEditIndex
        GridView1.DataSource = table
        GridView1.DataBind()
    End Sub

   Protected Sub CancelEditRow(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)
        GridView1.EditIndex = -1
        GridView1.DataSource = table
        GridView1.DataBind()
    End Sub

Upvotes: 0

Views: 2745

Answers (2)

Shashank Chaturvedi
Shashank Chaturvedi

Reputation: 2793

I would suggest you to make two different functions for filling the data table and binding it. Call the filling function before the !IsPostBack condition and do the binding inside the condition. Here is the sample code:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim table as new DataTable
        table = GetData() 'The function that would return a data table
        If Not IsPostBack Then
           GridView1.DataSource = table
           GridView1.DataBind()
        End If

End Sub

Private Function GetData() As DataTable
Using miconn As New SqlConnection(con.GetConnectionString())
    Dim sql As String = "SELECT area,lider_usuario FROM AREA"
    Using command As New SqlCommand(sql, miconn)
        Using ad As New SqlDataAdapter(command)
            ad.Fill(table)
            GetData = table
            miconn.Close()
        End Using
    End Using
End Using
End function

Hope it helps.

Upvotes: 0

user240141
user240141

Reputation: 

BindGrid()
{
   var dt = YourMethodReturningDatatable();
   ViewState["Table"] = dt;
   grid.DataSource = ViewState["Table"];
   grid.Databind();
}

page_load
{
   if(not ispostback) // not because my 1 key stopped working.
   {
     BindGrid();
   }
}

Upvotes: 1

Related Questions