I have a server-side event 'beforesearch' that basically every time a search is made in a grid, I have data that is captured and collected into a table called 'user_tracking'. This has been working fine as a .dbf. But I am converting everything over to sql. I went through all of the xbasic help videos, and created what I thought would be the correct statement to write to this table, but it is not working. Wondering if someone can take a look at this and tell me where my error(s) are:
function BeforeSearch as v (SearchDataSubmitted as P, Args as p, PageVariables as p)
with PageVariables
end with
'Declare session variables
DIM session.UserID as c
DIM session.Account_Number as c
DIM session.Sales_Rep as c
DIM session.Vendor as c
DIM session.Part as c
DIM session.Int_Ext as c
'Get currrent user id
session.UserID = a5ws_getcurrentuser()
'Check for specific vendor search
session.Vendor = if(SearchDataSubmitted.Manufacturer = null_value(),"None",SearchDataSubmitted.Manufacturer)
'Check for specific part number search
session.Part = if(SearchDataSubmitted.Part_number = null_value(),"None",SearchDataSubmitted.Part_number)
'Open usersregistered table to retrieve account number and sales rep based on user id
tbl = table.open("[PathAlias.ADB_Path]\usersregistered_sql")
query.filter = "username="+quote(var->session.UserID)
query.order = ""
qry = tbl.query_create()
session.Account_Number = alltrim(tbl.ACCOUNT_NUMBER)
session.Sales_Rep = alltrim(tbl.SALES_REP)
tbl.close()
'check if user is internal or external
session.Int_Ext = if(session.Sales_Rep = "Internal","Internal","External" )
'write info to user tracking table
dim cn as sql::Connection
flag = cn.open("::Name::snap-sql")
dim sqlCommand as c
sqlInsert = "Insert into user_tracking (userid, account_number, date, page_title, event_type, sales_rep, vendor_specified, part_specified, int_ext, client_type, ip_address) Values (:newuserid, :newaccount_number, :newdate, :newpage_title, :newevent_type, :newsales_rep, :newvendor_specified, :newpart_specified, :newint_ext, :newclient_type, :newip_address)"
dim args as sql::arguments
args.add("newuserid", "session.UserID")
args.add("newaccount_number", "session.Account_Number")
args.add("newdate", "date()")
args.add("newpage_title", "Cameras")
args.add("newevent_type", "Product Search")
args.add("newsales_rep", "session.Sales_Rep")
args.add("newvendor_specified", "session.Vendor")
args.add("newpart_specified", "session_Part")
args.add("newint_ext", "session_Int_Ext")
args.add("newclient_type", "Desktop")
args.add("newip_address", "request.remote_addr")
flag = cn.execute(sqlCommand, sqlInsert, args)
end function
function BeforeSearch as v (SearchDataSubmitted as P, Args as p, PageVariables as p)
with PageVariables
end with
'Declare session variables
DIM session.UserID as c
DIM session.Account_Number as c
DIM session.Sales_Rep as c
DIM session.Vendor as c
DIM session.Part as c
DIM session.Int_Ext as c
'Get currrent user id
session.UserID = a5ws_getcurrentuser()
'Check for specific vendor search
session.Vendor = if(SearchDataSubmitted.Manufacturer = null_value(),"None",SearchDataSubmitted.Manufacturer)
'Check for specific part number search
session.Part = if(SearchDataSubmitted.Part_number = null_value(),"None",SearchDataSubmitted.Part_number)
'Open usersregistered table to retrieve account number and sales rep based on user id
tbl = table.open("[PathAlias.ADB_Path]\usersregistered_sql")
query.filter = "username="+quote(var->session.UserID)
query.order = ""
qry = tbl.query_create()
session.Account_Number = alltrim(tbl.ACCOUNT_NUMBER)
session.Sales_Rep = alltrim(tbl.SALES_REP)
tbl.close()
'check if user is internal or external
session.Int_Ext = if(session.Sales_Rep = "Internal","Internal","External" )
'write info to user tracking table
dim cn as sql::Connection
flag = cn.open("::Name::snap-sql")
dim sqlCommand as c
sqlInsert = "Insert into user_tracking (userid, account_number, date, page_title, event_type, sales_rep, vendor_specified, part_specified, int_ext, client_type, ip_address) Values (:newuserid, :newaccount_number, :newdate, :newpage_title, :newevent_type, :newsales_rep, :newvendor_specified, :newpart_specified, :newint_ext, :newclient_type, :newip_address)"
dim args as sql::arguments
args.add("newuserid", "session.UserID")
args.add("newaccount_number", "session.Account_Number")
args.add("newdate", "date()")
args.add("newpage_title", "Cameras")
args.add("newevent_type", "Product Search")
args.add("newsales_rep", "session.Sales_Rep")
args.add("newvendor_specified", "session.Vendor")
args.add("newpart_specified", "session_Part")
args.add("newint_ext", "session_Int_Ext")
args.add("newclient_type", "Desktop")
args.add("newip_address", "request.remote_addr")
flag = cn.execute(sqlCommand, sqlInsert, args)
end function
Comment