Subject Re: Data module with a complex index
From Akshat Kapoor <akshat.kapoor@kapoorsons.in>
Date Sat, 7 Dec 2019 13:21:51 +0530
Newsgroups dbase.getting-started
Attachment(s) datamodule.txt

On 07-12-2019 02:00, Robbie Nott wrote:
> Hi Akshat
 > Many thanks for your help
 >
> Show
 > Cat
 > Ring
 >
> It's the query on the Ring table that has me confused...
 > I'm hoping to use the cool "params" that you so kindly taught me in the
> data module.
 >
> Just not sure how to get these into the query because they come from the
> other 2 tables
 >
> FormRef.qRing.Sql = "Select * from Ring where ShowNo = :nShowNo and
> CatNo = :nCatNo order by RingNo"
 >
> FormRef.qRing.Params["nShowNo"] = FormRef.qShow.Fields["ShowNo"].Value
 > FormRef.qRing.Params["nCatNo"] = FormRef.qCat.Fields["CatNo"].Value
 >
> Thanks for your assistance
Good Afternoon Robbie,
Please find attached herewith datamodule.txt in which I have copied and
pasted 1 of the queries of my datamodule.

I am using ADO hence the syntax for declaring params is slightly different.
I have to use params[].value
In local tables you will be using
params[] = ""
Another difference would be the use of dtos() for dates.

But if you ignore the differences the way of coding will remain the same.

Now coming to main explanation

I have assigned some value to the params. The main concern here is that
value is of the correct type. Hence used date() for date.

In the form I have Refresh data button. User just has to change the
concerned entryfields/combobox and press the refresh data button.
The code for refresh data button is also attached.

In the form the param values are changed and requery is issued. The data
in rowset / grid is refreshed.


I hope this helps.

Regards
Akshat

In the data



class dlsalesDATAMODULE of DATAMODULE
   this.SALES = new ADOQUERY(this)
   with (this.SALES)
      left = 2.0
      top = 2.0
                database = mcompany.adodb
                sql = 'Select sales.* , sales.qty* sales.rate - sales.misc as amount, if(sales.mast<0,100,10) as order_col from sales19 sales WHERE ddate = :dldate AND locat = :locat AND mast <>0 ORDER BY invoice_no ,order_col'
                params["dldate"].value=dtos(date())
                params["locat"].value="0"
                requestlive = false
      active = true
   endwith


The above portion is code from Datamodule.
And below is the code from the form where the datamodule is being used.
If is part of Refresh Data button onClick event


         form.dlsalesdatamodule1.sales.params["dldate"].value         = dtos(form.dl_date.value)
         form.mdl_date = form.dl_date.value
         do case
            case lower(form.location.value) = "head office"
               form.dlsalesdatamodule1.sales.params["locat"].value= "0"
               form.mdl_locat = "0"
            case form.location.value = alltrim(mcompany.go1)
               form.dlsalesdatamodule1.sales.params["locat"].value= "1"
               form.mdl_locat = "1"
            case form.location.value = alltrim(mcompany.go2)
               form.dlsalesdatamodule1.sales.params["locat"].value= "2"                
               form.mdl_locat = "2"
         endcase
         form.dlsalesdatamodule1.sales.requery()