shayne23d
asked on
adodb and data grid question
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
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
ASKER
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_RecordsetChangeComp lete(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(By Val 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
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_RecordsetChangeComp
If adStatus = adStatusOK Then
cmdDelete.Enabled = True
End If
End Sub
Private Sub Adodc1_WillChangeRecord(By
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
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_RecordsetChangeComp
If adStatus = adStatusOK Then
cmdDelete.Enabled = True
End If
End Sub
Private Sub Adodc1_WillChangeRecord(By
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