Retreiving datawindow contents!! 1

[ConsMorante]

hi there...


i've been trying to retreive datawindow contents based on user input..

here's what i'm trying to do. i'm creating a window wherein user can search for thier employee record either by the following inputs: a. Employee_id b. Employee_name and lastly by Employee_department.

and upon selection of the desired search option, the datawindow object on the window should be able to retreive data from the database based on the user selected option. i can retrieve data from the database but i can't implement the retreival if its a user input..

need help here.. please!!!

 

[mbalent]

How are the input fields set up? Are they in a datawindow themselves or some other type of control? What triggers the retrieve from the database?

Matt

"Nature forges everything on the anvil of time

 

[thekl0wn]

There are many different ways to do this; each with their own benefits. Knowing what kind of controls the employee_ID, etc are in and how the DataWindow's retrieve is triggered would help to advise the "slickest" way to retrieve/filter. Also, how big is the employee table you will be querying? Are we talking hundreds of thousands, or in the tens of tens?

 

[ConsMorante]

the controls used are SingleLineEdit.


here are the objects,

the window contains a datawindow, series of Radiobuttons, which upon click displays a SLE where the user inputs the supposed data that will be used for the retrieve() event for the datawindow..

i've been thinking that the trigerring event for the reteive will be when the user presses ENTER on the SLE controls..


i think it has something to do with the SETFILTER() and the FILTER() functions..

am i right??

kindly give me ideas on how to do and achieve this..

 

[mbalent]

Without getting into a political discussion about the 'right way to do things'...

Your initial code should be in the modified event of the SLE. This code can set an instance variable for which of the three fields the user is going to query on. Then it should trigger a retrieve on your datawindow control.

In the SQLPreview event of the datawindow you can check the instance variable to 1) determine which SLE to get the user input from, and 2) know which column you are going to use in the WHERE clause of the SQL statement.

Parse out the datawindow sqlsyntax and either add or insert the appropriate WHERE clause (example " WHERE employee_id = '" + sle_emp_id.Text + "'" ) and then use the SetSQLPreview method to have your modified statement sent to the DB.

Make sure you check the return values from any methods to help in troubleshooting any syntax errors.

Look in the online help for the SQLPreview and SetSQLPreview methods.

Matt

"Nature forges everything on the anvil of time

 

[thekl0wn]

Another set of commands to look into would be the GetSQLSelect( ) and SetSQLSelect( ). Here's one of two ways that I would typically do this:

String ls_id, ls_name, ls_dept, ls_select, ls_where

//load the variables
ls_select = dw_1.GetSQLSelect( )
ls_id = sle_id.Text
ls_name = sle_name.Text
ls_dept = sle_dept.Text

//ID
IF ls_id <> '' THEN
ls_where += "WHERE Employee.ID = '" + ls_id + "' "
END IF

//Name
IF ls_name <> '' THEN
IF ls_where = '' THEN
ls_where = 'WHERE '
ELSE
ls_where += ' AND '
END IF

//column name
ls_where += 'Employee.Name '

//figure out whether to use LIKE or =
//LIKE is for "wildcard" searches
IF Right( ls_name, 1 ) = '%' THEN
ls_where += 'LIKE '
ELSE
ls_where += '= '
END IF

ls_where += "'" + ls_name + "' "
END IF

//dept follows same suite as above...

//assuming there is no WHERE clause in the DW's original SELECT
ls_select += ' ~r' + ls_where

dw_1.SetSQLSelect( ls_select )
dw_1.SetTransObject( Whatever_Trans )
dw_1.Retrieve( )

 

[thekl0wn]

For only three arguments, I would possibly pass in retrieval arguments. Assign the arguments in the datawindow painter under the SQL script from the Design menu, and then use a syntax in the SQL statement like:

WHERE ( emp.id = :as_is OR :as_id = '' ) AND
( emp.name = :as_name OR :as_name = '' ) AND
( emp.dept = :as_dept OR :as_dept = '' )

--'' values are for when there are no parms to pass in

-----------------------------------------------------

Then retrieve using dw_1.Retrieve( ls_id, ls_name, ls_dept ). You may want to create an event in the window to retrieve, and then call that event from the modify event of each of the sle's.

 

[ConsMorante]

hi there..

iv'e tried what theKlown suggested but every time the modified event triggers the retrieve() function it produces an error.. it says:

"Database Transaction information not available
Call SetTrans or SetTransObject Function"

what should i do??

 

[mbalent]

Somewhere in the window open or datawindow constructor event you need to set the transaction object. See online help for 'SetTransObject'.

Matt

"Nature forges everything on the anvil of time

 

[thekl0wn]

So, you're getting results when the DataWindow first loads? If so, then it sounds like you have the transaction object set up, but need to call the dw.SetTransObject( trans ) function again before trying to retrieve again.

 

[ConsMorante]

Nope.. before i even get to activate.. the error occurs.. i did set the transobject.. but still the error still keep on popping up..

 

[thekl0wn]

Could you post up your retrieval event? Also, how/where is your transaction set up?

 

[ConsMorante]

hi..

i was able to solve this problem. the thing that's making the error is that the transobject was set after the window opens and the event that will retrieve() function..

whooooa.. that was just it.. ^_^

thanks by the way to all who replied to this thread..