Help with verify saved data,SQL

[richardurban]

I have a server side after dialog validate script on a dialog. I want to make sure that the records are written to a table before deleting them from another table. However, the following script does not work in that the records are not deleted from the cust_ord_web table after checking to see if the count of records in cust_ord is the same as that in cust_ord_web. If I run the script witout the "records count" validation, it works. Can anyone help?

ExecuteServerSideAction("Save Data::Save_cust_data")
if rtc.flagRecordWasSaved then
if variable_exists("Request.Variables.uuid")
	uuid  = alltrim(Request.Variables.uuid)
end if 
Dim cn as sql::connection 
Dim args as SQL::Arguments
args.set("ID",e.dataSubmitted.idnum)
args.set("Uuid",uuid)
flag = cn.open("::Name::urbangrocery")
dim sqlCommand as c

sqlCommand="Select count(*) from cust_ord_web where Uuid =:Uuid"
flag = cn.execute(sqlCommand,args)
dim rs as sql::ResultSet
rs = cn.ResultSet
count1=rs.data(1)


sqlCommand = "update rosterne set ID =:ID where Uuid =:Uuid"
flag = cn.execute(sqlCommand,args)
sqlCommand = "INSERT INTO cust_ord (uuid, new_code, ordered_quantity,weight,new_id) SELECT uuid, new_code, quantity, weight, new_id FROM cust_ord_web where Uuid=:Uuid"
flag = cn.execute(sqlCommand,args)
sqlCommand = "update cust_ord set new_id =:ID where Uuid =:Uuid"
flag = cn.execute(sqlCommand,args)
sqlCommand="Select count(*) from cust_ord where Uuid =:Uuid"
flag = cn.execute(sqlCommand,args)
dim rs as sql::ResultSet
rs = cn.ResultSet
count2=rs.data(1)

if val(count1) = val(count2)then
sqlCommand="DELETE FROM cust_ord_web WHERE Uuid =:Uuid"
flag = cn.execute(sqlCommand,args)
cn.close()
else
itemadd = "alert('Your order was not submitted; please try again or contact customer service.');" 



end if
end if

[Steve Workings]

Re: Help with verify saved data,SQL

Richard - Is this the part you're referring to?

if val(count1) = val(count2) then

I think count1 and count2 are already numeric values, so there's no need to use the val() function here -- in fact it may be stumbling because of the val() function. Have you tried without, e.g.

if count1 = count2 then

[richardurban]

Re: Help with verify saved data,SQL

Hi Steve. I tried removing the (val), but it still does not work.

[Steve Workings]

Re: Help with verify saved data,SQL

So that's the section you're referring to, right?

Have you run it with debug(1) ? Is the condition evaluating to true and them something within the if/end if is failing? Or, is the condition simply evaluating to false? If it's evaluating to false, what are the values of count1 and count2? If it's evaluating to true, how much gets executed? Does it execute the cn.execute()? If so, what's the value of the flag? If so, what is cn.callresult.text ?

So many questions -- you need to be a little more specific maybe about where it's failing.

[richardurban]

Re: Help with verify saved data,SQL

This script now works:

sqlCommand="Select count(*) from cust_ord_web where Uuid =:Uuid"
flag = cn.execute(sqlCommand,args)
dim rs as sql::ResultSet
rs = cn.ResultSet
count1=rs.data(1)


sqlCommand = "update rosterne set ID =:ID where Uuid =:Uuid"
flag = cn.execute(sqlCommand,args)
sqlCommand = "INSERT INTO cust_ord (uuid, new_code, ordered_quantity,weight,new_id) SELECT uuid, new_code, quantity, weight, new_id FROM cust_ord_web where Uuid=:Uuid"
flag = cn.execute(sqlCommand,args)
sqlCommand = "update cust_ord set new_id =:ID where Uuid =:Uuid"
flag = cn.execute(sqlCommand,args)
sqlCommand="Select count(*) from cust_ord where Uuid =:Uuid"
flag = cn.execute(sqlCommand,args)
dim rs as sql::ResultSet
rs2 = cn.ResultSet
count2=rs2.data(1)

if count1 = count2 then
sqlCommand="DELETE FROM cust_ord_web WHERE Uuid =:Uuid"
flag = cn.execute(sqlCommand,args)

When using result sets with SQL data, if there are multiple result sets in one script, you need to give each one a unique name; if not, the data from a previous result set in the script will show as the result set when you call the next result set.