Solved

asp:DropDownList in GridView is only populating the current value

Posted on 2013-05-23
6
368 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
  • 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now