Solved

asp:DropDownList in GridView is only populating the current value

Posted on 2013-05-23
6
372 Views
Last Modified: 2013-05-28
I have a GridView that's populated from a rather large select statement with multiple joins from a couple different databases. Everything is working well and I can update the editable fields using text boxes but I want to edit some of the fields using dropdownlists populated from one of the joined tables in the select statement. I can choose the value and text attributes of the dropdownlist and when I publish the site is only displays the current value in the dropdownlist and doesnt populate the rest of the items in the joined table. I'm not using any code behind to populate the list, just the existing datasource and the asp.net dropdownlist control.

If I create a separate data source and bind that to the dropdown it populates just fine but I don't know how to edit the value of the gridview column doing it that way.

Am I missing something here? I've tried populating it from code behind and haven't been successful in doing so but even then I'm still not sure how to get it update the value in my current gridview.

Any help appreciated!
0
Comment
Question by:BigDeer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 13

Expert Comment

by:jitendra patil
ID: 39193841
Hi BigDeer,

can you post your aspx and .cs code here.

try the link given below.

how to bind dropdown list in gridview at edit operation from code behind

the above link contains a good example for the problem you are experiencing.

if the above doesn't work, then go with the link below.
how to use ASP.Net DropDownList control in the EditItemTemplate of ASP.Net GridView control

hope this helps.
0
 
LVL 3

Author Comment

by:BigDeer
ID: 39194294
I have nothing in my codebehind that pertains to my gridview. All I have is an insert form that creates new rows and that's in my code behind. I've also just noticed that the delete button does nothing but throw an error with what I have here. I've gotten it to completely delete the entire table or just do nothing but not delete the row.

<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" CellPadding="4" DataSourceID="AssetMgmtView" EnableModelValidation="True" ForeColor="#333333" GridLines="None">
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    <Columns>
        <asp:CommandField ShowEditButton="True" ShowDeleteButton="True" />
        <asp:BoundField DataField="TrackID" HeaderText="TrackID" ReadOnly="True" SortExpression="TrackID" Visible="true" />
        <asp:BoundField DataField="ItemID" HeaderText="ItemID" ReadOnly="True" SortExpression="ItemID" Visible="false" />
        <asp:BoundField DataField="FirstName" HeaderText="FirstName" ReadOnly="True" SortExpression="FirstName" />
        <asp:BoundField DataField="LastName" HeaderText="LastName" ReadOnly="True" SortExpression="LastName" />
        <asp:BoundField DataField="AssetNum" HeaderText="AssetNum" ReadOnly="True" SortExpression="AssetNum" />
        <asp:BoundField DataField="Manufacturer" HeaderText="Manufacturer" ReadOnly="True" SortExpression="Manufacturer" />
        <asp:BoundField DataField="Model" HeaderText="Model" ReadOnly="True" SortExpression="Model" />
        <asp:BoundField DataField="SerialNumber" HeaderText="SerialNumber" ReadOnly="True" SortExpression="SerialNumber" />
        <asp:BoundField DataField="SystemName" HeaderText="SystemName" ReadOnly="True" SortExpression="SystemName" />
        <asp:BoundField DataField="ClientID" HeaderText="ClientID" ReadOnly="True" SortExpression="ClientID" />
        <asp:BoundField DataField="AssetType" HeaderText="AssetType" ReadOnly="True" SortExpression="AssetType" />
        <asp:BoundField DataField="TicketID" HeaderText="TicketID" SortExpression="TicketID" />
        <asp:BoundField DataField="Notes" HeaderText="Notes" SortExpression="Notes" />
        <asp:BoundField DataField="TrackingNum" HeaderText="TrackingNum" SortExpression="TrackingNum" />
        <asp:BoundField DataField="ReasonText" HeaderText="ReasonText" SortExpression="ReasonText" />
        <asp:BoundField DataField="CarrierName" HeaderText="CarrierName" SortExpression="CarrierName" />
        <asp:TemplateField HeaderText="CarrierID" SortExpression="CarrierID">
            <EditItemTemplate>
                <%--<asp:TextBox runat="server" Text='<%# Bind("CarrierID") %>' ID="TextBox1"></asp:TextBox>--%>
                <asp:DropDownList ID="CarrierList" runat="server" DataSourceID="AssetMgmtView" >
                </asp:DropDownList>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label runat="server" Text='<%# Bind("CarrierID") %>' ID="Label1"></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>

        <asp:BoundField DataField="Status" HeaderText="Status" SortExpression="Status" />
    </Columns>
    <EditRowStyle BackColor="#999999" />
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
</asp:GridView>
<asp:SqlDataSource runat="server" ID="AssetMgmtView" 
    ConnectionString="<%$ ConnectionStrings:AssetMgmtConnectionString %>" 
    DeleteCommand="DELETE FROM Tracking
