Solved

asp:DropDownList in GridView is only populating the current value

Posted on 2013-05-23
6
370 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

856 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