Update Insert Delete Using Queries

Hi.

I am very new to WebDev 16. Could someone help me please.

Does anyone have a simple Project example I can take apart that they could let me have showing a table that is displayed with add, modify and delete buttons which are based on queries.

I can only find Select in the Tutorial

Thanks
Ian
getwebwise@aol.com

Create a small analysis.

Run 'Full RAD' and select queries from the options available.

This will give you a start for the WD way of using queries however as you get more experience you will find different ways of using this approach.

Hi Ian

The help file stipulates that for insert, modify and delete, the hadd, hmodify, and hdelete methods are the reccomanded ones, while the query is reccomanded when doing needing to return several results at one, and the hreadseek when looking for ONE record

Best regards

Ian,

I can only re-iterate Fabrice; when possible use queries for populating controls such as drop-down lists, table controls etc... and use the WD functions (Hread..., HModify, Hdelete etc...) for handling the single data instances.

For example;
Populate a table control using a query, then when a row is selected from the table control, use a Hread... command to read (and lock if required) the single instance, then Hmodify or Hdelete as appropriate.

Quote
DarrenF
For example;
Populate a table control using a query, then when a row is selected from the table control, use a Hread... command to read (and lock if required) the single instance, then Hmodify or Hdelete as appropriate.

Does this still apply when there is distance/connection issues, like (1) where the server is in Paris and the workstation is in Los Angeles and connection is via IP over the Internet, or (2) the server is in the basement and the workstation on the second floor and the connection is gigabit Cat5e?

Quote
A Bonds

Quote
DarrenF
For example;
Populate a table control using a query, then when a row is selected from the table control, use a Hread... command to read (and lock if required) the single instance, then Hmodify or Hdelete as appropriate.

Does this still apply when there is distance/connection issues, like (1) where the server is in Paris and the workstation is in Los Angeles and connection is via IP over the Internet, or (2) the server is in the basement and the workstation on the second floor and the connection is gigabit Cat5e?

Hi Art, imho yes, because there's only a single record accessed by the said h-commands. Queries, especially server based queries reduce the amount of records sent on a journey over the network compared to filters. Imho, h-commands are ok in all projects, except for filters which tend to slow down things ridiculously. Kind regards, Guenter

Quote
Jimbo

Quote
A Bonds

Quote
DarrenF
For example;
Populate a table control using a query, then when a row is selected from the table control, use a Hread... command to read (and lock if required) the single instance, then Hmodify or Hdelete as appropriate.

Does this still apply when there is distance/connection issues, like (1) where the server is in Paris and the workstation is in Los Angeles and connection is via IP over the Internet, or (2) the server is in the basement and the workstation on the second floor and the connection is gigabit Cat5e?

Hi Art, imho yes, because there's only a single record accessed by the said h-commands. Queries, especially server based queries reduce the amount of records sent on a journey over the network compared to filters. Imho, h-commands are ok in all projects, except for filters which tend to slow down things ridiculously. Kind regards, Guenter

The physical location of the server shouldn't matter... as Guenter confirms in his response. That is unless it's a loaded question Art? ...and you know something different?

I agree - I went down the route of auto-populating table controls and then HFilter(ing) the contents. It soon became apparent that HFilters are very easy to apply but with simplicity, comes a trade off - I just hadn't realised at the outset just what that trade off really was - performance!

The tag line in my signature still holds true :cheers:

I agree with Darren &Guenter.
Hfilter never.
Multi-record or multi-table always use query.
Single record Hread...,hadd,hmodify,hdelete are ok.

The bandwidth can be a serious problem.

Try to limit as mutch as you can the amout of data loaded in tables, combos, ...

If your app is going to work thru the internet forget the RAD way (showing all the record of the file in a table control), i don't know your app but try to show only the relevant data at the opening of the windows and if the user needs more info show it only at request.

Just some tips. You will find a lot more problems along the way but normally they can be solved with some work around.

Thanks all. Nothing loaded in the question Darren...

