We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

"Requery" a datagrid based on changes in underlying dataset

kmoloney
kmoloney asked
on
Medium Priority
389 Views
Last Modified: 2008-01-09
Hi,

I have a datagrid (DataGrid1) on a webform.  The datagrid is "bound" to the dataset dsJobs1.  DsJobs1 is derived from OleDBAdapter1.  The datasource of OleDBAdapter1 is:

SELECT JobID, JobTitle, JobDesc
FROM Jobs
WHERE (JobFilled=0);

The connection is to a single table in a Microsoft Access Database on the server.

In the property builder of the datagrid, I am displaying the "Delete" column, and the control appears as a pushbutton.  When the "Delete" button is pushed, I don't really want it to delete the row from the database; I want it to change the value of JobFilled to -1, and redisplay the datagrid (This is an oversimplification; I'm doing other stuff, too, but for these purposes, I'll just use this as the example).  Since JobFilled is no longer 0, the row that I just "deleted" shouldn't be there.

So far, I have the following code:

    Private Sub DataGrid1_DeleteCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.DeleteCommand

        Dim intIndex = e.Item.ItemIndex
        Dim ds As DataSet
        Dim tbl As DataTable
        ds = Me.DsJobs1
        tbl = ds.Tables("Jobs")

        With tbl.Rows(intIndex)
            .Item("JobFilled") = -1
        End With

        Me.OleDbDataAdapter1.Update(ds)
        Me.DataGrid1.DataBind()
        Me.DataGrid1.EditItemIndex = -1
        Me.DataGrid1.DataBind()

    End Sub

Besides some possibly redundant coding (do I really need to databind the datagrid twice at the end of the sub?)...this is not giving me the results I'm hoping for.  The datagrid still shows the "Deleted" row.  If I exit the form, and open it back up again, it's gone, but I want it to update immediately.

Any help?

Thanks,

Kevin
Comment
Watch Question

try refilling your dataadapter after your update
that should give you the results of a new query(what you get when you open up the form again)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
That kind of works.

The first time the button is clicked, the display doesn't change.  The next time, the "deleted" row is gone.

However, if I keep on doing this a couple times, I'll get an error message "There is no row at position X" where x is the row I'm deleting.

So it seems like it's changing the data every time I do this, but only updating the display every other time.

Author

Commented:
CURRENT CODE:

 Private Sub DataGrid1_DeleteCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.DeleteCommand
        Dim intIndex = e.Item.ItemIndex
        Dim ds As DataSet
        Dim tbl As DataTable
        ds = Me.DsJobs2
        tbl = ds.Tables("Jobs")

        With tbl.Rows(intIndex)
            .Item("JobFilled") = -1
            .Item("JobFilledBy") = Session("UserID")
            .Item("JobDateFilled") = Now()
        End With

        Me.OleDbDataAdapter1.Update(ds)
        Me.OleDbDataAdapter1.Fill(ds)
        Me.DataGrid1.DataBind()
        Me.DataGrid1.EditItemIndex = -1
        Me.DataGrid1.DataBind()
    End Sub

Author

Commented:
The extra fields in the code listed should be superflous...also the fact that my dataset is named dsJobs2 instead of dsJobs1.  This is the actual code...I was trying to simplify it earlier, and then just copied and pasted the code on this one...cause I'm in a hurry! :)
That kind of works.
The first time the button is clicked, the display doesn't change.  The next time, the "deleted" row is gone.
However, if I keep on doing this a couple times, I'll get an error message "There is no row at position X" where x is the row I'm deleting.
So it seems like it's changing the data every time I do this, but only updating the display every other time

im realy not sure of why...try taking out the first DataBind() and clearing the dataset completely ...so you have

        Me.OleDbDataAdapter1.Update(ds)
        Me.DataGrid1.DataSource = Nothing
        Me.OleDbDataAdapter1.Fill(ds)
        Me.DataGrid1.DataBind()
        Me.DataGrid1.EditItemIndex = -1
    End Sub

if not...try setting a break point and making sure everything is cleared and reset each time

Author

Commented:
Yeah, its all running - display doesn't change, though.  If I hit the refresh key, it works, but I have to "resubmit" the data (probably because of the session variable?)  No solution yet.
you could always call the page_load to force it if you wont be losing anything off the page

Author

Commented:
I thought about that - that's probably what I would want.  But somewhat of a newbie.  Don't I have to pass the "Sender as System.Object" and "e as System.EventArgs" parameters?  How do I do that?

Author

Commented:
Never did find out how to force a refresh...
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.