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!
|
|
|
|
|