Solved

Still further datawindow/retrieval argument problems

Posted on 2003-10-23
6
460 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need Tool Easily Convert Text to a quote string 5 35
edit html 3 147
I can't set a breakpoint ? 5 138
easiest way to find port number 12 113
Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deleti…
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org Go to that link and select download selenium in the right hand columnThat will then direct you to their download page.From that page s…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

679 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