Using the gridview to display multiple joined tables, and then allow editing of all columns displayed?

I'm just curious if this is possible.

 

I'm using a SqlDataSource to populate a gridview with data from multiple tables using a inner join select statement.

It would be nice if it were possible to allow a user to click "Edit" and be able to update every single column for the particular row in every single table.

 

Is it possible to do something like this? 


 

0
Sileet
11/18/2006 5:13:59 AM
asp.net.presentation-controls 72751 articles. 3 followers. Follow

13 Replies
1150 Views

Similar Articles

[PageSpeed] 31

Yes this is possible, you just have to use the proper Edit SQL command, that might be a little complex depending on how many DataTables you are editing.
www.someguy.ca rantings from some Canadian guy
Follow me on twitter as well twitter.com/SomeCanadianGuy
0
Rivelyn
11/18/2006 6:21:01 PM
You could also use the gridview_updating event to map the right data to the correct sql update statements.
Feel free to hit me on MSN, I will try to help if I am not super busy.
0
jasonjanofsky
11/19/2006 4:01:34 AM

Yes, you can use many UPDATE commands in the form of T-SQL Queries or Stored procedures.

 


HighOnCoding
Wanna get high!
0
azamsharp
11/19/2006 4:50:11 AM

Rivelyn:
Yes this is possible, you just have to use the proper Edit SQL command, that might be a little complex depending on how many DataTables you are editing.

 

I'm working with three tables but I only need to update two of them. It's three tables because I'm using a composite entity to break up a many to many relationship.

 
I have tblUser and tblSite and a bridge table called tblSU. You can see here, my entire schema: http://206.78.145.9/my_schema.jpg <-- its a IT department database. (I know I didnt use tbl in front of my tables.. but its too late now)

 

I basically want to allow a technician the ability to enter a User (and all their attributes), and assign that user to a Site and a Location within that site all on one .aspx page. After the technician enters the data, it should all display on the gridview. <-- i've gotten this far.

Now I need that proper SQL Edit command so when the technician hits "edit" they can easily edit all columns in the gridview. So if the technician wanted to move the user to a different site, or change their location, they could all on a single page.

So can you help me out with the proper SQL Edit command? I Hope I've given enough information.

Thanks, Big Smile


 

 


 

0
Sileet
11/19/2006 8:50:21 AM

Ok, this actually sounds a bit more normal than you might think.  In all the apps that I have done that have users and sites I do something like this.

 Users Gridview -- you can edit or delete your users here, or select the user at which point the user record is brought into a

User Detailsview -- you can do your fine edits here including every detail you want to add.

 In either case, you simply need a templatefield for your site foreign key.  Put a dropdownlist with the sites list populated on another datasource that brings up your list of sites and bind the selected value of the dropdownlist back to your foreign key.

 

Good Luck

 


Feel free to hit me on MSN, I will try to help if I am not super busy.
0
jasonjanofsky
11/19/2006 4:09:20 PM

Yes, but as far as I've seen, gridview and detalisview only allows a single "update query". And as far as I know, a single update query can only update a single table. Apparently though there are ways to work around this?

I have more than one table I need to update. The users table, and all its attributes. Then the assignment table which assigns the user to the site and location within the site.


 

0
Sileet
11/19/2006 7:54:00 PM
well what you would do if you need to update multiple tables is on the detailsviewupdating event, do your additional updates there using detailsview.findcontrol for the different fields that need to update the other tables.
Feel free to hit me on MSN, I will try to help if I am not super busy.
0
jasonjanofsky
11/19/2006 9:22:13 PM

jasonjanofsky:
well what you would do if you need to update multiple tables is on the detailsviewupdating event, do your additional updates there using detailsview.findcontrol for the different fields that need to update the other tables.


I see. An updating event which triggers when I click "Update"?

So I would have to programatically add update queries within the detailsviewupdating event procedure? <-- If this is correct, can you assist me further because I have no idea how to do this.


 

0
Sileet
11/19/2006 10:45:45 PM

hi! everyone..

I hope you can help me with my problem with table adapters.

