Solved

asp:DropDownList in GridView is only populating the current value

Posted on 2013-05-23
6
371 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 12

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 12

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 12

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL: Stored Procedure Syntax 3 34
Regarding Disk IO 3 48
Access #Deleted data 20 43
Make an array show the subkey and put it in a query 2 28
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

733 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