Using INNER JOIN for fields in GridView control--Enable editing breaks GridView

Visual Studio 2005 used for everything below:

2 tables created--Physician and Practice

Physician 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 page

SQL 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.fname

Lastly, enabled paging, sorting, BUT when i enable editing--the gridview shrinks to zero columns.

I would like the last column, Practice.name, to be read-only text, but a dropdownlist when editing a GridView record (dropdownlist populated by option: Practice.name and value: Practice.id)

How do I accomplish this? I can't seem to make the standard foreign key relationship work.

 Thanks in advance!

0
gr33d
4/13/2009 7:32:36 PM
asp.net.presentation-controls 72751 articles. 3 followers. Follow

9 Replies
2306 Views

Similar Articles

[PageSpeed] 29
Get it on Google Play
Get it on Apple App Store

In order for your edit to work you would have to create a SQL UPDATE statement for the update with any needed parameters.

 In order to get your dropdown in a gridview you would create a template field like this:

 

<asp:TemplateField HeaderText="Group" SortExpression="GroupID">
    <ItemStyle HorizontalAlign="center" />
    <ItemTemplate>
        <asp:Label ID="Label3" runat="server" Text='<%# Bind("FaqGroup.Name") %>' />                                       
    </ItemTemplate>
    <EditItemTemplate>
        <asp:Dropdownlist ID="ddl2" runat="server" DataSourceId="lnqFaqGroups" DataTextField="Name" DataValueField="ID" SelectedValue='<%# Bind("GroupID") %>' />
    </EditItemTemplate>
</asp:TemplateField>
 

 


View Brenden Kehren's profile on LinkedIn
Remember to mark as answer if this post answered your question.
0
b471code3
4/13/2009 9:48:44 PM

 I followed most of that. I have created an UPDATE statement:

UPDATE Physician SET lname = @lname, fname = @fname, mname = @mname, title = @title, practiceID = @practiceID, phone = @phone, address1 = @address1, address2 = @address2, city = @city, state = @state, zip = @zip WHERE (id = @id)

And added the ItemTemplate and EditItemTemplate controls above

 

&lt;asp:TemplateField HeaderText="Name"&gt;
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("Practice.name") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:Dropdownlist ID="ddl2" runat="server" DataSourceId="SqlDataSource1" DataTextField="Name" DataValueField="ID" SelectedValue='<%# Bind("Practice.id") %>' />
</EditItemTemplate>
</asp:TemplateField>
  

however, I do not understand Bind("FaqGroup.Name") and Bind("GroupID")

I tried for the first instance, Bind ("Practice.name") and for the second instance Bind ("Practice.id") but those don't seem to work.

Also, my GridView is still collapsed (as if it were "broken" ? ) when I check the "enable editing" checkbox

Receive bad formatted Bind error...not sure what Bind is really for

I have successfully tested the UPDATE and INSERT queries. Thanks so far!

0
gr33d
4/13/2009 11:23:13 PM

I am using LINQ to SQL as my datasource and FaqGroup is a table which has a FK of ID and a field of Name in it so I can connect to the FaqGroup table by using FaqGroup.Name and display the name in the ddl. 

In your case you can do it in SQL and just set DataTextField = Eval("Name") and DataValueField = Bind("ID"). 

One thing you may want to do to make things easier is put your datasource in with the template field you are using it in.  For instance:

 

<asp:TemplateField HeaderText="Name">  
<ItemTemplate>    
<asp:Label ID="Label3" runat="server" Text='<%# Bind("Practice.name") %>' />                                         
</ItemTemplate>  
<EditItemTemplate>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"  SelectStatment"...... />    
<asp:Dropdownlist ID="ddl2" runat="server" DataSourceId="SqlDataSource1" DataTextField="Name" DataValueField="ID" SelectedValue='<%# Bind("Practice.id") %>' />  
</EditItemTemplate>
</asp:TemplateField>
 
View Brenden Kehren's profile on LinkedIn
Remember to mark as answer if this post answered your question.
0
b471code3
4/14/2009 2:16:02 AM
see following posts...
0
gr33d
4/14/2009 1:47:37 PM

http://www.asp.net/learn/data-access/tutorial-20-vb.aspx

This actually looks exactly like what I'd like to do. I don't follow Step 1: Creating the Appropriate UpdateProduct Overload

