Help with callback please, trying to ensure all buyers have email address before processing...

[coleresources]

My back-end is a MS SQL 2008 database. I am trying to make sure that we have an email address for every record before doing some work. We have multiple people that we need to check for. The function below is not behaving as expected so there must be some faulty logic in it. It's returning the alert that the user needs to enter an email address whether that is the case or not. SQL is returning a row every time because it will give me the count as 0 if there are no records that meet the criteria. Is there any way to access the count in the function so that I can simply refer to that value and base my conditions on it versus using the NextRow function?

if e.dataSubmitted.SelectionID = "" then
	
	GenerateDocument = "alert('You must select a Deal first');"
	
	else
	
		dim conn as SQL::Connection
		dim args as SQL::Arguments
		dim sql as c = ""
		dim SelectionID as N = convert_type(e.dataSubmitted.SelectionID,"N")	
		
			sql ="SELECT COUNT(*) FROM BuyersCoBuyersUnionJC where selectionid = :sID and Email = ''"
			args.set("sID",SelectionID)
			FlagResult = conn.open("::name::MYDB")
			if FlagResult = .T.
			conn.PortableSQLEnabled = .t. 
			flagResult = conn.Execute(sql,args)
			dim records_found as N=0
			while conn.ResultSet.NextRow()
			       'do some processing
			        records_found=records_found+1
			        GenerateDocument = "alert('Every Buyer must have a valid email address before docusign documents can be generated');"
			end while
			
			
			if records_found==0 then
			   		GenerateDocument = "alert('You can now generate a document for Docusign');" 
			end if
					
				else 
					
			conn.close()
		end if
		end if

[Clipper87]

Re: Help with callback please, trying to ensure all buyers have email address before processing...

from the documentation topic "Learning Xbasic - Using Xbasic with SQL Tables"

flag = cn.open("::Name::AlphasportsXbasic")
dim sqlCommand as c
sqlCommand = "Select count(*) from customer where bill_state_region = :whatstate"
dim args as sql::arguments
args.add("whatstate","CA")
flag = cn.execute(sqlCommand,args)
dim rs as sql::ResultSet
rs = cn.ResultSet
count = rs.data(1)
?count
= 6

[GGandhi]

Re: Help with callback please, trying to ensure all buyers have email address before processing...

you want to know if there are records with the selectionID and email is blank
if so you can actually check that instead of sending a count query for example your code can be rewritten as follows

if e.dataSubmitted.SelectionID = "" then
	
	GenerateDocument = "alert('You must select a Deal first');"
	
	else
	
		dim conn as SQL::Connection
		dim args as SQL::Arguments
		dim sql as c = ""
		dim SelectionID as N = convert_type(e.dataSubmitted.SelectionID,"N")	
		
			sql ="SELECT * FROM BuyersCoBuyersUnionJC where selectionid = :sID and Email = ''"
			args.set("sID",SelectionID)
			FlagResult = conn.open("::name::MYDB")
			if FlagResult = .T.
			            conn.PortableSQLEnabled = .t. 
			             flagResult = conn.Execute(sql,args)
                                     if flagResult then
                                    dim rs as sql::resultSet
                                     rs = cn.resultSet
                                     flag = rs.nextRow()
                                          if flag then
                                           GenerateDocument = "alert('Every Buyer must have a valid email address before docusign documents can be generated');"
                                          else		
			   		    GenerateDocument = "alert('You can now generate a document for Docusign');" 
			                  end if
				end if
                                 conn.close()
 	
				else 
		                     GenerateDocument = "alert('Unable to establish connection to database');
		end if
end if

when I checked it looks like I matched all ifs and end ifs, please make sure.

edit: I re-read your post and if you do not want the above but only how many records the result count then
add this line to the code you have and the total count will be available here is the modified code.

if e.dataSubmitted.SelectionID = "" then
	
	GenerateDocument = "alert('You must select a Deal first');"
	
	else
	
		dim conn as SQL::Connection
		dim args as SQL::Arguments
		dim sql as c = ""
		dim SelectionID as N = convert_type(e.dataSubmitted.SelectionID,"N")	
		
			sql ="SELECT COUNT(*) FROM BuyersCoBuyersUnionJC where selectionid = :sID and Email = ''"
			args.set("sID",SelectionID)
			FlagResult = conn.open("::name::MYDB")
			if FlagResult = .T.
			conn.PortableSQLEnabled = .t. 
			flagResult = conn.Execute(sql,args)
                dim count as n
                count = rs.data(1)
                 if count > 1 then
			        GenerateDocument = "alert('Every Buyer must have a valid email address before docusign documents can be generated');"
                    else		
			    GenerateDocument = "alert('You can now generate a document for Docusign');" 
	           end if
					
				else 
					
			conn.close()
		end if
		end if

then the count will have the total record count returned b the query.

[coleresources]

Re: Help with callback please, trying to ensure all buyers have email address before processing...

Ghandi,

Thanks for the response. That is originally how I had it structured. I went with the count method after receiving unexpected results.

[coleresources]

Re: Help with callback please, trying to ensure all buyers have email address before processing...

The are all reporting that the documents are ready to be generated but they aren't.

[GGandhi]

Re: Help with callback please, trying to ensure all buyers have email address before processing...

okay, did you try with rs.data(1) to get the count?

[coleresources]

Re: Help with callback please, trying to ensure all buyers have email address before processing...

Ghandi,

The count method worked. Thank you very much for your help. For some reason, there are some records where the email was either null or blank so I had to filter on "IN (NULL, '')" and change the count so that it was > 0

Works great, thanks again.

[coleresources]

Re: Help with callback please, trying to ensure all buyers have email address before processing...

Actually there are still issues but the issue is an SQL problem, not xbasic. The filter I am using is not catching all of them. I can't do a where selectionID = :sID and Email IS NULL OR Email = '' which is what I need. It returns a ton of non-relevant rows.

[coleresources]

Re: Help with callback please, trying to ensure all buyers have email address before processing...

Proper SQL syntax if using AND as well as OR is...

where selectionID = :sID and (Email IS NULL OR Email = '')

[GGandhi]

Re: Help with callback please, trying to ensure all buyers have email address before processing...

glad to hear it worked.
very good.