FROM            DATABASE1.dbo.V_NOC_AssetTags AS V_NOC_AssetTags WITH (NOLOCK) INNER JOIN
                         L_Reason INNER JOIN
                         Tracking ON L_Reason.ReasonID = Tracking.ReasonID INNER JOIN
                         L_Carrier ON Tracking.CarrierID = L_Carrier.CarrierID INNER JOIN
                         L_Status ON Tracking.StatusID = L_Status.StatusID INNER JOIN
                         DATABASE1.dbo.V_NOC_UserList AS V_NOC_UserList ON Tracking.UserKey = V_NOC_UserList.UserKey ON 
                         V_NOC_AssetTags.ItemID = Tracking.ItemID
WHERE        (V_NOC_AssetTags.ClientID = 'HOS') AND (V_NOC_AssetTags.AssetStatus = 'ACTIVE') AND (Tracking.TrackID = @TrackID)" 
    InsertCommand="INSERT INTO Tracking(UserKey, ItemID, TrackID, TicketID, StatusID, Notes, CarrierID, TrackingNum, ReasonID) VALUES (@UserKey, @ItemID, @TrackID, @TicketID, @StatusID, @Notes, @CarrierID, @TrackingNum, @ReasonID)" 
    SelectCommand="SELECT V_NOC_AssetTags.ItemID, V_NOC_AssetTags.AssetNum, V_NOC_AssetTags.ClientKey, V_NOC_AssetTags.ClientSite, V_NOC_AssetTags.AssetClass, V_NOC_AssetTags.Location, V_NOC_AssetTags.AssetStatus, V_NOC_AssetTags.Manufacturer, V_NOC_AssetTags.Model, V_NOC_AssetTags.SerialNumber, V_NOC_AssetTags.SystemName, V_NOC_AssetTags.ClientID, V_NOC_AssetTags.CompanyName, V_NOC_AssetTags.SiteName, V_NOC_AssetTags.AssetType, V_NOC_UserList.UserKey, V_NOC_UserList.FirstName, V_NOC_UserList.FullName, V_NOC_UserList.LastName, Tracking.TrackID, Tracking.TicketID, Tracking.Notes, Tracking.TrackingNum, L_Reason.ReasonText, L_Carrier.CarrierName, Tracking.CarrierID, L_Status.Status FROM DATABASE1.dbo.V_NOC_AssetTags AS V_NOC_AssetTags WITH (NOLOCK) INNER JOIN L_Reason INNER JOIN Tracking ON L_Reason.ReasonID = Tracking.ReasonID INNER JOIN L_Carrier ON Tracking.CarrierID = L_Carrier.CarrierID INNER JOIN L_Status ON Tracking.StatusID = L_Status.StatusID INNER JOIN DATABASE1.dbo.V_NOC_UserList AS V_NOC_UserList ON Tracking.UserKey = V_NOC_UserList.UserKey ON V_NOC_AssetTags.ItemID = Tracking.ItemID WHERE (V_NOC_AssetTags.ClientID = 'HOS') AND (V_NOC_AssetTags.AssetStatus = 'ACTIVE') ORDER BY V_NOC_AssetTags.AssetNum"    
    UpdateCommand="UPDATE       Tracking
SET                TicketID = @TicketID, Notes = @Notes, TrackingNum = @TrackingNum, CarrierID = @CarrierID
FROM            DATABASE1.dbo.V_NOC_AssetTags AS V_NOC_AssetTags WITH (NOLOCK) INNER JOIN
                         L_Reason INNER JOIN
                         Tracking ON L_Reason.ReasonID = Tracking.ReasonID INNER JOIN
                         L_Carrier ON Tracking.CarrierID = L_Carrier.CarrierID INNER JOIN
                         L_Status ON Tracking.StatusID = L_Status.StatusID INNER JOIN
                         DATABASE1.dbo.V_NOC_UserList AS V_NOC_UserList ON Tracking.UserKey = V_NOC_UserList.UserKey ON 
                         V_NOC_AssetTags.ItemID = Tracking.ItemID