What does <System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, False)> mean?

Is this tag generated automatically when you add the method?

 

0
gr33d
4/14/2009 3:07:21 PM

 aha! I have progress!

I have just added 2 fields (Physician.lname and Practice.name) at this point, but now my updates do not work. I click edit, change the DDL or TextBox, click update, no change is saved...

code:

 

1            &lt;asp:GridView ID="GridView1" runat="server"
2                AutoGenerateColumns="False" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display." AllowPaging="True" AllowSorting="True"&gt;
3                <Columns>
4                    <asp:CommandField ShowEditButton="True" />
5                    <asp:TemplateField HeaderText="Practice" SortExpression="name">
6                        <EditItemTemplate>
7                            <asp:DropdownList ID="ddl2" runat="server" DataSourceID="SqlDataSource1" DataTextField="Name" DataValueField="PracticeID" SelectedValue='<%# Bind("PracticeID") %>'></asp:DropdownList>
8                        </EditItemTemplate>
9                        <ItemTemplate>
10                           <asp:Label ID="Label1" runat="server" Text='<%# Bind("name") %>'></asp:Label>
11                       </ItemTemplate>
12                   </asp:TemplateField>
13                   <asp:TemplateField HeaderText="Last Name" SortExpression="lname">
14                       <EditItemTemplate>
15                           <asp:TextBox ID="lname" runat="server" Text='<%# Bind("lname") %>'></asp:TextBox>
16                       </EditItemTemplate>
17                       <ItemTemplate>
18                           <asp:Label ID="Label2" runat="server" Text='<%# Bind("lname") %>'></asp:Label>
19                       </ItemTemplate>
20                   </asp:TemplateField>
21               </Columns>
22           </asp:GridView>
23           <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:mdsc-marketConnectionString1 %>"
24               DeleteCommand="DELETE FROM [Physician] WHERE [PhysicianID] = @PhysicianID" 
25               ProviderName="<%$ ConnectionStrings:mdsc-marketConnectionString1.ProviderName %>"
26               SelectCommand="SELECT Physician.lname, Physician.fname, Physician.mname, Physician.title, Physician.phone, Physician.address1, Physician.address2, Physician.city, Physician.state, Physician.zip, Practice.name, Practice.PracticeID FROM Physician INNER JOIN Practice ON Physician.practiceID = Practice.PracticeID"
27               UpdateCommand="UPDATE [Physician] SET [lname] = @lname, [fname] = @fname, [mname] = @mname, [title] = @title, [practiceID] = @practiceID, [phone] = @phone, [address1] = @address1, [address2] = @address2, [city] = @city, [state] = @state, [zip] = @zip WHERE [PhysicianID] = @PhysicianID">
28               <DeleteParameters>
29                   <asp:Parameter Name="PhysicianID" Type="Int32" />
30               </DeleteParameters>
31               <UpdateParameters>
32                   <asp:Parameter Name="lname" Type="String" />
33                   <asp:Parameter Name="fname" Type="String" />
34                   <asp:Parameter Name="mname" Type="String" />
35                   <asp:Parameter Name="title" Type="String" />
36                   <asp:Parameter Name="practiceID" Type="Int32" />
37                   <asp:Parameter Name="phone" Type="String" />
38                   <asp:Parameter Name="address1" Type="String" />
39                   <asp:Parameter Name="address2" Type="String" />
40                   <asp:Parameter Name="city" Type="String" />
41                   <asp:Parameter Name="state" Type="String" />
42                   <asp:Parameter Name="zip" Type="String" />
43                   <asp:Parameter Name="PhysicianID" Type="Int32" />
44               </UpdateParameters>
45               <InsertParameters>
46                   <asp:Parameter Name="lname" Type="String" />
47                   <asp:Parameter Name="fname" Type="String" />
48                   <asp:Parameter Name="mname" Type="String" />
49                   <asp:Parameter Name="title" Type="String" />
50                   <asp:Parameter Name="practiceID" Type="Int32" />
51                   <asp:Parameter Name="phone" Type="String" />
52                   <asp:Parameter Name="address1" Type="String" />
53                   <asp:Parameter Name="address2" Type="String" />
54                   <asp:Parameter Name="city" Type="String" />
55                   <asp:Parameter Name="state" Type="String" />
56                   <asp:Parameter Name="zip" Type="String" />
57               </InsertParameters>
58           </asp:SqlDataSource>
Thanks so far! Your sample code above has been a huge help! 
0
gr33d
4/14/2009 6:57:39 PM

