RecordsAffected Option for ADO Execute Method ALWAYS return 0 2

[comnetlimited]

Hi folks,

I have two Access 2000 Tables, tblCustomers & tblcustomersledger.

I use a combo box to load list from table tblCustomers
ie. Rowsource =SELECT DISTINCTROW [tblcustomers].[customerid], [tblcustomers].[customername] AS customer FROM tblcustomers;

Now I want to use a command button, cmdSubmit, to first of all get the selected record identification and search the tblcustomerledgers table using a search criteria. If there is already a record of this criteria exist then it Updates the record otherwise it Adds new record to the table.

The problem is the RecordsAffected Option of ALWAYS return 0 and ADD records is not supported . This I found when break through the program.

here is part of the code:

Private Sub cmdSubmit_Click()
On Error GoTo Err_cmdSubmit_Click
Dim tempId
Dim rstNewLoan As ADODB.Recordset
Dim strSQL As String

Set rstNewLoan = New ADODB.Recordset
strSQL = "tblcustomersledger"

Set rstNewLoan = CurrentProject.Connection.Execute( _
"SELECT * FROM tblCustomersLedger" _
& " WHERE CustomerId='" & Me.cboCustomerId.Column(0) & "'AND [AmtOutStanding]<>0", tempId)
'tempId = rstNewLoan.Fields("Customerid")

If tempId <> 0 Then
If rstNewLoan.Supports(adUpdate) Then

With rstNewLoan
.Update
.Fields("LoanAmount") = .Fields("LoanAmount") + txtLoanAmount
.Fields("Interestdue") = .Fields("Interestdue") + Interest
.Fields("AmtOutStanding") = .Fields("AmtOutStanding") + Total
.Update
cmdReset_Click
End With
End If
rstNewLoan.Close
Set rstNewLoan = Nothing
Else
If rstNewLoan.Supports(adAddNew) Then
With rstNewLoan
.AddNew
.Fields("CustomerId") = cboCustomerId
.Fields("CustomerName") = lblCustomerName.Caption
.Fields("LoanAmount") = txtLoanAmount
.Fields("LoanDate") = Date
.Fields("InterestDue") = Interest
.Fields("TotalDue") = Total
.Fields("Datedue") = Date + 14
.Fields("AmtOutStanding") = Total
.Update
cmdReset_Click
End With
End If

rstNewLoan.Close
Set rstNewLoan = Nothing
End If ' TempId

Exit_cmdSubmit_Click:
Exit Sub

Err_cmdSubmit_Click:
MsgBox Err.Description
Resume Exit_cmdSubmit_Click
End Sub


Please HELP

 

[bboffin]

The RecordsAffected parameter is only meaningful for INSERT or UPDATE SQL Statements.

No records are affected by a SELECT statement because no data has been changed so it's always set to 0.

Instead of

    If tempID = 0 Then

you should use

    If rstNewLoan.BOF and rstNewLoan.EOF Then

as this means no data has been found that matches your criteria.

Then proceed as you have coded it.

To minimise the number of database accesses you could execute an INSERT statement and if the RecordsAffected is 0 then do an UPDATE instead but this would best be done writing a Stored Procedure which can decide for itself whether to INSERT or UPDATE the record.


Bob Boffin

 

[comnetlimited]

Thank you Bboffin.
Here is how I made changes to the code...

Private Sub cmdSubmit_Click()
On Error GoTo Err_cmdSubmit_Click
Dim tempId
Dim rstNewLoan As ADODB.Recordset
Dim strSQL As String

Set rstNewLoan = New ADODB.Recordset
strSQL = "tblcustomersledger"

Set rstNewLoan = CurrentProject.Connection.Execute( _
"SELECT * FROM tblCustomersLedger" _
& " WHERE CustomerId='" & Me.cboCustomerId.Column(0) & "'AND [AmtOutStanding]<>0", tempId)
If rstNewLoan.BOF And rstNewLoan.EOF Then
If rstNewLoan.Supports(adAddNew) Then
With rstNewLoan
.AddNew
.Fields("CustomerId") = cboCustomerId
.Fields("CustomerName") = lblCustomerName.Caption
.Fields("LoanAmount") = txtLoanAmount
.Fields("LoanDate") = Date
.Fields("InterestDue") = Interest
.Fields("TotalDue") = Total
.Fields("Datedue") = Date + 14
.Fields("AmtOutStanding") = Total
.Update
End With
End If
Else
If rstNewLoan.Supports(adUpdate) Then
With rstNewLoan
.Update
.Fields("LoanAmount") = .Fields("LoanAmount") + txtLoanAmount
.Fields("Interestdue") = .Fields("Interestdue") + Interest
.Fields("AmtOutStanding") = .Fields("AmtOutStanding") + Total
.Update
End With
End If

End If ' TempId

rstNewLoan.Close
Set rstNewLoan = Nothing
cmdReset_Click

Exit_cmdSubmit_Click:
Exit Sub

Err_cmdSubmit_Click:
MsgBox Err.Description
Resume Exit_cmdSubmit_Click
End Sub

Now new problem again... when I step through I found there is a problem with the lines
1) If rstNewLoan.Supports(adAddNew) Then
2) If rstNewLoan.Supports(adUpdate) Then

The program does not seem to execute inside this loops. WHich suggest my rstNewLoan does not Supports adAddNew or adUpdate... How can i make it support them?

 

[cmmrfrds]

Normally, for a Select Statement use the Open Method. The Execute Method is used for the Insert, Update, Delete SQL strings.

rstNewLoan.Open yoursql, connection, cursortype, locktype

The execute you are using is taking default cursor type and lock type, which is a lock type of read only.

rstNewLoan.CursorLocation = adUseClient
rstNewLoan.CursorType = adOpenStatic
rstNewLoan.LockType = adLockOptimistic
rstNewLoan.Open yoursql, yourconnection