Getting a field value into a SQL query

[bjacobson]

I am trying to execute some code on the CanInsert event. The code needs to do several queries to get field values, and ultimately sends email to those values.

Everything worked fine in testing while I hard-coded a field value for the Select statement. But now I'm trying to use the value from the current (new) record, and get the following error message:

Error executing CanInsertRecord event handler: command: rs = cn.ResultSet Function sequence error. while getting latest result set.'
cn.ResultSet subelement not found.

I've tried using both DataSubmitted.fieldname and Args.DataSubmitted.R1.fieldname, both with the same result.

Here's the first part of the code, where the error seems to be occurring. Can anyone explain how this should be done?

dim cn as sql::Connection
flag = cn.open("::Name::dbname")
dim sql as c

' replace fixed value of 110 with Args.DataSubmitted.R1.DialogueID or DataSubmitted.DialogueID and it gets the error message

sql = "select ProjectID from Dialogues where Dialogues.DialogueID = 110"
cn.Execute(sql)
rs = cn.ResultSet

[bjacobson]

Re: Getting a field value into a SQL query

Well, I thought I knew what the issue was, but that's not it either. I changed code to use SQL Argument. When I run from the Interactive Window everything is OK. When I run from application/server, I get the following error:

Error executing CanInsertRecord event handler: command: args.add("whatdialogue",dialogtemp) Property not found
args.add method not found. What does this mean - property not found??

Here's the code: I'm again using a hard-coded value for testing, but even that doesn't work from the application (works fine from Interactive, returns the correct value for ProjectID).

dim cn as sql::Connection
flag = cn.open("::Name::dbname")

dim sql as c
dim args as SQL::Arguments
dim dialogtemp as n

dialogtemp = 116
args.add("whatdialogue",dialogtemp) --- I get the error even if I delete all code after this point -----

sql = "select ProjectID from Dialogues where Dialogues.DialogueID = :whatdialogue"
cn.Execute(sql,args)
rs = cn.ResultSet

[bjacobson]

Re: Getting a field value into a SQL query

Got it. Need to have the prototype code retained.

with PageVariables
Result.Cancel = .f.
Result.ErrorHTML = ""
end with

This must be in place... put my code after the Result.ErrorHTML and works.