Iron Speed Technical Forums
Register Latest Topics
 
 
 


Reply
  Author   Comment  
Fandango68

Registered:
Posts: 18
Reply with quote  #1 
I think I already know the answer to this, but...

I have a view in my database that pulls columns from three tables. Being a view, it does not have a Primary Key, but it could have. Anyway, I already have a website in IronSpeed that uses a static table.

How do I change the Add, Edit, Show, on my static table to now use the View, and how do I make it work with Edit and Add?

OR, as I alluded earlier, it cannot be done, unless I take the web site out of IronSpeed and into VisualStudio. In other words, I have to actually create a TABLE with the columns from my View and re-create my Add, Edit, Show forms based on the table. In other words, Views are purely for viewing data only?

Thanks in advance.

__________________
----
"There is only one C# note to play"
Dennisr

Registered:
Posts: 13
Reply with quote  #2 
Views based on only one updateable table can act just like the table in IS. They must have a column in the view that will act like a primary key, then manually assign the column as a PK in the database properties of IS.
A view connecting three different tables would be trickier to make work for add, edit, delete. It can be done, but only to one of the three tables in the view, the one the PK is based off of.  With that said there are always a work around. A custom stored procedure would be one.

If you do not want to create a new form for the view, you can drop the view control into your existing table form. I however would not go that route, as it usually ends up taking more time than just creating a new form, built directly from your view. 
-Dennis

__________________
Dennis Retherford
miles

Avatar / Picture

MVP Consultant
Registered:
Posts: 1,679
Reply with quote  #3 
I use views in ISD 12.x ALL the time.  What I do is use the view to present data, and then for the Add and Edit functions I point towards the underlying master table to add and update records.  You will need to over-ride the delete button for the view.  If the view consists of one table, then you may get away with using the ISD Delete function.  But usually I have to over-ride this because my views are often multi-table joins.

Make sure you have defined a virtual primary key (VPK) for your view!  You can also join other tables to your view via Virtual Foreign Keys (VFK) in ISD.  This is helpful when  you want a panel for this view to be displayed as a child table etc..

HTH,

Miles

__________________
Miles Gibson, MScIS
Iron Speed MVP
Senior Consultant, Principal
Milestone Software Inc.

http://www.ironspeedmvp.com\localizer 
Email: miles@milestone.ca
Milestone Localizer: Full Localization for your Iron Speed applications!
Fandango68

Registered:
Posts: 18
Reply with quote  #4 
Quote:
Originally Posted by miles
I use views in ISD 12.x ALL the time.  What I do is use the view to present data, and then for the Add and Edit functions I point towards the underlying master table to add and update records.  You will need to over-ride the delete button for the view.  If the view consists of one table, then you may get away with using the ISD Delete function.  But usually I have to over-ride this because my views are often multi-table joins.

Make sure you have defined a virtual primary key (VPK) for your view!  You can also join other tables to your view via Virtual Foreign Keys (VFK) in ISD.  This is helpful when  you want a panel for this view to be displayed as a child table etc..

HTH,

Miles


Thank you gents

Mile, that's exactly what I have.. a VIEW in my SQL Server database, with links to two other tables, aside from the primary "master" table.

ie

FROM tblBusiness b
LEFT OUTER JOIN tblContacts c ON b.BusinessID = c.BusinessID
RIGHT OUTER JOIN tblActivity a ON a.ContactID = c.ContactID AND a.BusinessID = b.BusinessID

However the primary table should be tblActivity

I think I should write it as ...

FROM tblActivity a
LEFT JOIN tblBusiness b
ON a.BusinessID = b.BusinessID
LEFT JOIN tblContacts c
ON b.BusinessID = c.BusinessID

Never the less, I only want people to see the data from tables 'b' and 'c' as view only, and be able to edit columns from table 'a'.

How do I override the default table 'a' when ISpeed first created the 'Show-TblActivity-Table.aspx' page?

When I click on QUERY, under Properties, I can see the SQL, but I cannot edit it and I cannot DELETE it as the button is greyed out.

Can I delete that page and recreate a new one based on my view, but then how would I set this "virtual primary key", as I cannot set primary keys on the view itself, inside SQL Server.

Sorry, I am very new to Ispeed. I prefer to write things in ASp.net directly, but my boss insisted I use ISpeed.


__________________
----
"There is only one C# note to play"
miles

Avatar / Picture

MVP Consultant
Registered:
Posts: 1,679
Reply with quote  #5 
The idea is to use views to create Show Table pages for presentation only.  Include the view, assign the VPK plus any relevant VFKs etc.  THEN create the Show Table page based upon the view.

Now:  Create separate Add/Edit Record pages based upon one of the tables in the view.  As long as the primary key for that table is included as a column in the view.  Then modify the Add record button to point towards the Add Record page.  Pass along any master PK column value(s) as required.

Do the same for the Edit Row Button.  Change it to point towards the Edit Record Page for the specified table  (different or same as Add Record).  Pass in the column value for the record you want to Edit, plus any other values you need.

The delete will need to be handled manually, as you can't delete one record from a view. I do this in code, over-riding the generated click event.  Essentially handling referential integrity by deleting child records first, then the master record.

HTH,

__________________
Miles Gibson, MScIS
Iron Speed MVP
Senior Consultant, Principal
Milestone Software Inc.

http://www.ironspeedmvp.com\localizer 
Email: miles@milestone.ca
Milestone Localizer: Full Localization for your Iron Speed applications!
pinbot

Avatar / Picture

MVP Consultant
Registered:
Posts: 963
Reply with quote  #6 

What you are doing is pretty common in Iron Speed apps.  The showtable is on a view but then it calls the add/edit pages for the underlying tables.  I recommend to people to create a view for the showtable even if now all the fields are in one table.  Sooner or later you'll want to pull in other fields and as you've noticed there is no way to change the query in Iron Speed.  You have to drop the table and add the view to the page.  Might as well start with a view anyway.


Everything Miles said is correct.  As mentioned the most important thing is to create the Virtual Primary Key in Iron Speed on the view.  If you don't you'll get an error "Record could not be retrieved" when clicking on the edit button.


The only thing I would add is: If you are familiar with database triggers and SQL you can create an "Instead of Delete" trigger on the view.  Then the delete button in Iron Speed will work without modification.  Whichever is easier for you (trigger or overriding the delete button, both work fine).




__________________
C. Bryan Patrick II
Iron Speed Designer MVP Consultant
Pseudo Consulting
Fandango68

Registered:
Posts: 18
Reply with quote  #7 
Well thank you Miles and C.Bryan. Success!
I did a new import of my view using the FROM clauses I mentioned above (the 2nd version), and it's all working!
The view is simply showing me the table version of itself line by line, with edit and delete buttons (have yet to test the delete), but I changed the EDIT to point to another EDIT form based purely on ONE table, as you've suggested.
It works!
I started getting massively confused with the Filters and Search features, but I also figured them out as well, and it's brilliant how IronSpeed has these by default to search for ANY data time in the view I've created. Partial searching etc... it just works! I would have had to spend days writing my own class for Searching if it were not for Ispeed (I already have over 1100 reputations in StackOverFlow because of the old ASP.net days). I still prefer to program in ASP.net and C# personally, as I get more control, but hell for the sake of some simple data entry forms and views of data, Ispeed is fast! I love it.
Thanks again fellas. And Merry Christmas from downunder - Australia. Bloody hot here! [smile]


__________________
----
"There is only one C# note to play"
Previous Topic | Next Topic
Print
Reply

Quick Navigation:

Easily create a Forum Website with Website Toolbox.

Download Iron Speed Designer

Terms of Service Privacy Statement