I added a new item DataSet and in it I created a new table adapter that can display records from 3 tables sing INNER JOIN.

I followed exactly the tutorials in ASP.net. The only difference is the sql query which uses an INNER JOIN.

When I tested it, it resulted to an error.There is no problem with the sql query (with INNER JOIN) because when I tested it in sql, it worked.

But then again, I do it all over again, and this time I used an ordinary SELECT query from only 1 table. It worked!.

But If I used a query with INNER JOIN, it resulted to an error.

 Why is this so?

 

Sheila


Rock on!!!
0
sheila_rt
11/20/2006 6:22:33 AM
azamsharp:

Yes, you can use many UPDATE commands in the form of T-SQL Queries or Stored procedures.

 

 

Can I use T-SQL queries with a sqldatasource?

0
Sileet
11/20/2006 3:22:30 PM

Thank you for your recommendation of using stored procedures. I finally figured out how to use them.

I was able to sucessfully update two tables at once very easily by passing paramters to a stored procedure.

In the end it was much simpler than I would have thought.

This is how I did it:

First, I created a stored procedure:

ALTER PROCEDURE spUserSite

@User_ID int,

@User_Fname varchar(100),

@User_Lname varchar(100),

@User_Mname varchar(100),

@site_ID int,

@Location varchar(100),

@Note varchar(100)

AS

SET NOCOUNT ON

Update Users

SET User_Fname = @User_Fname, User_Lname = @User_Lname, User_Mname = @User_Mname

Where User_ID = @User_ID

 

Update SU

SET Site_ID = @Site_ID, Location = @Location, Note = @Note

Where User_ID = @User_ID

RETURN

 

Next, I created sqldatasource shown here:

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AESDConnectionString %>"

        SelectCommand="SELECT Users.User_ID, Users.User_Fname, Users.User_Lname, Users.User_Mname, SU.Site_ID, SU.Location, SU.Note FROM Users INNER JOIN SU ON Users.User_ID = SU.User_ID"

        UpdateCommandType="StoredProcedure"

        UpdateCommand="spUserSite">

    </asp:SqlDataSource>

 

Next, I assigned the gridview to this sqldatasource and turn on editing. Make sure to set the datakeynames property on the gridview to the proper primary key. User_ID in this case. If I don’t add the data key names, it doesn’t seem like the gridview passes the User_ID to the stored procedure because it's not an editable field.

 


 

0
Sileet
11/20/2006 6:36:00 PM

Thought I'd add that the word User and User_ID are reserved or something because im having issue using them.

So I changed my table and column names around.
 

0
Sileet
11/20/2006 7:30:45 PM

hi sheila_rt,

could you post your code? It is rather hard to debug w/o knowing what code and error.

0
onepieceking
11/21/2006 9:43:20 AM
Reply:

Similar Artilces:

Display Multiple Datasets in a Single GridView or Use a Timer Control to Display Multple Results
I am trying to get the gridview to display the results of multiple dataset that containg SQL Queries. I have a combobox that allows the user to select a Query to run and then it returns the results to the datagrid, however I can not get it to display multiple results in the same gridview. The following works now to display one along with else if but I need to display all selected at the sametime. I have been advise that this may not be possible, and if it's not can someone direct me on using a Timer Control to display multiple DataSet results in various intervals on the same DataGrid. ...

Display multiple columns in Gridview from One Column of SQL table
Hi I have a table in sql with 2 columns and I want to display that in multiple columns in Gridview. SQL Table No.            Type 1               A1 1               B2 2              A2 2               B2 3        &...

displaying multiple columns in a gridview populated from a single column
hi, i'm a newbie so my apologies in advance if i am not explaining my problem clearly... i'm happy to repost to help clarify anything. Problem: i am not able to sort and organize my record set the way i need to to popluate my gridview. i am looking for suggestion on either how to construct my sql statement, or manipulate my gridview. Environment: i am working in asp.net 2.0 and using a sql server db. Details: I have a single table called Loans. i need to retrieve three columns from that table: LoanOfficer, LoanState, and LoanKey. The LoanState column contains one of three v...

