Hi and thanks for the help.

At design time I can double click on a Dataset, under the Data Set Manager window panel, and bring the dataset editor. If I click on the Row Source Tab, Click on the query radio button, then enter any SQL Select statenments, and on the base table, I can select the Table there.

I would like to do this at run time. Be able to assign SQL Statement to my table. Can this be done on this later version? How?

A code sample would be appreciated, and most likely will greatly benefit others as well.

Thanks so much for the help.

Regards

Carlos

<<Carlos wrote:

Hi and thanks for the help.

At design time I can double click on a Dataset, under the Data Set Manager window panel, and bring the dataset editor. If I click on the Row Source Tab, Click on the query radio button, then enter any SQL Select statenments, and on the base table, I can select the Table there.

I would like to do this at run time. Be able to assign SQL Statement to my table. Can this be done on this later version? How?

A code sample would be appreciated, and most likely will greatly benefit others as well.

Thanks so much for the help.

Regards

Carlos>>

Hi Carlos:
Add a button to the form and in the onclick event, add these lines.

"TForm1.Button8Click procedure (Sender: TObject);
begin
  prueba.Params.Clear; / / prueba is the name of the DataSet defined in my example
  prueba.Params.Add ('_name =''' +
                     trim (Edit3.Text) + '%' +'''');

  Database.Load (prueba);
end; "

In this case such as parameter passing (_name) (Edit3.Text content), for making queries. This works similar to a SQL query.

And at the DataSet Manager: Row Source, Query set, add the following:

"select * from <table_name> where <row_name>  like {_name = 'DefaultValue'}"

The execution of this SQL statement, replacing 'DefaultValue' by the value passed as a parameter.
This way of working with parameters, you can make the SQL query at runtime.

Check this part of the manual:

http://www.elevatesoft.com/manual?action=viewtopic&id=ewb1&topic=Creating_Loading_DataSets

I hope I was helpful
Alvaro

Hi Alvaro,

Thanks so much for your help.

I do appreciate your sample code. It worked very well. Now I have a clear view on how to create pass though query with parameters thanks to you.
And to clear a little bit more your code and so for others who get the chance to read the post, and are new to EWB (Elevate Web Builder) could have a better understanding how this whole thing works here is my sample query based on your help:

In my case I am connecting to MSSQL Server via an ADO Dataset

1. I have a Customer Table in my MSSQL database
2. Under my Dataset Manager in EWB I clicked on the Add New Dataset Icon
3. Under Type I selected (ADO), and Name : CustomerQuery

4. Under the Connection Property Tab, I simple went to my Application in Delphi, Data Module clicked on my ADO connection, and copied the complete Connection String , then back to the Connection Property Tab in EWB and pasted the values inside the Connection String, then Under the Provider Property Value I entered SQLOLEDB, and under the DefaultDatabase property, I left it blank.

5. Click on test connection, in my case I had a successful connection.
6. Clicked on the Row Source Tab, instead of having the Table radio button Selected, I selected the Query Radio Button and these are the Values I entered:

select * from Customers where Cust_id  like {_Cust_id = 'DefaultValue'}
What does the above means in plain English?
Get me all the records from the Customer Table where the Column "CUST_ID" is like the value acquired by _Cust_Id Parameter

7. Click OK to save the values
8. Dragged and dropped my newly created DataSet CustomerQuery into my Form
9. Under the Properties values I set the property “name” to dtsetCustomers, so I can make reference to the Dataset in my code to this new name.

10. I then created  a new Edit Field under my form, and Named editEnter_Customer_Id
11. I created the Button that you told me and Named it btnGet_Customers and Captioned Get Customer(s).

12. On the OnClick Event of my Newly Created Button,  I entered what you told me to enter, which is the query with the parameters. This parameter will wait to get any values entered in the newly created Edit Field named editEnter_Customer_Id.

procedure TForm1.btnGet_CustomersClick(Sender: TObject);
begin
  dtsetCustomers.Params.Clear; //dtsetCustomers is the name of the DataSet
  dtsetCustomers.Params.Add ('_Cust_id =''' + trim (editEnter_Customer_id.Text) + '%' +'''');
  Database.Load (dtsetCustomers);//Load the DataSet with the query result if any.
end;
So, if I run my App, and I type 10265 inside my editEnter_Customer_id field as a Customer Id, in my case I get
Cust Id   Customer Name City         State         Address
10265    Andrew Smith     Miami    Florida      1525 S.W Flagler Terrace


So, I hope that your help keeps on helping others.

I thank you greatly

Best regards,
Carlos






Alvaro wrote:

<<Carlos wrote:

Hi and thanks for the help.

At design time I can double click on a Dataset, under the Data Set Manager window panel, and bring the dataset editor. If I click on the Row Source Tab, Click on the query radio button, then enter any SQL Select statenments, and on the base table, I can select the Table there.

I would like to do this at run time. Be able to assign SQL Statement to my table. Can this be done on this later version? How?

A code sample would be appreciated, and most likely will greatly benefit others as well.

Thanks so much for the help.

Regards

Carlos>>

Hi Carlos:
Add a button to the form and in the onclick event, add these lines.

"TForm1.Button8Click procedure (Sender: TObject);
begin
  prueba.Params.Clear; / / prueba is the name of the DataSet defined in my example
  prueba.Params.Add ('_name =''' +
                     trim (Edit3.Text) + '%' +'''');

  Database.Load (prueba);
end; "

In this case such as parameter passing (_name) (Edit3.Text content), for making queries. This works similar to a SQL query.

And at the DataSet Manager: Row Source, Query set, add the following:

"select * from <table_name> where <row_name>  like {_name = 'DefaultValue'}"

The execution of this SQL statement, replacing 'DefaultValue' by the value passed as a parameter.
This way of working with parameters, you can make the SQL query at runtime.

Check this part of the manual:

http://www.elevatesoft.com/manual?action=viewtopic&id=ewb1&topic=Creating_Loading_DataSets

I hope I was helpful
Alvaro

Hi Carlos,

i try with database ADO and all you descrive work fine for me *with the Ide
EWB*... (NexusDB)
the only difference is "connection string"
and "provider"... i have: "Microsoft OLE DB Provider for ODBC Drivers"


have you tried without EWB ... ?

see my post "ADO DB"

thanks

Alessandro Romano

Hi Alessandro.

No I haven't tried anything else than ADO with MSSQL Server

I don't work with any other Database. Sorry I can't help you any further.

Best regards,

Carlos

Alessandro wrote:

Hi Carlos,

i try with database ADO and all you descrive work fine for me *with the Ide
EWB*... (NexusDB)
the only difference is "connection string"
and "provider"... i have: "Microsoft OLE DB Provider for ODBC Drivers"


have you tried without EWB ... ?

see my post "ADO DB"

thanks

Alessandro Romano

Hi Alessandro

I saw your earlier Post "ADO DB"

First make sure that you have Deploy your App to a Directory for example C:\MyApp\Deployment you do that by going to Project, Options and Hit the Deployment Tab Enter the Path, Click Ok

Then Deploy

Before I was having Problems with Port 80 but now that problem is gone. I think that the problem was SKYPE, not quite sure but since I closed SKYPE I don not get any other Port 80 related errors

This is what I enter on the Internet Explorer Address http://Localhost/myApp.html

If you are successful then whenever you make any changes to your app and want to see the result in Internet Explorer or Google Chrome, you must Compile then Deploy.

Hope this helps

Best regards,

Carlos


This works

Alessandro wrote:

Hi Carlos,

i try with database ADO and all you descrive work fine for me *with the Ide
EWB*... (NexusDB)
the only difference is "connection string"
and "provider"... i have: "Microsoft OLE DB Provider for ODBC Drivers"


have you tried without EWB ... ?

see my post "ADO DB"

thanks

Alessandro Romano

Carlos

<< At design time I can double click on a Dataset, under the Data Set
Manager window panel, and bring the dataset editor. If I click on the Row
Source Tab, Click on the query radio button, then enter any SQL Select
statenments, and on the base table, I can select the Table there.

I would like to do this at run time. Be able to assign SQL Statement to my
table. Can this be done on this later version? How? >>

You can't execute SQL directly from an EWB client application, no.  However,
you can specify parameters for SQL queries, as Alvaro so helpfully noted,
and that is how you can change the WHERE clause of a query (or other parts
of a query) dynamically.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

Carlos,

<< I do appreciate your sample code. It worked very well. Now I have a clear
view on how to create pass though query with parameters thanks to you.
And to clear a little bit more your code and so for others who get the
chance to read the post, and are new to EWB (Elevate Web Builder) could have
a better understanding how this whole thing works here is my sample query
based on your help: >>

That was very helpful for others, thank you.

Tim Young
Elevate Software
www.elevatesoft.com