|
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.
|
|
|
|
Creating database IDs (non Legacy)
Posted: 03 Nov 08 8:37 AM
|
I'm currently writing a script to insert contacts into my database. I'm converting a legacy script, which is where (I believe) that I'm running into problems. My question is, how do I utilize the 'DBCreateID', and 'DBInsert' functions when NOT writing in legacy code.
I get the following error message when I try to run my script:
An error occured executing active script (script name). Error calling method Main, Type mismatch: 'DBCreateID'.
Below is a subset of my code for you to see what I'm trying to do (the line that receives the error message is commented below):
Dim ContactRS, strContact
set objSLXDB = New SLX_DB
set ContactRS = objSLXDB.GetNewRecordset
strContact = "Select * from Contact where contactid = null"
'ContactTbl = dbOpenSQL(strContact, False)
ContactRS.open strContact, objSLXDB.Connection
Dim Contact3RS, strContact3
set objSLXDB = New SLX_DB
set Contact3RS = objSLXDB.GetNewRecordSet
strContact3 = "Select * from Contact3 where contactid = null"
'Contact3Tbl = dbOpenSQL(strContact3, False)
Contact3RS.open strContact3, objSLXDB.Connection
Dim AddrTblRS, strAddrTbl
set objSLXDB = New SLX_DB
set AddrTbl3RS = objSLXDB.GetNewRecordSet
strAddrTbl = "Select * from Address Where addressid = null"
'AddrTbl = dbOpenSQL(strAddrTbl, False)
AddrTbl3RS.open strAddrTbl, objSLXDB.Connection
Dim sbgLoanRS, strSQL
set objSLXDB = New SLX_DB
set sbgLoanRS = objSLXDB.GetNewRecordSet
strSQL = "Select * from sbg_loan1 where Left(LoanType, 2) = '" & DType & "' and SSI_Extract <> '" & EType & "'"
sbgLoanRS.open strSQL, objSLXDB.Connection
for i = 1 to 3 'dbRecordCount(SbgLoanRS)
SSN = sbgLoanRS.Fields("SSN").value
firstName = sbgLoanRS.Fields("Namefirst").value
lastName = sbgLoanRS.Fields("Namelast").value
upperLastName = UCase(sbgLoanRS.Fields("Namelast").value)
Add1 = sbgLoanRS.Fields("Address1").value
Add2 = sbgLoanRS.Fields("Address2").value
City = sbgLoanRS.Fields("City").value
St = sbgLoanRS.Fields("State").value
Zip = sbgLoanRS.Fields("Zip").value
Title = sbgLoanRS.Fields("Namefirst").value
Phone = sbgLoanRS.Fields("Phone").value
EstAmt = sbgLoanRS.Fields("EstimPayout").value
ReqDate = sbgLoanRS.Fields("DateRequestSent").value
SecCode = "SYST00000001"
NewAddrID = DBCreateID(AddrTbl) 'This is the line that I'm receiving the error message on
NewContactID = DBCreateID(ContactTbl)
DbInsert(ContactTbl)
DBSetValue ContactTbl, "CONTACTID", NewContactID
DBSetValue ContactTbl, "ACCOUNTID", AcctID
DBSetValue ContactTbl, "ACCOUNT", AcctName
DBSetValue ContactTbl, "ISPRIMARY", "F"
DBSetValue ContactTbl, "LASTNAME", ContLName
DBSetValue ContactTbl, "FIRSTNAME", ContFName
DBSetValue ContactTbl, "ADDRESSID", NewAddrID
DBSetValue ContactTbl, "SHIPPINGID", NewAddrID
DBSetValue ContactTbl, "WORKPHONE", Phone
DBSetValue ContactTbl, "TITLE", Title
DBSetValue ContactTbl, "ACCOUNTMANAGERID", "ADMIN"
DBSetValue ContactTbl, "LASTNAME_UC", ContULName
DBSetValue ContactTbl, "SECCODEID", SecCode
DBSetValue ContactTbl, "CREATEDATE", ReqDate
DbPost(ContactTbl)
DbInsert(ContactTbl3)
DBSetValue ContactTbl3, "CONTACTID", NewContactID
DBSetValue ContactTbl3, "ORIG_ACCOUNT", AcctName
DBSetValue ContactTbl3, "SSN", ContSSN
DBSetValue ContactTbl3, "ESTIM_PAYOUT", EstAmt
DBSetValue ContactTbl, "CREATEDATE", ReqDate
DbPost(ContactTbl3)
DbInsert(AddrTbl)
DBSetValue AddrTbl, "ADDRESSID", NewAddrID
DBSetValue AddrTbl, "DESCRIPTION", "Mailing"
DBSetValue AddrTbl, "ADDRESS1", Add1
DBSetValue AddrTbl, "ADDRESS2", Add2
DBSetValue AddrTbl, "CITY", City
DBSetValue AddrTbl, "STATE", State
DBSetValue AddrTbl, "POSTALCODE", Zip
DBSetValue AddrTbl, "ISPRIMARY", "T"
DBSetValue AddrTbl, "ISMAILING", "T"
DBSetValue AddrTbl, "SALUTATION", ContFName
DbPost(AddrTbl)
' set sbgLoan.SSI_Extract = "Yes"
DbPost(SbgLoanRS)
Next
Thanks (in advance),
Corey |
|
|
|
Re: Creating database IDs (non Legacy)
Posted: 03 Nov 08 1:28 PM
|
Hi Corey,
The DBxxxx commands were APIs that were part of the Legacy codes. They are completely replaced by standard ADO calls.
You can generate an ID either by opening a recordset (using the SalesLogix OLEDB provider) and calling slx_dbids('TABLENAME', count_of_keys_that_you_want) - this can work external to SalesLogix. If your script is running within SalesLogix then you have access to the Application object so you can get a key by calling Application.BasicFunctions.GetIDFor('TABLENAME') - which will give you one key.
To insert your records, you can replace the DBInsert/DBSetValue/DBPost with standard Recordset calls:
rs.Open "SELECT * FROM TABLENAME WHERE 1=0", cn, 3, 4
rs.AddNew
rs.Fields("TABLENAMEID").Value = ID
rs.Fieds("CREATEDATE").Value = Now
rs.Fields("CREATEUSER").Value = Application.BasicFunctions.CurrentUserId
...
rs.UpdateBatch
Regards,
Stephen
www.slxmaster.com
|
|
|
| |
| |
|
Re: Creating database IDs (non Legacy)
Posted: 04 Nov 08 1:31 PM
|
Actually no, I was kind of confused on that part of your code. So I changed when/where I was opening the recordset. Originally I opening all recordsets (4 in total) at the top of the script (one after another) but I was receiving an error...so I changed the script to open the recordset just prior to adding the fields to the recordset. Such as below.
Dim ContactRS, strContact
set objSLXDB = New SLX_DB
set ContactRS = objSLXDB.GetNewRecordset
strContact = "Select * from Contact where contactid = null"
ContactRS.open strContact, objSLXDB.Connection
ContactRS.AddNew
ContactRS.Fields("ContactID").value = NewContactID
ContactRS.Fields("AccountID").value = AcctID
ContactRS.Fields("Account").value = AcctName
ContactRS.Fields("IsPrimary").value = "F"
ContactRS.Fields("LastName").value = LastName
ContactRS.Fields("FirstName").value = FirstName
ContactRS.Fields("AddressID").value = NewAddrID
ContactRS.Fields("ShippingID").value = NewAddrID
ContactRS.Fields("WorkPhone").value = Phone
ContactRS.Fields("Title").value = Title
ContactRS.Fields("AccountManagerID").value = "Admin"
ContactRS.Fields("LastName_UC").value = upperLastName
ContactRS.Fields("SecCodeID").value = SecCode
ContactRS.Fields("CreateDate").value = ReqDate
ContactRS.UpdateBatch
Is that terribly bad coding? |
|
|
| |
|
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!
|
|
|
|
|