As I mentioned before you should create a seperate datasource for your nested dropdownlist.  Here is an example and it works just fine for me.  Granted, I don't have nearly as complex update process as you do but it works.  Here is a brief explination of how the DDL works inside the edit template.  Create a datasource that gathers the info from the primary key table.  In my case its the FaqGroups table which has ID and Name.  Set the display (Name) and value (ID) of the ddl to the two fields in the SQL datasource.  Set the selected index property of the ddl to the Foregin Key in the Faqs table which is GroupID.  This way when the row is updated it will update the GroupId with the selectedvalue of the ddl.

So in your case you are looking for the practice name and practice id.  Create a sql datasource with this select statement "SELECT DISTINCT Name, PracticeId FROM Practice ORDER BY Name"  This will get you the list to populate the ddl with.  The set the display and value fields and the selectvalue should be set to your FK field in your Physician table.

 

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:MyConnection %>" 
    DeleteCommand="DELETE FROM [Faq] WHERE [ID] = @ID" 
    InsertCommand="INSERT INTO [Faq] ([Question], [Answer], [GroupID], [CreateDate]) VALUES (@Question, @Answer, @GroupID, @CreateDate)" 
    SelectCommand="SELECT [ID], [Question], [Answer], [GroupID], [CreateDate] FROM [Faq] ORDER BY [Question]" 
    UpdateCommand="UPDATE [Faq] SET [Question] = @Question, [Answer] = @Answer, [GroupID] = @GroupID, [CreateDate] = @CreateDate WHERE [ID] = @ID">
    <DeleteParameters>
        <asp:Parameter Name="ID" Type="Int32" />
    </DeleteParameters>
    <UpdateParameters>
        <asp:Parameter Name="Question" Type="String" />
        <asp:Parameter Name="Answer" Type="String" />
        <asp:Parameter Name="GroupID" Type="Int32" />
        <asp:Parameter Name="CreateDate" Type="DateTime" />
        <asp:Parameter Name="ID" Type="Int32" />
    </UpdateParameters>
    <InsertParameters>
        <asp:Parameter Name="Question" Type="String" />
        <asp:Parameter Name="Answer" Type="String" />
        <asp:Parameter Name="GroupID" Type="Int32" />
        <asp:Parameter Name="CreateDate" Type="DateTime" />
    </InsertParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
    DataKeyNames="ID" DataSourceID="SqlDataSource1">
    <Columns>
        <asp:CommandField ShowEditButton="True" />
        <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" 
            ReadOnly="True" SortExpression="ID" />
        <asp:BoundField DataField="Question" HeaderText="Question" 
            SortExpression="Question" />
        <asp:BoundField DataField="Answer" HeaderText="Answer" 
            SortExpression="Answer" />
        <asp:TemplateField HeaderText="Group" SortExpression="GroupID">
            <EditItemTemplate>
                <asp:DropDownList ID="DropDownList1" runat="server" 
                    DataSourceID="SqlDataSource2" DataTextField="Name" DataValueField="ID" 
                    SelectedValue='<%# Bind("GroupID") %>'>
                </asp:DropDownList>
                <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:MyConnection %>" 
                    SelectCommand="SELECT DISTINCT [Name], [ID] FROM [FaqGroup] ORDER BY [Name]">
                </asp:SqlDataSource>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="Label1" runat="server" Text='<%# Bind("GroupID") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="CreateDate" HeaderText="CreateDate" 
            SortExpression="CreateDate" DataFormatString="{0:d}" />
    </Columns>
</asp:GridView>
 
View Brenden Kehren's profile on LinkedIn
Remember to mark as answer if this post answered your question.
0
b471code3
4/15/2009 2:15:22 PM

 you ROCK. I did add the secondary data source, as I didn't see where you mentioned it above--could've just been a 2am reading ;)

 I also had to add a (declaration?) to the gridview control datakeynames -- whats this all about?

 thank you so much!

0
gr33d
4/15/2009 10:53:02 PM

Here is the technical info about it.

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.datakeynames.aspx

Essentially it is a primary key or group of primary keys used to uniquely identify a row in the gridview and are passed to the datasource when updating and deleting.


View Brenden Kehren's profile on LinkedIn
Remember to mark as answer if this post answered your question.
0
b471code3
4/16/2009 1:25:16 AM
Reply:

Similar Artilces:

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 ...

Trying to use the FAQ " Sorting and paging in the GridView control when not using data source controls "Sorting and paging in the GridView control when not using..."
and I am not getting the results I had hoped for.  I keep getting a "Compiler Error Message: CS0123: No overload for 'AdhocJobGridView_PageIndexChanged' matches delegate 'System.EventHandler'".   Here is my event handling... protected void AdhocJobGridView_PageIndexChanged(object sender, GridViewPageEventArgs e){ try { AdhocJobGridView.DataSource = wsBaseInfoArray; AdhocJobGridView.PageIndex = e.NewPageIndex; AdhocJobGridView.DataBind(); }catch (Exception ex) {throw ex; }   Here is my aspx... <asp:GridView ID="AdhocJobGridView...

using javascript getting value from textbox when edit button is clicked and both controls are present in gridview control
Hi All using javascript I want to get value  from textbox when edit button is clicked and both controls that is texbox and button are present in gridview control .Alok Hi aloksinha83, Please see this post: http://forums.asp.net/t/1069245.aspx Thanks,Qin Dian TangMicrosoft Online Community SupportPlease remember to mark the replies as answers if they help and unmark them if they provide no help....

There is a Gridview Control in the tmplate field of another GridView Control.
    For instance,a gridview displays the information of the  students in a class.A table is recording the information except their mark.There is another table recording the mark of the students whose foreign key is the primary key of the previous table.   string strSQLconnection=".................."; SqlConnection sqlCononection=new SqlConnection(strSQLconnection); SqlCommand sqlCommand=new SqlCommand("select * from stuendent where classID=1",sqlConnection); SqlConnection.Open(); SqlDataReader reader=sqlCommand.ExecuteReader(); GridVie...

gridview control in another gridview control
Hi ia have a questioni have a gridview control which contains button in one colum.now my question is i need another gridview to be placed in the  first gidviewso that when i click on the button the second gridview will populate the data.now my question is how to place second gridview in first gridview.or is there any approach like first grid itself acting as parent and child.please help me  Hi sirikalavalapalli:                I think nested gridview will help you. Check this link and it will show you how...

Can I populate a Gridview outside of the Gridview using my own edit controls ?
I want to be able to populate a GridView control through some edit controls that willbe displayed above my GridView control. Is their a way to do this without using a DetailsView control ? (Refer to the following article) http://www.dotnetbips.com/articles/c1e0ca90-5f5d-47aa-a739-492b562e810a.aspx Or can I have my own edit controls seperately from the GridView control? Actually, all you need to do is to put some textboxes and button. Than on button click insert that data in database and bind data to your gridviewagain. __Please mark post as answer if it helps, if not come back and a...

using controlS to control a gridview
This question is probably posted but i did not bother to look at 10,000+ posts so i am sorry. I've seen the tutorial where he builds a dataset and uses a gridview and controls the gridview with the drop down list box. What i am trying to do is to control the gridview with 2 drop down list boxes. First select the Country you life in and then the State. I've looked at the grid view properties but couldn't find where i can add another parameter. Is it possible to control a grid view (or any form of displaying the information) with more than 1 controller? Thank You.  ...

GridView and Javascript
Hey All, I need help from  you. I have a GridView. In the  Edit Item template of the first Column I have a textbox, an user control and hidden field. When I click on the user control it  opens a pop up from which I can choose the required value. The text value is populated in the textbox and Id in the hidden field. Here's the code for edit template.         <EditItemTemplate>                        <table cellpaddin...

Gridview control embedded in another gridview control
I am writing an ambulance crew scheduling application on the web and I need some help completing the last part.  I have an access database that has three tables: user table, shift table (describing the shifts available), and a schedule table (that stores shift by shift_id and user by user_id).  I have a gridview control that will display the available shifts but I cannot get the embedded gridview control that will display the user that is signed up for each shift to display because I cannot figure out how to use the OnRowDataBound method.  I need a simple VB example of how to...

How to: 2.0 Encrypting Field Data Using a Formview Control and a Gridview control for SQL
The Exercise: Using a Formview Control and a Gridview control to Encrypt data going into SQL, Decrypt it for display, and Be able to Edit it. Using Visual Studio 2005 ASP.NET 2.0 Framework   Files needed: – QSEnc64.vb to be placed in the App_Code folder of your application TestInsertEdit.aspx in the root of the application TestInsertEdit.aspx.vb in the root of the application A Web Config file with a valid ConnectionString Your_SQLConnectionString A SQL Database that you can add the test table to (see script below)   Hopefully this exercise will save you the time it to...

can we use gridview control with in repeater control
HI all,     Can i use gridview control with in repeater control, Any one knows plzzz... let me know. Is there any possibility for this?      It is urgent required... plzzzz... help me..  ------------- ThanksBalu Chandaka  yes you can nest a gridview control in the repeater controlJeev~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~If you get the answer to your question, please mark it as the answer. Hi Jeev,    Thank you for your reply.But how can do that give some idea or If you have any example code snippet then plzzz... let m...

Problem Using Slider Control in Gridview Control
Hello to all,   it is urgent.   i want to use slider control in a Gridview Template Column, if someone can help me how i can get this functionality?...

Issue with getting values from child controls in a gridview, to use for the update using a SQLDataSource control
Hi all, I have a gridview bound with a SQLDataSource. I am using the Update feature of the SQLDataSource to update a SQL Server database with values entered into the gridview. However I am not getting it to work. I believe this is due to the controls that contain the user entries are not the gridview itself, but rather child controls within the gridview. I have been using the names of the actual controls but nothing happens. Upon submit, the screen returns blank, and the database is not updated. Here is some code:   <asp:GridView ID="GridEditSettlement" runat="server" AutoGenerate...

Getting the control id of a control inside gridview control
 uhmm.. a little bit confusing regarding my subject because i dont know how to say it...by the way my problem is how can i get the id of a control (e.g. textbox) inside the gridview... and the textbox is in the templatefield... is there any way i can get its id??  hoping i could get a reply as soon as possible... Regards,MhaeyPlease remember to click “Mark as Answer” on the post that helps you.. =) What do you want's to do by getting the id of the Text box? Do you want's to find particular Textbox with certain id ? Kamran ShahidSr. Software Engineer(MCP,MCAD.net,MC...

Web resources about - Using INNER JOIN for fields in GridView control--Enable editing breaks GridView - asp.net.presentation-controls

Cydia Tweak: PebbleActivator Enables Control Of Your iOS Device From A Pebble Watch
... With InstaChooser , and Sliderz Lets You Customize Text For Various Sliders . Pebble Smartwatch Cydia Tweak: PebbleActivator Enables Control ...

Dish Hopper DVR upgrades enable control over HDMI, expand iPad support
... home-automation support to its DVR, it's rolling out another update that offers considerably more control. The new "Denver" firmware enables ...

Light finds the right mix: Quickly switching orientation of a laser beam enables control over quantum ...
Classical computers process data as a series of ones and zeros known as bits; in contrast, quantum computers encode information into the physical ...

GM to enable control by voice commands
General Motors Co. is going to enable owners of many Chevrolet models to use voice commands for controlling Apple and Android applications on ...

Novel photolithographic technology that enables control over functional shapes of microstructures
Researchers in South Korea have developed a novel photolithographic technology enabling control over the functional shapes of micropatterns using ...

Kwikset finally enables cloud control for its Kevo smart lock
The nifty accessory makes it possible for the Kevo to be controlled and monitored from the cloud.

Google Search update also enables voice controls for UK English
Today's Google Search update may have a few more tricks up its sleeve than we know, but we have found that it enables voice actions for those ...

iPhone OS 3.1 beta enables Voice Control via Bluetooth, more
The latest update to the iPhone OS has been seeded to registered developers. …

Comodo Internet Security 8.1 tightens sandbox controls, enables PUP protection by default
... will now function in Chromium when not part of a block rule, and Microsoft Office will no longer switch to its classic theme when users enable ...

Heat Genius Enables Radiator Control Via Smartphone
... seen. Hopefully word spreads about Heat Genius fast and it becomes available in more markets. Check out Heat Genius Enables Radiator Control ...

Resources last updated: 12/14/2015 5:56:18 AM