Still further datawindow/retrieval argument problems

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

Eugene
Eug9Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

james_suCommented:
(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 :
closewithreturn(parent,sle_1.text)

When your main window open this criteria window:

open(w_search)
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
dw_1.SetSQLSelect(new_select)
dw_1.retrieve()

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
james_suCommented:
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.
gajender_99Commented:
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 table.name like :a_name
union
select *
from table
where table.post_code= :a_post_code
union
select *
from table
where table.client_id = :a_clientId
union
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.

thanks
Gajender



srikanth_aCommented:


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

dw_1.GetSqlSelect()

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.

or

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.


Srikanth









It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.