Still further datawindow/retrieval argument problems

Eug9 used Ask the Experts™
Thanks again, Namasi, SysProg and James.  There are two problems that James and SysProg might have already noticed:  (1) my search criteria and results windows are different, and (2) the columns that I want to enable the user to search on are not all of the same data type.

Thanks again

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
(1)When designing your search criteria window, set the window properties->window type with "Response".  Put a singleline edit control(sle_1) on it for entering searching conditions. Put a "OK" button with script :

When your main window open this criteria window:

ls_retstr=message.stringparm       // get the search conditions

because it's a "response" window, the main window will do nothing but wait for the returning string.

(2)Analyze what the user enters, and make a sql:

string old_select, new_select, where_clause

old_select = dw_1.GetSQLSelect()

// guess what the user wants to search and make the where clause
if ls_retstr>='0' and ls_retstr<='9' then     // must be a numeric column such as age
   where_clause = "age="+ls_retstr
elseif ......... then
   where_clause = "address='"+ls_retstr+"'"
end if      

// Add the new where clause to old_select
new_select = old_select + where_clause

sorry, where_clause should be:
where_clause = "where age="+ls_retstr

where_clause = "where address='"+ls_retstr+"'"

in this way your datawindow object does not need any retrieve arguments. make sure the old_select contains no where clause.
Hi Eug9

lets say you have a datawindow where user have multi search options  but you want to retrive one at a time.

lets say you have a table from where you want to retrive data based upon any one of these Name, Post_code, Client_id, Phone_no

select *
from table
where like :a_name
select *
from table
where table.post_code= :a_post_code
select *
from table
where table.client_id = :a_clientId
select *
from table
where table.phone_no =  :a_phoneno

but rembember that while passing the value to the arguments make the varibles set to null except for the arguments you like to pass. this should bring only the data required by you.
if you pass all the values it would bring the data for each arguments passed to it. and then join together.

if you have a problem or want a example let me know.


1) Prepare the where clause in the search window and pass the Where clause as a stringparm.

2)  While preparing the where clause , it will be easy to script if the


returns a string with atleast one where condition.

If there are no where condition in the beginning add a dummy where condition.
(Eg:   primary_key  > 0  )

This should help.

String ls_sql
Integer li_client_id
String ls_name

ls_Sql = dw_1.GEtSQLSelect()

li_client_id = dw_1.Object.client_id[Row]
Data Type : Integer

If li_client_id > 0 And  Not IsNull(li_client_id) Then
ls_Sql = ls_sql + " and   client_id  = " + li_client_id
End if

ls_name = dw_1.Object.client_name[row]
Data type : String

If  Not IsNull(ls_name) And Len(ls_name) > 0 Then
  ls_sql = ls_sql + " and Upper(Trim(client_name))  =  ' " + Upper(Trim(ls_name)) +"'"
End If
(Use appropriate Trim and Upper functions depending upon the database.)

Please note that if the SQL statements of the criteria window and result window are not the same then ,

1) Either make the datawindows same  as far as the back end SQL statement is concerned.


2)  Create a datastore of the result window  in the script of the search window and use

the GetSQLSelect() to get the sql statement and append the additional where clause and

pass the entire string as a parameter to the result window for retrieve.

Please note that you have to do a SetTransObject after the SetSqlSelect function is called before retrieval.


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial