[RESOLVED] [2005] Datagrid

[onlyGirl]

Can anybody point me the correct direction in deleting rows in a datagrid? I've tried searching on msdn and even here on the forum but I still haven't found anything. This is the current code I have:

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim strRoll2 As String
        ' strRoll2 holds the Delete Query

        strRoll2 = "Delete from Param2 where ParamSetName = @ParamSetName And " _
                    & "Reservoir = @Reservoir And Description = @Description "

        Dim DaRoll As New OleDb.OleDbDataAdapter(strRoll2, gConn)
        DaRoll.DeleteCommand = New OleDbCommand(strRoll2, gConn)
        Dim rollBack As New OleDbParameter
        rollBack = DaRoll.DeleteCommand.Parameters.Add("@ParamSetName", OleDbType.VarChar)
        rollBack.SourceColumn = "ParamSetName"
        rollBack.SourceVersion = DataRowVersion.Original

        rollBack = DaRoll.DeleteCommand.Parameters.Add("@Reservoir", OleDbType.VarChar)
        rollBack.SourceColumn = "Reservoir"
        rollBack.SourceVersion = DataRowVersion.Current

        rollBack = DaRoll.DeleteCommand.Parameters.Add("@Description", OleDbType.VarChar)
        rollBack.SourceColumn = "Description"
        rollBack.SourceVersion = DataRowVersion.Current
        Dim row As DataRowCollection = dsPar.Tables(0).Rows

        ' If the delete is not successful then the Catch will be executed
        Try
            DaRoll.Update(dsPar)
        Catch x As Exception
            ' The error will be displayed in a messagebox
            MsgBox(x.Message)
        End Try
        DaRoll.Dispose()
        Me.FillDgDelete()
    End Sub

[steve_rm]

Hello,

Why don't you get the PK of the currently selected row.

pkRow = mydatagrid.currentRow.cells("PK_Row").value

Then use the PK to delete the row from the database and then fill the datagrid again with updated information from the database.

You might find it easy if you where to use a bindingsource.

Hope that helps,

Steve

[timeshifter]

Agreed.

If you had the data bound from the beginning, you wouldn't even need to make a call to get the PK. Just configure the data source to generate Update,Insert, and Delete statements. Then, in your code where you need to delete the row, you'd just do something like DetailsView1.DeleteItem() or whatever object you have bound to the data source.

[onlyGirl]

I do have the data bound while filling the datagrid. That code is here:

   Private Sub FillDgDelete()
        Try
            dsPar.Clear()
            Dim strParm As String

            strParm = "Select ParamSetName as Parameter,Reservoir,Description from Param2"

            Dim daParm As New OleDb.OleDbDataAdapter(strParm, gConn)
            daParm.Fill(dsPar)
            dgDelete.DataSource = dsPar.Tables(0)
        Catch x As Exception
            ' The error is displayed in a messagebox.
            'MsgBox(x.Message)
            MessageBox.Show("There was an error in populating the data. " & x.Message, "ReservoirGrail", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

Could I use this to delete a row? Sorry if it sounds like a dumb question, but I've never done this before?

[onlyGirl]

I'm using this instead now:

Dim dv As New DataView(Me.dsPar.Tables(0))
        dv.Delete(0)

My question now is how will i tell it to delete the current row that is highlighted instead of having to give it an index?

[timeshifter]

You pretty much have to give it the index to do anything. With a bit of fiddling around, you can create a CommandArguement and suck the index out of the object when it's triggered... fun stuff.

[onlyGirl]

Ok, I'm able to delete the row from the datagrid but if I close it and reopen it, it still has the same items that were deleted on the grid. I tried doing the .AcceptChanges() but that didn't work. This is what I have to delete the row:

Dim dv As New DataView(Me.dsPar.Tables(0))
        dv.Delete(dgDelete.CurrentRowIndex())
        dsPar.Tables(0).AcceptChanges()

        'Me.FillDgDelete()
        Me.dgDelete.DataSource = dv

[onlyGirl]

I'm now trying to use the adapter to delete the rows but it doesn't delete them. I don't get an error, it doesn't do anything. This is what i have:

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim command As OleDbCommand
        Dim parameter As OleDbParameter
 
        command = New OleDbCommand("Delete * from Param2 WHERE ParamSetName = ?", gConn)
 
        parameter = command.Parameters.Add("ParamSetName", OleDbType.Char, 10, "Parameter")
 
        parameter.SourceVersion = DataRowVersion.Original
        Me.daParm.DeleteCommand = command
    End Sub

Anybody?

[stanav]

When you delete a row in your datatable, the rowstate of that row is marked as RowState.Deleted (but the datarow is still there in the datatable until you call datatable.AcceptChanges). The table in your database has no changes until you update it... So if you call acceptchanges before actually updating your database, all it does is to remove any deleted row and reset rowstate of the reamining rows; The next time when you load the same table from database, those "supposed to have been deleted" rows will show up again...
So NEVER call acceptchanges before updating your database.

[onlyGirl]

stanav-i changed the code. could you please help me with the new code?

[onlyGirl]

Anybody?

[onlyGirl]

how can you update your changes to the datagrid? I have been trying to use the adapter.Update() but it tells me that I don't have a valid Delete Command.

Is there any other way of updating without using the adapter?