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!

Architect Forums - SalesLogix Scripting & Customization
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.

Forums RSS Feed

Back to Forum List | Back to SalesLogix Scripting & Customization | New Thread

View: Search:

Author

Thread: Problem with function

NebSeb
Posts: 49

Problem with function Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 15 Jan 08 2:13 PM

We added grades to accounts, and it is a piclist and on change i want a history record created to document the change, only I keep getting error converting varchar to int. The only INT I have is the history Type.
I have included my function and my sotred procedure i call.

Where am i messing up?

Thanks.

Sub plGradeChange(Sender)
Dim strSQL
Dim strQ
Dim strC
Dim oShell
Dim tCurrentGrade
tCurrentGrade = plGrade.Text
Dim tHistoryID
Dim tActivityID
Dim tUserID
Dim tType
Dim tAccountID
Dim tAccountName
Dim tContactName
Dim tCategory
Dim tStartDate
Dim tRecurring
Dim tTimeless
Dim tHisDescription
Dim tDuration
Dim tResult
Dim tResultCode
Dim tCreateUser
Dim tModifyUser
Dim tCompletedUser
Dim tNotes
Dim tAttachment

Dim objCon
Set objCon = Application.GetNewConnection

strSQL = "UPDATE ProspectingGrades SET GRADE = '" & plGrade.Text & "' WHERE ACCOUNTID = '" & frmAccountDetail.CurrentID & "'" 'DNL
objCon.Execute strSQL,,adExecuteNoRecords
objCon.Close
Set objCon = Nothing

tHistoryID = application.basicfunctions.GetIDFor("HISTORY")
tActivityID = application.basicfunctions.GetIDFor("HISTORY")
tUserID = application.basicfunctions.CurrentUserID
tType = 262165
tAccountID = frmAccountDetail.CurrentID
tAccountName = txtAccount.Text
tContactName = ""
tCategory = "Internal"
tStartDate = Now
tDuration ="0"
tHisDescription = "Grade changed from " + tCurrentGrade + "to " + plGrade.Text
tTimeless = "F"
tRecurring = null
tResult = "Complete"
tResultCode = "DON"
tCreateUser = application.basicfunctions.CurrentUserID
tModifyUser = application.basicfunctions.CurrentUserID
tCompletedUser = application.basicfunctions.CurrentUserID
tNotes = ""
tAttachment = "T"

Dim rs
Dim sUserName
Set rs = Application.GetNewConnection.Execute("select username from userinfo where userid = '" & tUserID & "'")
Dim tUserName
tUserName = rs.Fields("username").Value
rs.Close
Set rs = Nothing

Set oShell = CreateObject("Wscript.Shell")
UserName = oShell.ExpandEnvironmentStrings("%USERNAME%")
strQ = "EXECUTE SalesLogix.dbo.InsertHistoryRecord '"& tHistoryID &"','" & tActivityID & "','" & tType &"','" & tAccountID &"','" & tAccountName &"','" & tContactName &"','" & tCategory &"','" & tDuration &"','" & tHisDescription & "','" & tTimeless &"','" & tRecurring &"','" & tUserID &"','" & tUserName &"','" & tResult &"','" & tResultCode &"','" & tCreateUser &"','" & tModifyUser &"','" & tCompletedUser &"','" & tNotes &"','" & tAttachment &"'"
strC = "Driver={SQL Server};Server=TITAN;Database=SalesLogix;Uid=

wd=;"
'MsgBox strQ, 0
'MsgBox UserName, 0
'MsgBox Application.BasicFunctions.CurrentUserID, 0
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = strC
conn.Open
Dim objRecordset
Set objRecordset = CreateObject("ADODB.Recordset")
objRecordset.Open strQ, conn
conn.close
Set conn = nothing
tCurrentGrade = plGrade.Text

End Sub

USE [SalesLogix]
GO
/****** Object: StoredProcedure [dbo].[insertHistoryRecord] Script Date: 01/15/2008 13:12:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[insertHistoryRecord]
-- Add the parameters for the stored procedure here
@historyID char(12), @activityID char(12), @hisType int, @accountID char(12), @contactID char(12), @accountName varchar(128), @contactName varchar(64), @category varchar(64), @duration int, @hisDescription varchar(64), @timeless char(1), @recurring char(1), @activebasedon char(12), @userID char(12), @userName varchar(64), @result varchar(12), @resultCode varchar(8), @createUser char(12), @modifyUser char(12), @completedUser char(12), @notes varchar(255), @attachment char(1)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO sysdba.HISTORY
(HISTORYID, ACTIVITYID, [TYPE], ACCOUNTID, CONTACTID, OPPORTUNITYID, ACCOUNTNAME, CONTACTNAME, OPPORTUNITYNAME, PRIORITY,
CATEGORY, STARTDATE, DURATION, [DESCRIPTION], PROCESSID, PROCESSNODE, TIMELESS, RECURRING, ACTIVITYBASEDON, USERID,
USERNAME, ORIGINALDATE, RESULT, RESULTCODE, CREATEDATE, CREATEUSER, MODIFYDATE, MODIFYUSER, COMPLETEDDATE,
COMPLETEDUSER, NOTES, LONGNOTES, ATTACHMENT, USERDEF1, USERDEF2, USERDEF3, TICKETID)
VALUES (@historyID,@activityID,@hisType,@accountID,@contactID,null,@accountName,@contactName,'','',@category,getutcdate(),@duration,@hisDescription
,'','',@timeless,@recurring,null,@userID,@userName,convert(datetime,'1899-12-30 00:00:05.000', 102),@result,@resultCode,getutcdate(),@createUser,getutcdate(),@modifyUser,getutcdate(),@completedUser,@notes,null,@attachment,null,null,null,'')

END

[Reply][Quote]

Phil Parkin
Posts: 819