WHERE        (V_NOC_AssetTags.ClientID = 'HOS') AND (V_NOC_AssetTags.AssetStatus = 'ACTIVE')"
    >
    <DeleteParameters>
        <asp:Parameter Name="TrackID" />
    </DeleteParameters>
    <InsertParameters>
        <asp:Parameter Name="UserKey" />
        <asp:Parameter Name="ItemID" />
        <asp:Parameter Name="TrackID" />
        <asp:Parameter Name="TicketID" />
        <asp:Parameter Name="StatusID" />
        <asp:Parameter Name="Notes" />
        <asp:Parameter Name="CarrierID" />
        <asp:Parameter Name="TrackingNum" />
        <asp:Parameter Name="ReasonID" />
    </InsertParameters>
    <UpdateParameters>
        <asp:Parameter Name="TicketID" />
        <asp:Parameter Name="Notes" />
        <asp:Parameter Name="TrackingNum" />
        <asp:Parameter Name="CarrierID" />
    </UpdateParameters>
</asp:SqlDataSource>

Open in new window

0
 
LVL 13

Accepted Solution

by:
jitendra patil earned 500 total points
ID: 39194412
The problem in your code is you are binding the dropdownlist with  a sqldatasource
which is kept outside of gridview.

as you need your dropdownlist to be binded in edit mode you need to keep your sqldatasource in the edittemplate along with your dropdownlist

for example
<EditItemTemplate>
                                                                    <asp:DropDownList ID="Segment" runat="server" DataSourceID="dsSegment" AppendDataBoundItems="True"
                                                                        DataTextField="SegmentName" DataValueField="SegmentName" SelectedValue='<%# Bind("Segment") %>'
                                                                        Width="80px" />
                                                                    <asp:SqlDataSource ID="dsSegment" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
                                                                        SelectCommand="SELECT * FROM [pb_Segment] ORDER BY [SegmentName]"></asp:SqlDataSource>
                                                                </EditItemTemplate>

Open in new window


secondly you need to specify the datatextfield and datavaluefield value of dropdownlist
as shown in above example.

for your delete code check the delete query, you have used From 2 times in the query

 DeleteCommand="DELETE FROM Tracking
FROM            DATABASE1.dbo.V_NOC_AssetTags AS V_NOC_AssetTags WITH (NOLOCK) INNER JOIN
                         L_Reason INNER JOIN
                         Tracking ON L_Reason.ReasonID = Tracking.ReasonID INNER JOIN
                         L_Carrier ON Tracking.CarrierID = L_Carrier.CarrierID INNER JOIN
                         L_Status ON Tracking.StatusID = L_Status.StatusID INNER JOIN
                         DATABASE1.dbo.V_NOC_UserList AS V_NOC_UserList ON Tracking.UserKey = V_NOC_UserList.UserKey ON
                         V_NOC_AssetTags.ItemID = Tracking.ItemID

modify your query and try for deleting the records.

hope this helps.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 3

Author Comment

by:BigDeer
ID: 39194942
Awesome. Updating is working perfectly now but my deletes are still just throwing errors. Removed the FROM  DATABASE1.dbo.V_NOC_AssetTags AS V_NO ... and just left it as:

DELETE FROM Tracking WHERE Tracking.TrackID = @TrackID
0
 
LVL 13

Expert Comment

by:jitendra patil
ID: 39196245
What kind of error you are getting, please post the error msg.

From your delete query i think you don't need to specify tablename.columnname.

Your delete query should be "Delete from Tracking where TrackId=@TrackID.

if you want you can also delete the records from backend by defining the gridview row deleting event as belows

aspx code.

just add the below line of code in your gridview tag

onrowdeleting="gvDetails_RowDeleting"

create a new item template inside of columns tag within gridview.

</ItemTemplate>
<asp:ImageButton ID="imgbtnDelete"CommandName="Delete"Text="Edit"runat="server"ImageUrl="~/Images/delete.jpg"ToolTip="Delete"Height="20px"Width="20px"/>
 
</ItemTemplate>
here i have used image button you can also use link button for the purpose.

.cs code
protected void gvDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
 
{
 
int TrackID = Convert.ToInt32(GridView2.DataKeys[e.RowIndex].Values["TrackID"].ToString());
 
con.Open();
 
SqlCommand cmd = new SqlCommand("delete from Tracking where TrackID=" + TrackID, con);
 
int result = cmd.ExecuteNonQuery();
 
con.Close();
 
if (result == 1)
 
{
 
// write the code for rebinding your gridview.
 
lblresult.ForeColor = Color.Red;
 
lblresult.Text = username + " details deleted successfully";
 
}
 
}

hope this helps.
0
 
LVL 3

Author Closing Comment

by:BigDeer
ID: 39202167
Thanks, I was able to get it working with this suggestion and I rebuilt my delete query with Query Designer and it's working properly now.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question