Counting Records using a Dialog Component

[dstroud13]

I'm sure there is a simple solution to this but I need a little help. I have a Dialog component that is bound to an SQL table. The component has 3 fields (License, Current_DateTime, and Location). I need to have a message box displayed on the client side after the data is submitted. The message should only appear if the count of the records in the table where the License = the license just submitted, the Location = the location just submitted and the date is on the same date as today and the count of these records is greater than 1. I have tried a couple different things but I keep getting error messages and I know it's because I don't really know what I'm doing. Any help that anyone could provide would be appreciated.

Thank you in advance.

[Davidk]

Re: Counting Records using a Dialog Component

What about using the Server-side event dialogValidate to test for your record and then return an error if it's already there.

I have this code running in the dialogValidate event. You'd probably need more error/data checking, but the basics are here to pull a count of records from your table and test.

Make sure your date format is ok. Dates and their formats drive me nuts.

DIM cn as SQL::Connection
dim flagResult as l 
flagResult = cn.open("::Name::YOURCONNECTIONSTRING","YOURACCOUNT","YOURPW")

if flagResult = .f. then 
	e.javascript = "alert('Error : could not connect to the database - contact support');"
	end 
end if

dim rs as sql::ResultSet
dim sqlSelect as c
dim flag as l
dim msg as c
dim jscmd as c
 
dim License as n
dim CurrDate as d
dim strDate as c
dim txtLoc as c

License = e.dataSubmitted.nbrLicense
CurrDate = e.dataSubmitted.CurrDate
strDate = date_format(CurrDate,"yyyy-MM-dd")
txtLoc = e.dataSubmitted.txtLocation

sqlSelect = "Select count(*) as cntRecs from tblpkdate WHERE KTest = " + License + " AND Key2 = '" + strDate + "' AND TestData = '" + txtLoc + "'"
	
flag = cn.Execute(sqlSelect)
if flag = .f. then 
	'there was an error - close the connection and exit
	cn.Close()
	msg = "Could not execute the query. Error reported was: " + cn.CallResult.text
	msg = js_escape(msg)
	jscmd = "alert('" + msg + "');"
	e.javascript = jscmd
	exit function 
else
	rs = cn.ResultSet
	result_count = rs.data(1)
end if 

'Your code must set these properties

if result_count > 0 then
	dialogValidate.hasError = .t.
	dialogValidate.errorText = "Record already exists"
else
	dialogValidate.hasError = .f.
	dialogValidate.errorText = ""
end if

[dstroud13]

Re: Counting Records using a Dialog Component

Thank you so much for this. I had to play around with the date format, but eventually I figured that out and it works now. Thanks again!!!