Any examples of Data-Presentation-Controls used to make a tabular report and display it on browser and allow user to print it?
All -- What is a good way to make a report that can be displayed and printed on a browser's web-page? (I am using VWD-2008Express)The requirements are that ...1) user selects one of 10-reports -- each one having certain filter-params (date-ranges, customer-ranges, and the like) -- (I know I can do this with a form)2) user can view the report(s) on a web-page --     (which controls are used to show the columnar-report? -- how to make data-breaks and summary-rows?)3) user can press a PRINT-button that send the report to the user's printer - else use browser's PR...

Display of Data in Gridview from Two Tables using 'LIKE' query and a control text box
Hi!  I am trying to display data in gridview using two tables with innerjoin. A textbox is used to control the results. I need to use 'Like' query to search results.  I am usineing the following code.   SELECT CustomerBrandDetails.BrandName, CustomerIDContact.Name, CustomerIDContact.City, CustomerIDContact.CstID FROM CustomerBrandDetails INNER JOIN CustomerIDContact ON CustomerBrandDetails.CstID = CustomerIDContact.CstID WHERE (CustomerBrandDetails.BrandName =   '%' + @BrandName + '%') If I am using simply "CustomerBrandDetails.BrandN...

Display records from multiple tables in a Datalist by using multiple Datasets
hi everyone, i want to be able to show data from multiple tables in a Datalist control by binding it with mutliple datasets. This should happen at he load event - after the form is loaded i should be able to see the details from various tables.... any help will be very much appreciated. thanks a lot.  Merge the data in the different DataTables into a single DataTable and then bind the resulting combined DataTable to the DataList.ds is a DataSet with tables tablename1 and tablename2DataTable dt; dt = ds.Tables["tablename1"];dt.Merge(ds.Tables["tablename2"]...

Display Multiple Columns from the Database in One Column of a Table in the Form
Hi, I have a table with several columns in the database and I want to display this some of these cols in one column. So for example: I have in the table, NameCustomer, TitleCustomer, and DescriptionCustomer and I want to display this info in one col of a table, just like that: NameCustomer TitleCustomer DescriptionCustomer Those three in one col, I do not know if can use a datagrid or something else, Does any body has a suggestion?  Hi Use a repeater to populate your columns. Thanks  Wonder is the beginning of wisdom[ If my p...

Add new controls to a gridview row; when editing that row using the edit row feature in the gridview control.
Hi all  I have a questions that looks hard / complex to me, and i know you can help me in this. Q {  I have row in a Gridview and i have an edit option to it. I press the edit button, to edit the values in that row and update data all the way back to the database. Now when i press the edit button, i want a drop down list to be displayed that contain values for a particular field in my grid view. (for now only plain text boxes appear.) } I hope you understand my question, all i need to do is replace the text box controls with a dropdown list when i am editing a row in ...

Displaying multiple edit controls
Greetings, I have a module with many controls. My view control is a list of clients. I have one edit control that shows the details of one client. I have another control (could be view or edit I think) that shows a list of addresses for a specific client. When I click on a client in my client list, I want to show both the edit control of the client AND the address list control. Is there a way to do this? I'd rather keep the address list in a separate control than in the client edit control for modularity. Plus, I was also hoping to have the option of putting this address li...

Displaying multiple tables in Gridview
Dear all,  I have a gridview that displays multiple table A, B, C and D. From the gridview, i have linked a details view to see all of the fields in detail. The problem was, when i choose the data source from teh gridview, the enable selection checkbox appears. But when i pull the data into the gridview using a dataset, the enable selection checkbox is not shown on the gridview tasks. How could i make is possible that if table A is displayed on Gridview, the Detailsview has table A or if table B is displayed on Gridview, the Detailsview has table B. I am pullying over my head. please p...

Using INNER JOIN for fields in GridView control--Enable editing breaks GridView
Visual Studio 2005 used for everything below:2 tables created--Physician and PracticePhysician has a field named practiceID (int) which should act as foreign key to Practive.id (both type int, BUT NO relationships created)Dragged Physician table onto new .aspx pageSQL statement: SELECT Physician.id, Physician.lname, Physician.fname, Physician.mname, Physician.title, Physician.phone, Physician.address1, Physician.address2, Physician.city, Physician.state, Physician.zip, Practice.name FROM Physician INNER JOIN Practice ON Physician.practiceID = Practice.id ORDER BY Physician.lname, Physician.f...

How can I display multiple rows w/8 columns (4 columns editable on each row)?
Hello, Please - Can anyone suggest the best way to display a joined dataset with 4 read-only columns, 4 editable columns and a checkbox column? The content for this display currently has < 100 rows - but could approach 1000 in the future. I currently have this setup with a Datagrid (working) - but I would like to be able to edit the 4 columns of ALL rows in the displayed list instead of one row at a time as the Datagrid allows. After editing, I use an update function to traverse the modified list/table and save all values that have the Checkbox clicked. Could a list with 10...

superreview requested: [Bug 333493] Hang with sub - display: table-cell and div
Bernd <bernd_mozilla@gmx.de> has asked Boris Zbarsky <bzbarsky@mit.edu> for superreview: Bug 333493: Hang with sub - display: table-cell and div - display: table-column https://bugzilla.mozilla.org/show_bug.cgi?id=333493 Attachment 219045: patch https://bugzilla.mozilla.org/attachment.cgi?id=219045&action=edit ...

superreview granted: [Bug 333493] Hang with sub - display: table-cell and div
Boris Zbarsky <bzbarsky@mit.edu> has granted Bernd <bernd_mozilla@gmx.de>'s request for superreview: Bug 333493: Hang with sub - display: table-cell and div - display: table-column https://bugzilla.mozilla.org/show_bug.cgi?id=333493 Attachment 219045: patch https://bugzilla.mozilla.org/attachment.cgi?id=219045&action=edit ...

Web resources about - Using the gridview to display multiple joined tables, and then allow editing of all columns displayed? - asp.net.presentation-controls

Wikipedia:File copyright tags/Displayed and ranked - Wikipedia, the free encyclopedia
... images in Wikipedia. In order to more easily locate the best copyright tag for any given image, currently available copyright tags are displayed ...

Facebook to adjust calculations for total check-ins displayed on place pages
... go into effect within the next few weeks, and page owners might notice a difference in their total “were here” tally. Total check-ins are displayed ...

CSS3 : What is the best method for adding 2x images to webpages that will be displayed on the new iPad ...
Answer (1 of 14): WebKit deals in logical pixels and a device pixel ratio to render stuff. It knows nothing about the physical pixels of the ...

QuakeFeed Earthquake Map, Alerts and News - World Earthquakes Displayed on Esri Maps on the App Store ...
Get QuakeFeed Earthquake Map, Alerts and News - World Earthquakes Displayed on Esri Maps on the App Store. See screenshots and ratings, and read ...

Is this not bigotry displayed by Houston, TX 3rd grade teacher Angela Box? - YouTube
There is a controversy brewing over third grade elementary school teacher Angela Box. She teaches at Houston Independent School District (HISD) ...

John Robertson displayed error of judgment over Michael McGurk bribe offer for Currawong, inquiry finds ...
Labor leader John Robertson displayed an &#8220;error of judgment&#8221; by failing to report an attempted $3 million bribe from late businessman ...

Surgeon who cut away child's hip bone displayed 'unsatisfactory professional performance'
... orthopaedic surgeon who removed substantial bone from a teenage girl's misaligned hip, further destabilising it and destroying her joint, displayed ...

Wrong flag displayed before North Korea soccer match halts Games
... international incident when the North Korea women's team refused to take the field for more than an hour after the South Korean flag was displayed ...

MH17 plane fragment to be displayed by Russian museum: reports
A fragment of Malaysia Airlines flight MH17 will be put on display despite investigators seeking all debris to find out why it crashed.

Sydney siege: Flag displayed during Martin Place hostage crisis not same as that used by terrorist group ...
The flag displayed during the siege at a Sydney cafe is not the same one used by the Islamic State terrorist group.

Resources last updated: 12/16/2015 12:00:32 AM