So I have a GridView that display loaning information, my Loans table has the ID for Laptop, User, Location, etc.
When I display on the GridView, instead I use names instead of ID numbers.
My problem is when I put a dropdownlist in the edititemtemplate such as below it does not update my table and I get the below error I want to be able to update my loans table with the appropriate info:
Cannot insert the value NULL into column 'Laptop_ID', table 'itassetmgmt.dbo.Loans'; column does not allow nulls. UPDATE fails. The statement has been terminated.
Loans Table include: Loan_ID Date_Loaned Date_Returned Sign_Off Laptop_ID User_ID Dept_ID Location_ID
ASPX:
<asp:TemplateField HeaderText="Laptop_Name" SortExpression="Laptop_Name">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList1" SelectedValue ='<%# Bind("Laptop_Name") %>' runat="server" DataSourceID="editLPID" DataTextField="Laptop_Name" DataValueField="Laptop_Name">
</asp:DropDownList>
<asp:SqlDataSource ID="editLPID" runat="server" ConnectionString="<%$ ConnectionStrings:itassetmgmtConnectionString1 %>" SelectCommand="SELECT [Laptop_ID], [Laptop_Name] FROM [Laptops]"></asp:SqlDataSource>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("Laptop_Name") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name" SortExpression="Name">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList2" runat="server" SelectedValue='<%# Bind("Name") %>' DataSourceID="editUID" DataTextField="Name" DataValueField="Name">
</asp:DropDownList>
<asp:SqlDataSource ID="editUID" runat="server" ConnectionString="<%$ ConnectionStrings:itassetmgmtConnectionString1 %>" SelectCommand="SELECT User_ID, Firstname +' '+ Lastname AS Name FROM Users"></asp:SqlDataSource>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("Name") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Department" SortExpression="Department">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList3" runat="server" SelectedValue='<%# Bind("Department") %>' DataSourceID="editDID" DataTextField="Department" DataValueField="Department">
</asp:DropDownList>
<asp:SqlDataSource ID="editDID" runat="server" ConnectionString="<%$ ConnectionStrings:itassetmgmtConnectionString1 %>" SelectCommand="SELECT * FROM [Departments]"></asp:SqlDataSource>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("Department") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Location_Name" SortExpression="Location_Name">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList4" runat="server" SelectedValue='<%# Bind("Location_Name") %>' DataSourceID="editLID" DataTextField="Location_Name" DataValueField="Location_Name">
</asp:DropDownList>
<asp:SqlDataSource ID="editLID" runat="server" ConnectionString="<%$ ConnectionStrings:itassetmgmtConnectionString1 %>" SelectCommand="SELECT * FROM [Locations]"></asp:SqlDataSource>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("Location_Name") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
EDIT: I know that it's probably because the DataValueField for the DropDownList isn't set to the corresponding ID# in the table, but when I change the DataValueField, it gives me a bunch of errors. Any suggestions?
My Update Command: UpdateCommand="UPDATE [Loans] SET [Date_Loaned] = @Date_Loaned, [Date_Returned] = @Date_Returned, [Sign_Off] = @Sign_Off, [Laptop_ID] = @Laptop_ID, [User_ID] = @User_ID, [Dept_ID] = @Dept_ID, [Location_ID] = @Location_ID WHERE [Loan_ID] = @original_Loan_ID"
In the Gridview take out all ID's from the displayable portion of the grid. No need to display them, however they must be part of the gridview datasource in order to bind against them
Then in the ItemTemplate (I'm only showing the user template) DataTextField and DataValueField come from the DropDownList datasource BUT the SelectedValue property is bound against the Gridview User_ID :
<asp:TemplateField>
<ItemTemplate>
<asp:DropDownList ID="DropDownList2Disabled" runat="server" DataSourceID="SqlDataSource3"
Enabled="False"
DataTextField="Name"
DataValueField="User_ID"
SelectedValue='<%# Eval("User_ID") %>'>
</asp:DropDownList>
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource3"
DataTextField="Name"
DataValueField="User_ID"
SelectedValue='<%# Bind("User_ID") %>'>
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateField>
I'm using a DDL in both the ItemTemplate and EditItemTemplate, disabled in one and enabled in the other. you can use a label if you wish in the item table but that would require a bit more work as the username is not part of the gridview.
If you have control of the gridview sql and can add the username to the dataset (say as UserName) then you could bind an ItemTemplate label to the UserName and the EditItemTemplate DropDownList to the User_ID
EDIT: Expanded Example:
So, Going with the Label idea the template would look something like this:
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="LabelUserName" runat="server" Text='<%# Eval("UserName") %>'>
</asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource3"
DataTextField="Name"
DataValueField="User_ID"
SelectedValue='<%# Bind("User_ID") %>'>
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateField>
And the Gridview DataSource Select(or Stored Proc) would look (or contain) something like this:
Select Loan_ID
, l.Date_Loaned
, l.Date_Returned
, l.Sign_Off
, l.Laptop_ID
, l.User_ID
, u.FirstName + ' ' + u.LastName as UserName
, l.Dept_ID
, d.DepartmentName
, l.Location_ID
, (etc...)
From LoansTable l
Left Join UserTable u on u.User_ID = l.User_ID
Left Join DeptTable d on d.Dept_ID = l.Dept_ID
(etc.)
Order By d.DepartmentName
, l.Date_Loaned
So this would return a fieldset that provides the Gridview with enough User Friendly Data for a clean display and also the ID's that you, the programmer, may require to make changes as the user interacts with the Gridview.
Now the ID's may or may not be added to the GridView by adding them to the DataKeyNames property. This would allow for easy access to the ID's in the code behind during GridView Postbacks. Keys placed here are not part of the displayable portion of the GridView
The DDL in the EditItemTemplate has it's own DataSource and has a simple Select:
Select User_ID, FirstName + ' ' + LastName as Name From UserTable
Do not confuse duplicate Fields from 2 different DataSources. In the EditItemTemplate the DDL Declaration:
<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource3"
DataTextField="Name"
DataValueField="User_ID"
SelectedValue='<%# Bind("User_ID") %>'>
</asp:DropDownList>
DataValueField="User_ID" refers to the DDL DataSource whereas SelectedValue='<%# Bind("User_ID") %>' is a Binding to the User_ID field in the GridView DataSource
Here the Gridview User_ID field is "hidden" from the user but available to the Gridview for editing purposes and conditionally available to you programmatically if you placed it in DataKeyNames