The team that will be working on the retail information/future ERP system we are writing are all from the Clarion world (we are all www.jda.com veterans). Out of the group I am the only one that can actually spell 'WinDev'... OK, they can spell it --- but I am the only one with any practical experience and even that is not much despite owning and playing with the product since ver 5.5. We would like to get it right from the beginning, it is going to be an interesting ride and the less gotcha's (like "hFilter Never and Queries Forever") and the more we can get great answers from this group the better off we will be.

Appreciate it guys!

Art

Quote
DerekT
Create a small analysis.

Run 'Full RAD' and select queries from the options available.

This will give you a start for the WD way of using queries however as you get more experience you will find different ways of using this approach.

Hi Derek,

I have tried this. I obviously have it wrong. I cannot see where, when using RAD, to Select Queries?

What I am trying to achieve is to display four, maybe five updatable Tables. I have got filter to work up to three but after reading the Help on ..Filter it suggests that Queries are used as they will work where filter does not. What I have in mind is a series of one to many relationships (Maybe five tables on a page) each using three or four records.

Maybe you could expand the steps I need to take to use the Queries Option.

Many Thanks
Ian

Hello Ian

In the RAD options for creating a single window, there is an option to base it on a query, but you must have already created the query.

If you want to do it by hand, you can tackle it in separate steps.

Use the SQL wizard to create a query based on the data to be displayed in one of your tables.
I find It is better to have NOT already created the table, as although you can switch the source of a table between Memory, File and Query, the Windev IDE can get confused and refuse to allow the multi file link process to be set up on the columns

Create a table on your window that has the query as its source and set up any additional links between the table columns and other files.

In the window init or focus gain, set any query parameters as
QueryName.pParam1 = variablename
QueryName.pParam2 = "ABC
QueryName.pParam3 = NULL //this will exclude to condition

Use tabledisplay(tablename,taReExecuteQuery) to fill the table


Regards
Al

Ian
Seems I am living in the past (many have said that)

I have just had a look and found that what I suggested ceased to be available after version 10.
In that version it gave the option the create the app as Standard or MDI with code options for Simplified (code in windows), Procedural (based on queries) or OOP.

I have never used RAD myself but I did find this option a help in discovering the various methods available as defined by PCS.

This option was removed in later versions when the ability to design your own RAD template was introduced - my apologies for not looking before responding.

I would suggest that you get as far as you can with your window and then post specific questions to any issues you cannot resolve.
There are many on here who will be willing to give you help and guidance.

Quote
DerekT
Ian
Seems I am living in the past (many have said that)

I have just had a look and found that what I suggested ceased to be available after version 10.
In that version it gave the option the create the app as Standard or MDI with code options for Simplified (code in windows), Procedural (based on queries) or OOP.

I have never used RAD myself but I did find this option a help in discovering the various methods available as defined by PCS.

This option was removed in later versions when the ability to design your own RAD template was introduced - my apologies for not looking before responding.

I would suggest that you get as far as you can with your window and then post specific questions to any issues you cannot resolve.
There are many on here who will be willing to give you help and guidance.

Hi, thanks for your patience.

Needless to say I am now very confused. I have started again and built a small project which I hope illustrates what I am trying to achieve, I have four tables (The excercise is modified from a 2000 Clarion Getting Started Manual which was built around a room by room Inventory listing - how's that for living in the past!) This project reflects Uni accomodation. 1st Level is a building - second are the rooms in the building - third the people in the rooms - 4th How to contact them - Mobile - Landline - email - Skype etc.

The four tables have embedded SELECT queries. The Buttons have been generated by RAD and with the exception of 'Modify' work. My feeling is that I should be able to apply code to the Server Click embed point of each button to run the relative queries (Insert, Update and Delete) to deal with its table). I don't know how to do that and if I am right, I will have part of my question resolved.

Once I can get that far my next objective is to display the relationships using Queries, filters don't work to four levels. I am guessing this may be a Union Query.

I am pretty good at 'gobbledy guke', and if I am unclear, it will take a minute or so to zip the project up and let you see more clearly where I am tryng to get to.

Many, many thanks
Ian