CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2012
    Posts
    13

    Delete button problem

    Okay,

    so here's my problem.
    I have a MS Hierarchical Flex Gird Control [fgdCompany] which is used to display records that are taken from a database. Everything works great but not the Delete button. What the delete button is supposed to do is when a record is selected in fgdCompany, it is supposed to delete that current record. But the problem is, it always deletes the first record in the fgdCompany. Not the record that is selected in fgdCompany. What I am doing wrong? Please help me debug this error!!!!

    Code used by me for the Delete button is given below :

    Code:
     Dim rsCompany As New ADODB.Recordset
      Dim cn As New ADODB.Connection
      Dim strSQL As String
      
      cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                            "Data Source=" & App.Path & "\Sample.mdb;" & _
                            "Persist Security Info:False"
                                                
      cn.Open
      strSQL = "SELECT [Company].* FROM [Company]"
      rsCompany.Open strSQL, cn, adOpenStatic, adLockPessimistic
      
      If rsCompany.RecordCount > 0 Then
        msg = MsgBox("Delete Record?", vbYesNo)
        If msg = vbYes Then
          rsCompany.Delete
          rsCompany.MovePrevious
          If rsCompany.EOF Then
            rsCompany.MovePrevious
          ElseIf rsCompany.BOF Then
            rsCompany.MoveNext
          End If
          If rsCompany.EOF And rsCompany.BOF Then
            txtName = ""
            txtAddress = ""
            txtTelephoneNo = ""
            txtVehiclesReg = ""
            Exit Sub
          End If
        End If
        If rsCompany.Fields("Company Name") <> "" Then
          txtName = ""
        End If
        If rsCompany.Fields("Address") <> "" Then
          txtAddress = ""
        End If
        If rsCompany.Fields("Telephone Numbers") <> "" Then
          txtTelephoneNo = ""
        End If
        If rsCompany.Fields("Vehicles Registered") <> "" Then
          txtVehiclesReg = ""
        End If
      ElseIf rsCompany.RecordCount = 0 Then
        txtName = ""
        txtAddress = ""
        txtTelephoneNo = ""
        txtVehiclesReg = ""
        MsgBox "No records to be deleted."
      End If
      
      Set fgdCompany.DataSource = rsCompany
      
      If rsCompany.RecordCount <> 0 Then
        rsCompany.Update
      End If

  2. #2
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,725

    Re: Delete button problem

    Obviously you are not specifying the record you want to delete.
    You open a new recordset which will automatically be set to point to the first record.
    Your new recordset has no idea where your flexgrid cursor is.
    What you do is determine which record you want to delete, like
    Code:
      Dim Name$, Addr$, sqlDel$
      Const colName = ? here goes the column number where the name is
      Const colAddr = ? here the column number of the address note that column numbers start at 0
    
      With fgdCompany
         Name = .TextMatrix(.Row, colName)
         Addr = .TextMatrix(.Row, colAddr)
         'now we can delete with an SQL statement
         sqlDel = "DELETE FROM [Company] WHERE [Company Name]='" & Name & "' AND [Address]='" & Addr & "'"
         cn.Execute sqlDel 'let the connection execute the delete command (the connection is assumed open here)
      End With
    Now if you still have a reference to the recordset you had put in the grid you can perform a recordset.Requery to actualize.
    If not you have to reload it.
    Code:
      Dim strSQL$, rsCompany as New ADODB.Recordset
      strSQL = "SELECT [Company].* FROM [Company]"
      rsCompany.Open strSQL, cn, adOpenStatic, adLockPessimistic
      Set fgdCompany.DataSource = rsCompany

  3. #3
    Join Date
    Jan 2000
    Location
    Saskatchewan, Canada
    Posts
    595

    Re: Delete button problem

    When you open a recordset, it points to the first record. You'll need to navigate the recordset to that your flexgrid is showing before you issue the delete.


    oops. Looks like WoF is alot quicker than me this morning.
    Last edited by d.paulson; June 27th, 2012 at 08:54 AM.

  4. #4
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: Delete button problem

    Or you could use a delete query with a where clause instead which is much more efficient than using a select then a delete.
    Always use [code][/code] tags when posting code.

  5. #5
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,725

    Re: Delete button problem

    Well, yes. That's what I had provided as a solution, isn't it?

  6. #6
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: Delete button problem

    Yep I guess you did, I somehow overlooked the delete in the first code block and saw the select in the second
    Always use [code][/code] tags when posting code.

  7. #7
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,725

    Re: Delete button problem

    Btw.: would there be another possibility to determine at which record the cursor of the grid is actually pointing?
    As I have yet found none, that's how I work around usually, provided my table has an index key field with a unique number (what most tables should have anyway).
    I write the SELECT so that the Key field is th first one in the recordset.
    When setting the FlexGrid, the key goes to column 0. We can set column 0 width to zero, so we don't see the key value.
    When wanting to know the key of the current record where the cursor is on I do:
    Code:
      CurrentKey = Val(flx.TextMatrix(flx.Row, 0))
    To delete this current record you can then simply:
    Code:
      DBConn.Execute "DELETE FROM myTable WHERE [KeyField]=" & CurrentKey
    Also a reference to the Recordset should be kept in a variable when the flex is loaded in the first place like
    Code:
      Private rsList as ADODB.Recordset
      ...
      rsList.Open SQLstring, DBConn, adOpenStatic, adLockReadOnly
      set flxList.DataSource = rsList
    So to refresh the list you can now do a rsList.ReQuery and Set flxList.DataSource = rsList

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured