8/18/2025 4:28:39 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.
|
|
|
|
Issue with SQL
Posted: 22 Jan 08 11:29 AM
|
I know that I cannot call a stored procedure from SLX, or at least I dont know how to do it, so I am trying to just have it run the SQL from inside SLX upon pressing a button on the form.
I get the following error though:
ActiveX component can't create Object: ADOBD.RecordSet
It happens the second time i am calling the
Set objRS = CreateObject("ADODB.Recordset")
Help???
Sub Button1Click(Sender)
Dim strQ
Dim strC
Dim oShell
Dim tAccountID
Dim tCreateUser
Dim tModifyUser
Dim strSQL
Dim objRS
Dim tUserID
tUserID = application.basicfunctions.CurrentUserID
'This finds the current ContactID's that are not in the letter sent table and inserts them
Set objRS = CreateObject("ADODB.Recordset")
strSQL ="INSERT INTO LETTERSENT (CONTACTID, CREATEUSER, CREATEDATE, LETTERSENT) "_
& "SELECT DISTINCT CONTACT.CONTACTID, ' " &tUserID& "', getutcdate(), '0' "_
& "FROM sysdba.CONTACT "_
& "INNER JOIN HISTORY ON CONTACT.CONTACTID = HISTORY.CONTACTID "_
& "INNER JOIN PROSPECTINGGRADES ON CONTACT.ACCOUNTID = PROSPECTINGGRADES.ACCOUNTID "_
& "INNER JOIN USERINFO ON CONTACT.ACCOUNTMANAGERID = USERINFO.USERID "_
& "WHERE (PROSPECTINGGRADES.GRADE = 'a' OR PROSPECTINGGRADES.GRADE = 'a1') "_
& "AND CONTACT.CONTACTID NOT IN (SELECT CONTACTID FROM LETTERSENT)"
objRS.Open strSQL, Application.GetNewConnection
UpdateQuery()
End Sub
Sub UpdateQuery()
Dim strSQL
Dim objRS
Dim tUserID
tUserID = application.basicfunctions.CurrentUserID
'This sets the letterSetn bit for all contacts we have sent a letter to.
Set objRS = CreateObject("ADOBD.RecordSet")
strSQL = "UPDATE LETTERSENT"_
& "SET LETTERSENT = '1', MODIFYUSER = '" & tUserID & "', MODIFYDATE = getutcdate()"_
& "FROM LETTERSENT INNER JOIN"_
& "HISTORY ON ETTERSENT.CONTACTID = HISTORY.CONTACTID INNER JOIN"_
& "PROSPECTINGGRADES ON HISTORY.ACCOUNTID = PROSPECTINGGRADES.ACCOUNTID"_
& "WHERE (sysdba.PROSPECTINGGRADES.GRADE = 'a' OR PROSPECTINGGRADES.GRADE = 'a1')"_
& "AND (HISTORY.TYPE = 262153)"_
& "AND (HISTORY.DESCRIPTION LIKE 'CA Intro Letter%') AND LetterSent.LetterSent <> '1'"
objRS.Open strSQL, Application.GetNewConnection
End Sub |
|
|
|
Re: Issue with SQL
Posted: 22 Jan 08 11:44 AM
|
I don't know if this was just a typo in putting the code in the post, but you have:
Set objRS = CreateObject("ADOBD.RecordSet")
Notice the mistyped "ADOBD", this should be "ADODB". This seems to be what your error is indicating.
-Ryan |
|
|
|
Re: Issue with SQL
Posted: 22 Jan 08 11:56 AM
|
I am getting a failed to parse on this query:
strSQL = "UPDATE LETTERSENT"_
& "SET LETTERSENT = '1', MODIFYUSER = '" & tUserID & "', MODIFYDATE = getutcdate()"_
& "FROM LETTERSENT INNER JOIN"_
& "HISTORY ON ETTERSENT.CONTACTID = HISTORY.CONTACTID INNER JOIN"_
& "PROSPECTINGGRADES ON HISTORY.ACCOUNTID = PROSPECTINGGRADES.ACCOUNTID"_
& "WHERE (sysdba.PROSPECTINGGRADES.GRADE = 'a' OR PROSPECTINGGRADES.GRADE = 'a1')"_
& "AND (HISTORY.TYPE = 262153)"_
& "AND (HISTORY.DESCRIPTION LIKE 'CA Intro Letter%') AND LetterSent.LetterSent <> '1'"
Would I be better off trying to call it from a stored procedure, and if so how would i call the procedure. I dont need it to return any date, just execute the update.
Thank You, |
|
|
|
Re: Issue with SQL
Posted: 22 Jan 08 12:02 PM
|
The SLX Provider doesn't handle most UPDATE FROM type of statements, so that could be it - if that is the case you'll have to rethink how to do your update. Also, some of the lines don't have a space at the end, so you'll end up with things like "FIELD1 = 'a1')AND" which it will choke on as well.
Also to point out, this would likely not sync to remotes since you have a date inline tha is not an ISO formatted date.
Stored procs are not recommended for SLX if you have remotes. The data modified in the proc will *not* sync to remotes.
-Ryan |
|
|
| |
|
Re: Issue with SQL
Posted: 22 Jan 08 12:07 PM
|
I made sure i had spaces at the end of every line and it was doing the same thing.
IS there a good reference as to how I would call a stored procedure that doesnt return any date, and passes the userid variable to the procedure?
Thank you. |
|
|
| |
|
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!
|
|
|
|
|
|
|
|