Solved

Still further datawindow/retrieval argument problems

Posted on 2003-10-23
6
436 Views
Last Modified: 2013-12-26
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
0
Comment
Question by:Eug9
  • 2
6 Comments
 
LVL 1

Accepted Solution

by:
james_su earned 84 total points
ID: 9611295
(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()

0
 
LVL 1

Expert Comment

by:james_su
ID: 9611440
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.
0
 
LVL 8

Assisted Solution

by:gajender_99
gajender_99 earned 83 total points
ID: 9671975
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



0
 
LVL 1

Assisted Solution

by:srikanth_a
srikanth_a earned 83 total points
ID: 9683076


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









0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In our object-oriented world the class is a minimal unit, a brick for constructing our applications. It is an abstraction and we know well how to use it. In well-designed software we are not usually interested in knowing how objects look in memory. …
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org (http://seleniumhq.org) Go to that link and select download selenium in the right hand columnThat will then direct you to their downlo…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now