VB6 - Delete From DataGrid - Deletes the Wrong Record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jdoggz - 27 Sep 2006 16:26 GMT

Hi,

I have a datagrid that is bound to a recordset in a SQL 2000 database.
I want the user to be able to highlight the record and press a command
(delete) button to delete the record.

Currently, when the command button is pressed, it just deletes the top
record, regardless of the record I have selected. I also noticed that
even if I just hit delete without selecting a record, it deletes
whatever record is at the top.

My code is below. Any help is greatly appreciated!!!

Private Sub cmdRemove_Click()
Dim Db As ADODB.Connection
Dim tempRecSet As ADODB.Recordset
Dim strSQL As String
Dim strAccount As String
Dim strCompany As String

If MsgBox("Are you sure?", vbYesNo) = vbNo Then
Exit Sub

Else

Set Db = New ADODB.Connection
Set tempRecSet = New ADODB.Recordset

Db.CursorLocation = adUseServer
Db.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=ItemProcessing;Data Source=UF2KOBDSSQL1"
strSQL = "select * from b1accounts"
tempRecSet.Open strSQL, Db, adOpenDynamic, adLockOptimistic

DataGrid1.AllowDelete = True
tempRecSet.Delete
tempRecSet.UpdateBatch
tempRecSet.Requery
DataGrid1.AllowDelete = False
DataGrid1.Refresh
End If

End Sub

Reply to this Message

MP - 27 Sep 2006 19:13 GMT

where do you set tempRecSet to the selected record?

> strSQL = "select * from b1accounts"
> tempRecSet.Open strSQL, ...
> tempRecSet.Delete

> Hi,
>
[quoted text clipped - 6 lines]
> even if I just hit delete without selecting a record, it deletes
> whatever record is at the top.

Reply to this Message

jdoggz - 27 Sep 2006 21:16 GMT

I guess that's my question. That seems to be the missing peice, but I
have no clue of where to set that.

> where do you set tempRecSet to the selected record?
>
[quoted text clipped - 12 lines]
> > even if I just hit delete without selecting a record, it deletes
> > whatever record is at the top.

Reply to this Message

jdoggz - 28 Sep 2006 14:50 GMT

Anyone??

> Hi,
>
[quoted text clipped - 42 lines]
>
> End Sub

Reply to this Message

MP - 28 Sep 2006 15:06 GMT

> Anyone??
> > Hi,

Sorry i've never used a datagrid so don't know the methods,
doesn't it have a .SelectedItem or .SelectedRow or .Selected property or
some such thing?

I would assume you'd locate what ever record was selected in whatever way is
appropriate to a datagrid, then assuming you have a field like "ID" or some
such you would then do something like
pseudo pseudo pseudo code<g>
"select from table where "ID" = .SelectedItem.Field("ID").Value" or
something like that....

Maybe someone who actually knows will pipe up now just to show how wrong the
above is

:-)

Mark

Reply to this Message

Mark J. McGinty - 28 Sep 2006 15:43 GMT

> Hi,
>
[quoted text clipped - 8 lines]
>
> My code is below. Any help is greatly appreciated!!!

First, for single-row deletes, if you simply leave AllowDelete set to true
the DataGrid will handle all this for you.

If you want to do it yourself anyway, you'll need to keep or create a clone
of the recordset to which the grid is bound, set the bookmark property of
the clone to the bookmark property of the grid, and then act on the clone.

How to acquire a clone from the grid's datasource depends upon what you've
bound it to. ADODC exposes a recordset propertry, so if you're using that:

Dim rsClone As ADODB.Recordset
Set rsClone = ADODC1.recordset.Clone()

' alternatively, you could use this
'Dim obj As Object
'Set obj = DataGrid1.DataSource
'Set rsClone = obj.recordset.Clone()
' or if the grid is simply bound to a recordset...
'If TypeOf obj Is ADODB.Recordset Then Set rsClone = obj.Clone()

Once you have a clone, you can use the bookmark property of the grid to
position it to the same record that is selected in the grid:

' always trap errors for this assignment, because if the user is adding
' a new row using the grid, its bookmark will be invalid
rsClone.Bookmark = DataGrid1.Bookmark

' anything you do to the clone will be reflected in the grid
rsClone.Delete

' closing the clone does not close the original
rsClone.Close

A few other comments inline...

[snip]

> Db.CursorLocation = adUseServer

DataGrid works best with a client-side cursor.

> Db.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=ItemProcessing;Data Source=UF2KOBDSSQL1"
[quoted text clipped - 4 lines]
> tempRecSet.Delete
> tempRecSet.UpdateBatch

UpdateBatch is of no consequence unless a batch cursor is being used.

> tempRecSet.Requery
> DataGrid1.AllowDelete = False
> DataGrid1.Refresh

Using a clone of the recordset that's already open/bound is almost
infinitely more efficient.

-Mark

> End If
>
> End Sub

Reply to this Message

VB Forum / Database Access / September 2006

VB6 - Delete From DataGrid - Deletes the Wrong Record