Looking to replicate a form / subform relationship in .aspx -> SQLDB as I was able to d

Hello Everyone,

I would think that I'd find a good example of using .NET that would allow me to recreate a .aspx page that would look similar to an Access form with a Subform inside of it. It's very easy to create this in type of form in access, just surprised I haven't found it in the asp.net forums or video's.

My tables have a 1 to many relationship

Table 1 - tblCustomers (Fields = CustomerID, CustomerName, CustomerAddress, etc.)

Table 2 - tblCustomerOrders (Fields = CustomerID, CustomerOrderNumber, ItemOrdered, Price, etc.)

Within my .aspx form, I would like to represent my tblCustomers fields, and the tblCustomerOrders / Subform (as something like an Access Subform Window) within the form.

I'm looking to bring up the Customer, see their info, look at their orders (multiple - as I was able to in an Access Subform), and be able to edit any and all information on the form represented by either table. It's basically an edit form I'm looking for.

Thus, I guess my question is this:

What is the best way to accomplish this within a .aspx page?

Is there a Master -Details way of doing this (haven't seen an example)Is there a control for this that I haven't seenAm I the only one that has looked for this?

The connection I am making now is to a SQL Server holding the database information. The data is no longer in Access. I mentioned Access due to the functionality I was trying to explain.

A link to an article / example or something to search on would be a huge help to me at this point. I'm not beyond a reward for anyone who gives good advice / or great example (s)!

All the best!

Dave

I think the closet you are going to get to this in .net is to use the detailsview control

http://quickstarts.asp.net/QuickStartv20/aspnet/doc/ctrlref/data/detailsview.aspx

The only drawback is when editing data you get a postback, however you could use ajax to stop this

Couple ways to do this dave. For example, what we would like to do is bring up a Customer and see all their orders right? Alrighty.

We're going to need several controls because this is a complicated webform.

I'm going to assume a couple things here, first since you're asking for something this complicated you're not a noobie to ASP.NET. Second, you have the ability to select a single user and pass it to this page via Session or a Query String.

On this page we'll call it CustomerDetails.aspx so create a new WebForm and title it CustomerDetails.aspx

Now, we need to add a few controls. We'll need a GridView to see all the Customer details horizontally. Then we'll need another GridView to see all their orders. And finally we'll need a DetailsView or FormView or Repeater or DataList (I'll use a DetailsView for simplicity) to show all the details of a single order.

Drag all the controls listed above onto the page in design view for ease and we'll manipulate them further via code. Select your first GridView and then click the SmartTag (the little black arrow at the top right of the control) to expand it. Select the Choose a Datasource and select <new datasource>. Follow the wizard (it takes a minute) to setup your first SQL Datasource. Use the query builder to choose a SELECT statement that is:
SELECT * FROM tblCustomers WHERE CustomerID = @.CustomerID

Test the query real quick to make sure it works and you (or I) didn't have any typos and then click finish. Now select your OrdersGridView and repeat the same DataSource setup process but put in another SQL query similar to:
SELECT * FROM tblCustomerOrder WHERE CustomerID = @.CustomerID ORDER BY OrderDate DESC

and Test your query. Then click finish.

Now let's select the DetailsView and do the exact same thing but in the SQL Query we need to modify it so that it shows the details of a single Order.
SELECT * FROM tblCustomerOrder WHERE OrderNumber = @.OrderNumber

Test your query and click finish.

Now here's the nifty part. Goto Code view and look at the code that was generated. Inside GridView1 (that's your CustomerDetails GridView that you can rename) you should add EmptyDataText = "There are no Customers to show" in case something weird happens and a user ends up on your page with no users. Do the same to the other gridview and DetailsView to handle any errors. But now we need to handle the parameters of our SQL statements. For example, in our very first SQLDataSource we have the Parameter @.CustomerID. We need to get that from the query string we passed to this page. So let's do that now.

Inside the <asp:Parameter> tag that is identified as @.CustomerID inside SqlDataSource1 (srcCustomers if you want to rename) you'll see the Name and Type. We need to change this parameter to a QueryString Parameter (this can be done in the Design view but it's just as easy here.) Now just be sure your QueryStringField matches the Query string name that is passed and the Name="CustomerID" (without the @.).

Next, we have the parameter for the second query string. We'll use a ControlParameter for this one. It's pretty striaghtforward. You need to setup ControlName to GridView1 or gdvCustomers whatever it is.

The detailsView parameter is the exact same as the Control Param above. Easy peasy.

Build, check for errors, and then run. And you have it. Now, if you want to be REAL fancy you can add Visible tags to your other controls and only render them when a value is selected. That's a little more complicated than I would explain in type because of the if logic, but it's pretty easy to do if you think about it for a while.

jpuckett, the detailsview control has all of the above built into itl!!

Sorta. The Detailsview can only show a many of one record. If you have a true One to Many, a DetailsView works in the first tier. But most databases in 2.0 still have 'tweener tables that have One to many to Many.

That's why I'm so happy with .NET 3.5 and LINQ. To get rid of the need for 'tweeners. :)