Solved

adodb and data grid question

Posted on 2004-10-25
265 Views
Last Modified: 2010-05-02
I am having a problem figuring datagrids and adodb oout.
I ahve a data grid that is connected to the database using an adodb
What I need to know is how to make the datagrid update the databse.
I notice that when I teab out of the grid it updates the database. Is there way of calling this from a button that is easy.
When I try and do adodb1.recordset.update i get cannot insert empty row errors.

thanks in advance.

Shayne  
0
Question by:shayne23d
    4 Comments
     

    Author Comment

    by:shayne23d
    here is what I have in code.

    what am I missing


    Private rs As ADODB.Recordset
    Private Const UPDATE_CANCELLED As Long = -2147217842
    Private Const ERRORS_OCCURED As Long = -2147217887



    Private Sub Adodc1_RecordsetChangeComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
       If adStatus = adStatusOK Then
          cmdDelete.Enabled = True
       End If
    End Sub

    Private Sub Adodc1_WillChangeRecord(ByVal adReason As ADODB.EventReasonEnum, ByVal cRecords As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
       Select Case adReason
       Case adRsnUndoAddNew, adRsnUndoUpdate, adRsnFirstChange
          'Do nothing
       Case Else

         
       
      End Select
     
         
       
    End Sub

    Private Sub cmdAdd_Click()
       rs.r
    End Sub

    Private Sub cmdCancel_Click()


          rs.Cancel
         
         

       
       
       
       

    End Sub

    Private Sub cmdDelete_Click()
       If MsgBox("Are you sure you want to delete this record? ", vbQuestion + vbYesNo, "Delete REcord") = vbYes Then
          'rs.CancelUpdate
          'rs.Bookmark = rs.Bookmark
          rs.Delete
          'rs.MoveFirst
          'Adodc1.Refresh
         
       End If
         
       
    End Sub

    Private Sub MoveToRecord(intDirection As Integer)
       On Error GoTo MoveToRecord_Err
       
       Select Case intDirection
          Case adRsnMoveFirst
             rs.MoveFirst
          Case adRsnMovePrevious
             rs.MovePrevious
             If rs.BOF Then
                rs.MovePrevious
             End If
          Case adRsnMoveNext
             rs.MoveNext
             If rs.EOF Then
                rs.MovePrevious
             End If
          Case adRsnMoveLast
             rs.MoveLast
          Case adRsnAddNew
             rs.AddNew
             Adodc1.Refresh
             
             'cmdDelete.Enabled = False
          End Select
         
    MoveToRecord_exit:
          Exit Sub
    MoveToRecord_Err:
       Select Case Err.Number
          Case UPDATE_CANCELLED, ERRORS_OCCURED
             ' Do Nothing
          Case Else
             'Err.Raise Err.Number, Err.Source, Err.Description
          End Select
          Resume MoveToRecord_exit
         

    End Sub


    Private Sub cmdFindRecord_Click()
       Dim strSearch As String
       Dim strSQL As String
       
       
       
       If (Trim$(txtPLMS.Text) = "") Then 'And Trim$(txtEDMS.Text) = "") Then
           MsgBox "You must enter a PLMS#  to perform a search ", vbOKCancel
           txtPLMS.SetFocus
             Else:

         
         
             strSearch = (Trim$(txtPLMS.Text))
             strSQL = "Select * from kaiserLetters where plms_number = " & strSearch
             Adodc1.RecordSource = strSQL
             Adodc1.CommandType = adCmdText
             Adodc1.Refresh
           
          End If
             
         
    End Sub

    Private Sub cmdRefresh_Click()
    Adodc1.Refresh

    End Sub



    Private Sub DataGrid1_LostFocus()

       rs.Update
       
       
    End Sub

    Private Sub Form_Load()
       Set rs = Adodc1.Recordset
       
    End Sub

    0
     

    Author Comment

    by:shayne23d
    ok quick change sorry.
    I have the add working now the only part I am having a problem with is the delete.
    it seems that whenI delete something I need to then hit the refresh button and it all works fine. but if I call adodc1.refresh in the delete command it does not work the same? I eventually hit a record that is marked for deletion and get an error.
    Wha tam i missing there?

    here is the current code


    Private rs As ADODB.Recordset
    Private Const UPDATE_CANCELLED As Long = -2147217842
    Private Const ERRORS_OCCURED As Long = -2147217887



    Private Sub Adodc1_RecordsetChangeComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
       If adStatus = adStatusOK Then
          cmdDelete.Enabled = True
       End If
    End Sub

    Private Sub Adodc1_WillChangeRecord(ByVal adReason As ADODB.EventReasonEnum, ByVal cRecords As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
       Select Case adReason
       Case adRsnUndoAddNew, adRsnUndoUpdate, adRsnFirstChange
          'Do nothing
       Case Else

         
       
      End Select
     
         
       
    End Sub

    Private Sub cmdAdd_Click()
       Adodc1.Recordset.Update
       Adodc1.refresh
       
       
    End Sub

    Private Sub cmdCancel_Click()


          rs.Cancel
         
    End Sub

    Private Sub cmdDelete_Click()
       If MsgBox("Are you sure you want to delete this record? ", vbQuestion + vbYesNo, "Delete REcord") = vbYes Then
         
          Adodc1.refresh
         
          rs.Delete
          Adodc1.refresh
         

       End If
         
       
    End Sub




    Private Sub cmdFindRecord_Click()
       Dim strSearch As String
       Dim strSQL As String
       
       
       
       If (Trim$(txtPLMS.Text) = "") Then 'And Trim$(txtEDMS.Text) = "") Then
           MsgBox "You must enter a PLMS#  to perform a search ", vbOKCancel
           txtPLMS.SetFocus
             Else:

         
         
             strSearch = (Trim$(txtPLMS.Text))
             strSQL = "Select * from kaiserLetters where plms_number = " & strSearch
             Adodc1.RecordSource = strSQL
             Adodc1.CommandType = adCmdText
             Adodc1.refresh
           
          End If
             
         
    End Sub

    Private Sub cmdRefresh_Click()
    Adodc1.refresh

    End Sub



    Private Sub DataGrid1_LostFocus()

       rs.Update
       
       
    End Sub

    Private Sub Form_Load()
       Set rs = Adodc1.Recordset
       
    End Sub

    sorry for allthe switching around of code but the day is just like that

    thanks in advance

    shayne
    0
     
    LVL 3

    Accepted Solution

    by:
    why are you mixing adodc with adodb? the prob there is that you're havin two different recordset.
    I assume that the datasource of the datagrid is the Adodc1. maybe you could use Adodc1.Recordset.Delete instead of rs.Delete.

    0
     
    LVL 3

    Expert Comment

    by:Noel_Castillo
    Sorry. i'd just noticed that you referenced adodc1.recordset with rs. thats my mistake. but you could still try using  Adodc1.Recordset.Delete instead
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
    Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now