6/19/2025 3:29:44 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss writing script in Architect plugins for SalesLogix & general SalesLogix customization topics (for Windows client only). View the code of conduct for posting guidelines.
|
|
|
|
Add to recordset while looping through?
Posted: 02 Aug 07 10:15 AM
|
I have a situation where I am looping through a RS, firing SQL on each loop to check if two values match from another table. If the values match I would like to do a calculation and add that value to the looping record set..
There is more to it than this, but I am wondering if this is possible..
Thanks |
|
|
|
Re: Add to recordset while looping through?
Posted: 02 Aug 07 12:04 PM
|
This should be possible. Are you updating a field in the current record of your recordset, or do you want to add a new record?
If updating the field, simply...
objRs.Fields("FieldName") = NewValue
objRs.Update
If adding, first ensure that all the relevant ID and CREATEUSER, CREATEDATE, etc fields are in your original recordset, which they would be if you simply did "Select * from..." Then...
objRs.AddNew
objRs.Fields("FieldName") = NewValue etc. repeating for each field you need to populate, including the ID etc.
objRs.Update
Hope this helps. |
|
|
| |
|
Re: Add to recordset while looping through?
Posted: 02 Aug 07 12:23 PM
|
Thanks Paul. I am getting 'Multiple Step Operation generated errors'..
What I am doing is taking the recordset of a grid and looping through. During the loop I fire an SQL statement to see if values match, if so I would like to append the grid RS, rsProceeds with this data..
If rsProceeds.recordcount>0 then
rsProceeds.movelast
rsProceeds.movefirst
Do while not rsProceeds.eof
strSQL="SELECT A1.ICS_FUND as fund, A1.ICS_EXPENSECURRENCY as currency " & _
"FROM ICS_EXPENSES A1 " & _
"WHERE ((A1.ICS_EXPENSECATEGORY='Non-Company Incurred') OR (A1.ICS_EXPENSECATEGORY='Reimbursable')) " & _
"AND A1.ICS_FUND='" & rsProceeds("Fund") & "' AND A1.ICS_EXPENSECURRENCY='" & rsProceeds("currency") & "' " & _
"AND (A1.ICS_DETAILID = '" & txtICSDetailID.text & "') " & _
"Union " & _
"SELECT A1.Netted_From_text as fund, A1.CURRENCY as currency " & _
"FROM C_FEES A1 " & _
"WHERE A1.Netted_From_text='" & rsProceeds("Fund") & "' AND A1.CURRENCY='" & rsProceeds("currency") & "' " & _
"AND (A1.C_OpportunitiesID = '" & txtOppID.text & "')"
set objRS = objConn.GetNewRecordset
objRS.Open strSQL, objConn.Connection
If not objRS.eof then
>>Crapping out here>>> rsProceeds.Fields("newamt").value=5
rsProceeds.update
blnCheck=true
Else
blnCheck=false
End If
rsProceeds.movenext
loop
End If
msgbox blnCheck
set rsProceeds=nothing
Code grid was created with:
With dgNetProceeds1
'.SQL.Text = "SELECT A1.SECURITYTYPE, A1.TRANCHE, A1.SECURITYINDEX, A1.FUNDEDAMOUNT, A1.ORIGINATIONFEEAMOUNT, A1.ORIGINATIONFEEPERCENT, Sum(A2.EXPECTEDAMOUNT) EXPECTED_AMOUNT, Sum(A2.EXPECTEDSYNDFEE) EXPECTEDSYNDFEE, SUM(A2.EXPECTEDSYNDFEE) - A1.ORIGINATIONFEEAMOUNT SKIM_AMOUNT, (SUM(A2.EXPECTEDSYNDFEE) - A1.ORIGINATIONFEEAMOUNT) / A1.ORIGINATIONFEEAMOUNT SKIM_PERCENT, A1.FUNDEDAMOUNT - SUM(A2.EXPECTEDAMOUNT) REMAINING_HOLD_LEVEL, SUM(A2.EXPECTEDSYNDFEE) / SUM(A2.EXPECTEDAMOUNT) SYND_FEE_PERCENT FROM SYNDICATEDSECURITIES A1 LEFT OUTER JOIN SYNDICATEDBUYERS A2 ON (A1.SYNDICATEDSECURITIESID=A2.SYNDICATEDSECURITIESID) where a1.syndicationsid = '" & SyndID & "' Group BY A1.SECURITYTYPE, A1.TRANCHE, A1.SECURITYINDEX, A1.FUNDEDAMOUNT, A1.ORIGINATIONFEEAMOUNT, A1.ORIGINATIONFEEPERCENT"
.SQL.Text="Select Sum(A1.Amount_Allocated) as amt , B1.Currency, C1.Fund, Sum(A1.Amount_AllocatedRPT) as amtrpt, 'newamt' as newamt " & _
"from sysdba.C_OPP_ASSETALLOCATION A1 " & _
"left Join sysdba.C_OPP_SECURITIES b1 " & _
"on a1.split_assetID = b1.C_Opp_SecuritiesID " & _
"left Join sysdba.Fund C1 " & _
"on A1.FUndID =C1.FundID " & _
"where a1.c_opportunitiesID = '" & txtOppID.text &"' and b1.ACASECAS='T' " & _
"group by C1.Fund, B1.Currency "
With .Columns
'remove any existing columns
If (.Count > 0) Then
For i = 0 To .Count - 1
.Item(0).Delete
Next
End If
Set col = .Add(0)
col.FieldName = "Fund"
col.Caption = "Fund"
col.Width = 150
col.ReadOnly = True
Set col = .Add(0)
col.FieldName = "Currency"
col.Caption = "Currency"
col.ReadOnly = True
Set col = .Add(0)
col.FieldName = "amt"
col.Caption = "Amount Allocated"
col.ReadOnly = True
col.Width = 90
col.FormatType = ftFixed
col.FormatString = "%.2n"
Set col = .Add(0)
col.FieldName = "amtrpt"
col.Caption = "Reporting Amount"
col.ReadOnly = True
col.Width = 90
col.FormatType = ftFixed
col.FormatString = "%.2n"
Set col = .Add(0)
col.FieldName = "newamt"
col.Caption = "newamt"
col.ReadOnly = True
col.Width = 90
col.FormatType = ftFixed
col.FormatString = "%.2n"
' col.Width = 70
End With
'now refresh the grid to see the new columns
.Refresh
End With |
|
|
|
Re: Add to recordset while looping through?
Posted: 02 Aug 07 12:31 PM
|
| The error is likely coming from the fact that the grid's data comes as a result of joins to several tables. The provider will choke on that for updates. You're going to have to just execute a separate SQL update statement for each row. |
|
|
|
Re: Add to recordset while looping through?
Posted: 02 Aug 07 1:00 PM
|
I tried this, still multi step error... Is this what you meant? I might be bound for a temp table as much as I hate to do it..
Do while not rsProceeds.eof
'Check Expenses for Fund, Currency match from Fund Allocation Rollup
strSQL="SELECT A1.ICS_FUND as fund, A1.ICS_EXPENSECURRENCY as currency " & _
"FROM ICS_EXPENSES A1 " & _
"WHERE ((A1.ICS_EXPENSECATEGORY='Non-Company Incurred') OR (A1.ICS_EXPENSECATEGORY='Reimbursable')) " & _
"AND A1.ICS_FUND='" & rsProceeds("Fund") & "' AND A1.ICS_EXPENSECURRENCY='" & rsProceeds("currency") & "' " & _
"AND (A1.ICS_DETAILID = '" & txtICSDetailID.text & "')"
memo1.Text = strSQL
set objRS = objConn.GetNewRecordset
objRS.Open strSQL, objConn.Connection
If not objRS.eof then
>Error on this line>>>> rsProceeds.Fields("newamt").value=5
rsProceeds.update
' msgbox objRS("ICS_FUND") & " * " & rsProceeds("FUND") & " <><><> " & objRS("ICS_EXPENSECURRENCY") & " * " & rsProceeds("currency")
blnCheckExp=true
Else
'msgbox "NO MATCH"
blnCheckExp=false
End If |
|
|
|
Re: Add to recordset while looping through?
Posted: 02 Aug 07 1:04 PM
|
No, still loop through the code from the grid as you were before, but instead of this:
rsProceeds.Fields("newamt").value=5
rsProceeds.update
Execute this:
"update ICS_EXPENSES set newamt = 5 WHERE ((A1.ICS_EXPENSECATEGORY='Non-Company Incurred') OR (A1.ICS_EXPENSECATEGORY='Reimbursable')) AND A1.ICS_FUND='" & rsProceeds("Fund") & "' AND A1.ICS_EXPENSECURRENCY='" & rsProceeds("currency") & "' " & _
"AND (A1.ICS_DETAILID = '" & txtICSDetailID.text & "')" |
|
|
|
Re: Add to recordset while looping through?
Posted: 02 Aug 07 1:16 PM
|
MMmmm, a bit confused..
I am trying to update the rsProceeds (grid rs), Your code has me updating the ICS_Expenses table.. Am I missing something? |
|
|
|
Re: Add to recordset while looping through?
Posted: 02 Aug 07 1:19 PM
|
Originally posted by Steve Knowles
MMmmm, a bit confused..
I am trying to update the rsProceeds (grid rs), Your code has me updating the ICS_Expenses table.. Am I missing something? |
|
Sorry, I was guessing at what your table was that you're trying to update. The point is that instead of attempting to update the recordset, just execute an update statement (or open a recordset based on a simpler query - without the joins - to update instead of using the recordset the grid is bound to) |
|
|
| |
|
Re: Add to recordset while looping through?
Posted: 02 Aug 07 9:49 PM
|
It looks to me that your rsProceeds recordset will be read only. It is based on a complex query - contains GROUP BY etc. It has been made read only because of the fact that SQL would not know how to post the results of the update back to the database at the point where you execute the .Update method.
The only way that you will be able to edit this recordset and display the results in a datagrid is by disconnecting the recordset from the underlying database. From memory, Opportunity Products works this way - it will take a bit of work to put in place.
As Ryan has suggested, if you want to update the database, you will need to execute a direct UPDATE query, or use an update-able recordset.
|
|
|
|
Re: Add to recordset while looping through?
Posted: 03 Aug 07 6:33 AM
|
| Thanks all - interesting insight as always. I ended up added another table to display what I was shooting for at the end of the routine. It solved the problem. |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|