8/23/2025 5:26:33 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.
|
|
|
|
Copying existing records into new records
Posted: 29 Apr 08 4:38 PM
|
I have records that already exist in a table and I want to copy them into new records. I know the record count of the existing recordset that I am using for test purposes but I can only get one record re-created when I run the script that should add the new records. I have eliminated some of the code that I was experimenting with, but this is the code that will get me one record added for the new rocordset:
Set objRS = CreateObject("ADODB.Recordset")
With objRS
Set .ActiveConnection = Application.GetNewConnection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open "select * from TR_Breakout where 1 = 2 "
' If Not (.BOF And .EOF) Then
.AddNew
' If Not (.BOF And .EOF) Then
For I = 0 to .Recordcount - 1
.Fields("Tr_BreakoutId").Value = Application.BasicFunctions.GetIDFor("Tr_Breakout")
.Fields("Tr_ClassId").Value = strClassId
.Fields("createuser").Value = Application.BasicFunctions.CurrentUserID
.Fields("createdate").Value = Now
.Fields("breakout_name").Value = GetField("Breakout_Name", "Tr_Breakout", "Tr_ClassId = '" & strGClassId & "'")
.Fields("start_date").Value = strBrStartDate
.Fields("end_date").Value = strBrEndDate
.Fields("breakout_status").Value = GetField("Breakout_Status", "Tr_Breakout", "Tr_ClassId = '" & strGClassId & "'")
.Fields("location").Value = GetField("location", "Tr_Breakout", "Tr_ClassId = '" & strGClassId & "'")
.Fields("max_seats").Value = GetField("max_seats", "Tr_Breakout", "Tr_ClassId = '" & strGClassId & "'")
.Update
Next
' End If
.Close
End With
Any suggestions on what I need to change or add??
Thanks, |
|
|
|
Re: Copying existing records into new records
Posted: 30 Apr 08 12:40 AM
|
Do you mean you want to copy records from some table back into itself? (i.e. TR_Breakout into TR_Breakout)?
Your code above will only add one record because there is only one record to add. You need a source recordset and a target recordset to do this appropriately. See code below
(don't forget to include System:SLX Database Support script as well this allows you to save some coding time)
dim objRS, objRS2, strSQL, objSLXDB
set objSLXDB = New SLX_DB
set objRS = objSLXDB.GetNewRecordset 'creates new RS and sets cursor and lock settings for you
set objRS2 = objSLXDB.GetNewRecordset
strSQL = "select * from TR_Breakout where someconditionthatgivesyousourceset"
objRS.Open strSQL, objSLXDB.connection 'source recordset
strSQL = "select * from TR_Breakout where 1=2"
objRS2.Open strSQL, objSLXDB.connection
while not objRS.eof 'loop through the source records
with objRS2
.AddNew
.Fields("Tr_BreakoutId").Value = Application.BasicFunctions.GetIDFor("Tr_Breakout")
.Fields("Tr_ClassId").Value = objRS("Tr_ClassID")
.Fields("createuser").Value = Application.BasicFunctions.CurrentUserID 'in v7 or later this field is handled automatically by the provider and you do not need to explicitly set
.Fields("createdate").Value = Now 'in v7 or later this field is handled automatically by the provider and you do not need to explicitly set
.Fields("breakout_name").Value = objRS("breakout_name")
.Fields("start_date").Value = objRS("start_date")
.Fields("end_date").Value = objRS("end_date")
.Fields("breakout_status").Value = objRS("breakout_status")
.Fields("location").Value = objRS("location")
.Fields("max_seats").Value = objRS("max_seats")
end with
objRS.movenext 'cycle to the next record
wend
objRS2.UpdateBatch 'post all new records to the database
If the table has a large number of fields you could even break that above down further like so:
dim objRS, objRS2, strSQL, objSLXDB, i
set objSLXDB = New SLX_DB
set objRS = objSLXDB.GetNewRecordset 'creates new RS and sets cursor and lock settings for you
set objRS2 = objSLXDB.GetNewRecordset
strSQL = "select * from TR_Breakout where someconditionthatgivesyousourceset"
objRS.Open strSQL, objSLXDB.connection 'source recordset
strSQL = "select * from TR_Breakout where 1=2"
objRS2.Open strSQL, objSLXDB.connection
while not objRS.eof 'loop through the source records
i = 0
objRS2.AddNew
while i < objRS.Fields.Count
if (objRS2.Fields(i).name = "TR_BreakoutID") then
objRS2.Fields(i).value = Application.BasicFunctions.GetIDFor("Tr_Breakout")
else
objRS2.Fields(i).value = objRS.fields(i).value
end if
i = i+1
wend
objRS.movenext 'cycle to the next record
wend
objRS2.UpdateBatch 'post all new records to the database
|
|
|
|
Re: Copying existing records into new records
Posted: 30 Apr 08 8:21 AM
|
Thanks Matt,
I had a second recordset in some of the code I deleted when I posted. I looked it over after reading through your reply and I had to move a couple lines of my loop and one line for my second recordset and I got it working. Your example was exactly what I needed to get me going back in the right direction. |
|
|
|
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!
|
|
|
|
|
|
|
|