as the title states I am currently having trouble populating a drop down list that is contained within a gridview. At first glance this seems like a relatively simple problem and I have populated many gridviews and many drop down lists in the past, however this one is being a real pain. I am relatively new to software development so any help regarding this issue regardless of simplicity would be greatly appreciated. Below I have detailed the problem more explicitly and copied any relevant code, if there is anything I have missed I would be more than happy to supply it.
As stated before I have a drop down list (ddlPartEquipmentNew) within a gridview control (GridView3). So far there is one item in the ddl "---SELECT---" that I added as a ListItem in the aspx page. I am trying to populate the gridview with data from my database that corresponds to a value that is currently stored in a variable on the page. I have tested my query and can confirm that it returns the appropriate values in SSMS and it looks like
SELECT EquipmentType FROM Equipment WHERE Agreement = @Agreement
Below I have posted The aspx code of the gridview and drop down.
<asp:GridView ID="GridView3" runat="server"
EmptyDataText="No Claimed Parts" AutoGenerateColumns="False"
ShowHeaderWhenEmpty="True" DataKeyNames="RecID"
ShowFooter="True" DataSourceID="SqlDataSource5" Width="95%">
<EmptyDataTemplate>
<asp:DropDownList ID="ddlPartEquipmentNew" runat="server" DataSourceID="SqlDataSourcePartEquipment" DataValueField="EquipmentType" DataTextField="EquipmentType"
AppendDataBoundItems="True" Width="270px" Height="20px" Style="margin-left: 70px;">
<asp:ListItem Value="0">---SELECT---</asp:ListItem>
</asp:DropDownList>
<asp:DropDownList ID="ddlPartNew" runat="server" DataSourceID="SqlDataSourcePart" DataValueField="RecID"
DataTextField="description" AppendDataBoundItems="True" Width="270px" Height="20px"
style="margin-left: 70px; margin-right: 110px">
<asp:ListItem Value="0">---SELECT---</asp:ListItem>
</asp:DropDownList>
<asp:textbox ID="txtUnitPriceNew" runat="server" Width="95px"/>
<asp:textbox ID="txtTaxNew" runat="server" Width="95px" text="0"/>
<asp:Button ID="InsertDetail" runat="server" CommandName="InsertDetail" Height="25px" Text="Add Detail" Width="85px" />
</EmptyDataTemplate>
<AlternatingRowStyle BackColor="#CCCCCC" />
<Columns>
<asp:CommandField ShowEditButton="True" footertext="Add -->" ShowDeleteButton="True" HeaderStyle-Width="70px"/>
<asp:BoundField DataField="RecID" HeaderText="RecID" SortExpression="RecID" ReadOnly="True" Visible="False" />
<asp:TemplateField HeaderText="Parts Description" ItemStyle-HorizontalAlign="center">
<ItemTemplate>
<asp:label ID="lblDescriptionAdd" Text='<%# Bind("PartFailed") %>' runat="server"/>
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList ID="ddlPartEquipmentEdit" runat="server" DataValueField="Agreement" DataTextField="EquipmentType"
AppendDataBoundItems="True" Width="270px" Height="20px" Style="margin-left: 70px;">
<asp:ListItem Value="0">---SELECT---</asp:ListItem>
</asp:DropDownList>
<asp:DropDownList ID="ddlPartEdit" runat="server" DataSourceID="SqlDataSourcePart" DataValueField="RecID" DataTextField="description" SelectedValue='<%# Eval("RepairID")%>' AppendDataBoundItems="True" Width="270px"/>
</EditItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="ddlPartEquipmentInsert" runat="server" DataValueField="Agreement" DataTextField="EquipmentType"
AppendDataBoundItems="True" Width="270px" Height="20px" Style="margin-left: 70px;">
<asp:ListItem Value="0">---SELECT---</asp:ListItem>
</asp:DropDownList>
<asp:DropDownList ID="ddlPartInsert" runat="server" DataSourceID="SqlDataSourcePart" DataValueField="RecID" DataTextField="description" AppendDataBoundItems="True" Width="270px">
<asp:ListItem Value="0">---SELECT---</asp:ListItem>
</asp:DropDownList>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Part Cost Requested" ItemStyle-HorizontalAlign="center" HeaderStyle-Width="100px">
<ItemTemplate>
<asp:label ID="lblUnitPrice" Text='<%# Bind("PartCost", "{0:C}") %>' runat="server" Enabled="False"/>
</ItemTemplate>
<EditItemTemplate>
<asp:textbox ID="txtUnitPriceEdit" Text='<%# Bind("PartCost") %>' Enabled="true" runat="server"/>
</EditItemTemplate>
<FooterTemplate>
<asp:textbox ID="txtUnitPriceInsert" Text='<%# Bind("PartCost") %>' runat="server" style="width: 100%; box-sizing: border-box;"/>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Tax" ItemStyle-HorizontalAlign="Center" HeaderStyle-Width="100px">
<ItemTemplate>
<asp:LinkButton ID="lbTaxTotal" Text='<%# Bind("Tax", "{0:C}")%>' runat="server" CommandArgument="Part" OnClick="lblTaxTotal_Click"/>
<asp:HiddenField ID="hidPartGST" runat="server" Value='<%# Bind("GST")%>' />
<asp:HiddenField ID="hidPartPST" runat="server" Value='<%# Bind("PST")%>' />
<asp:HiddenField ID="hidPartQST" runat="server" Value='<%# Bind("QST")%>' />
<asp:HiddenField ID="hidPartHST" runat="server" Value='<%# Bind("HST")%>' />
</ItemTemplate>
<EditItemTemplate>
<asp:textbox ID="lblTaxTotalEdit" Text='<%# Bind("Tax")%>' Enabled="true" runat="server"/>
</EditItemTemplate>
<FooterTemplate>
<asp:textbox ID="txtTaxTotalInsert" Text="0" runat="server" Enabled="true" style="width: 100%; box-sizing: border-box;"/>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Insert New" HeaderStyle-Width="85px">
<ItemTemplate>
<asp:Label ID="lblEmpty" Text="" runat="server" Width="75px"/>
</ItemTemplate>
<FooterTemplate>
<asp:Button ID="Insert" runat="server" CommandName="InsertNewDetail" Height="22px" Text="Insert" style="width: 100%; box-sizing: border-box;" />
</FooterTemplate>
</asp:TemplateField>
</Columns>
<HeaderStyle BackColor="#336699" ForeColor="White" />
</asp:GridView>
In order to populate the drop down list I have associated it with an asp:SqlDataSource control below.
<asp:SqlDataSource ID="SqlDataSourcePartEquipment" runat="server" ConnectionString="<%$ ConnectionStrings:WarrantyConnectionString %>"
SelectCommand="SELECT EquipmentType FROM Equipment WHERE Agreement = @Agreement">
</asp:SqlDataSource>
In the vb.net code behind I have declared the parameter in the page load event as seen below
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Decrypt et get params
MaintainScrollPositionOnPostBack = True
hashtableParams = RedirectionHandler.EncryptedRequest(Request.QueryString("data"))
'SqlDataSourcePartEquipment.SelectParameters.Add("@Agreement", hashtableParams("agreementNumber"))
'If Page.IsPostBack Then
'Dim wcICausedPostBack As WebControl = CType(GetControlThatCausedPostBack(TryCast(sender, Page)), WebControl)
'Dim indx As Integer = wcICausedPostBack.TabIndex
'Dim ctrl = _
'From control In wcICausedPostBack.Parent.Controls.OfType(Of WebControl)() _
'Where control.TabIndex > indx _
'Select control
'ctrl.DefaultIfEmpty(wcICausedPostBack).First().Focus()
'End If
'Dim SClaimID As String = CType(Session.Item("SClaimID"), String)
If Not IsPostBack Then
Dim agreement As String = hashtableParams("agreementNumber")
Dim claimID As String
If hashtableParams.Count > 0 AndAlso Not IsNothing(hashtableParams("claimID")) Then
claimID = hashtableParams("claimID")
Session("ClaimID") = claimID
Else
claimID = CType(Session.Item("ClaimID"), String)
End If
Me.txtAgreement.Text = agreement
Me.txtOtherInvoice.Text = "I'm Working"
If Not String.IsNullOrEmpty(claimID) Then
objCn.Open()
objSelectCmd.Connection = objCn
objSelectCmd.CommandText = "Select * From Claim Where ClaimNumber = @ClaimID"
objSelectCmd.Parameters.Add("ClaimID", SqlDbType.Int).Value = claimID
objSelectCmd.Connection = objCn
objDa.SelectCommand = objSelectCmd
objDa.Fill(objDs, "Claim")
objDataTable = objDs.Tables("Claim")
objCurrentRow = objDataTable.Rows(0)
Me.txtAgreement.Text = objCurrentRow("Agreement").ToString
Me.txtServicerNumber.Text = objCurrentRow("AccountNumber").ToString
'Me.txtCNumber.Text = objCurrentRow("AccountNumber").ToString
Me.lblClaimNumber.Text = objCurrentRow("ClaimNumber").ToString
lblCreationDate.Text = Convert.ToDateTime(objCurrentRow("CreateDate").ToString).ToShortDateString
Me.txtProblem.Text = objCurrentRow("ProblemDescription").ToString
Me.txtSubmitDate.Text = Convert.ToDateTime(objCurrentRow("ReceivedDate").ToString).ToShortDateString
Me.txtWorkPerformed.Text = objCurrentRow("WorkPerformed").ToString
Me.txtServiceDate.Text = Convert.ToDateTime(objCurrentRow("FailDate").ToString).ToShortDateString
Me.txtWorkInvoice.Text = objCurrentRow("WorkInvoice").ToString
Me.txtOtherInvoice.Text = objCurrentRow("OtherInvoice").ToString
Me.lblClaimStatus.Text = objCurrentRow("Status").ToString
SqlDataSourcePartEquipment.SelectParameters.Add("@Agreement", hashtableParams(agreementNumber))
(There is much more code in the Page_load() event, and if required I can supply it) When I execute this code and navigate to the web form in question I receive an exception error "Must declare the scalar variable "@Agreement". To my knowledge I am declaring the @Agreement variable in the last line of what I posted of the Page_Load event. I have also used the "agreement" variable seen earlier in the page load event and when I use that, I don't receive the exception error but my drop down still isn't populated.
Whats confusing me so much is that every variable or control that stores the value I need for my parameter either populates nothing to my drop down or I receive the exception mentioned above.
If anyone has an idea as to why this could be happening I would greatly appreciate some input here as I am at a loss as to what to do.
CodePudding user response：
Ok, you have a lot of moving parts.
But, how to setup a combo box in a gv?
Say this simple markup:
<asp:GridView ID="GHotels" runat="server" AutoGenerateColumns="False"
DataKeyNames="ID" CssClass="table table-striped" Width="50%">
<Columns>
<asp:BoundField DataField="FirstName" HeaderText="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="LastName" />
<asp:BoundField DataField="HotelName" HeaderText="HotelName" />
<asp:BoundField DataField="Description" HeaderText="Description" />
<asp:TemplateField HeaderText="Rate Hotel">
<ItemTemplate>
<asp:DropDownList ID="cboRank" runat="server"
DataValueField="ID"
DataTextField="Rating" >
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:Button ID="cmdSave" runat="server" Text="Save Changes" CssClass="btn"
OnClick="cmdSave_Click" />
<asp:Button ID="cmdAddNew" runat="server" Text="New Hotel" CssClass="btn"
style="margin-left:20px" />
And code to load this up is this:
Dim rstRank As New DataTable ' to load cbo box
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
LoadData()
End If
End Sub
Sub LoadData()
' load combo box data
rstRank = MyRst("SELECT ID, Rating from tblRating ORDER BY ID")
' load gv
Dim strSQL As String =
"SELECT * FROM tblHotelsA ORDER BY HotelName"
GHotels.DataSource = MyRst(strSQL)
GHotels.DataBind()
End Sub
Function MyRst(strSQL As String) As DataTable
Dim rst As New DataTable
Using conn As New SqlConnection(My.Settings.TEST4)
Using cmdSQL As New SqlCommand(strSQL, conn)
conn.Open()
rst.Load(cmdSQL.ExecuteReader)
End Using
End Using
Return rst
End Function
And we get this:
The only extra part? We need to load up each row combo, and ALSO set the value from the data. We use the row data bound event, and this code:
Protected Sub GHotels_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles GHotels.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then
' load combo box with choices
Dim cboRank As DropDownList = e.Row.FindControl("cboRank")
cboRank.DataSource = rstRank
cboRank.DataBind()
' add blank row "select"
cboRank.Items.Insert(0, New ListItem("Select", "0"))
' NOW set combo to current choice in database
Dim rowData As DataRowView = e.Row.DataItem
If Not IsDBNull(rowData("Rating")) Then
cboRank.SelectedValue = rowData("Rating")
End If
End If
So, in a nutshell, the above is the basic approch. However, you have a boatload of markup, and seem to have the 2 combo boxes repeated at least 3 times. You need to VAST VAST reduce the amount of markup you have. You with a somewhat different approach reduce that markup to what I have above - and have editing. But, I would not use edit templates, and I would not even use a whole new alternatiing template for JUST display of bands (note how I used the style class = table to make the grid look nice, and added table-striped to alternate the bands). This is a bootstrap class - and by default most asp.net sites do have bootstrap installed.
If you need editing, then I would suggest a clean and simple layout using listview in place of grid view. it tends to mean VAST less markup, and is less convoluted.
And for each asp.net control, you don't have to wrap it around a template.
So, to allow editing of the data above, and a simple save to save all edits. Then I would use this (a list view).
<style>
.borderhide input {border:none}
.borderhide textarea {border:none}
</style>
<asp:ListView ID="ListView1" runat="server" DataKeyNames="ID">
<ItemTemplate>
<tr style="">
<td><asp:Textbox ID="txtFirst" runat="server" Text='<%# Eval("FirstName") %>' Width="100px"/></td>
<td><asp:TextBox ID="txtLast" runat="server" Text='<%# Eval("LastName") %>' Width="100px" /></td>
<td><asp:TextBox ID="CityLabel" runat="server" Text='<%# Eval("City") %>' Width="100px" /></td>
<td><asp:TextBox ID="txtHotelName" runat="server" Text='<%# Eval("HotelName") %>' Width="100px" /></td>
<td>
<asp:TextBox ID="txtDescription" runat="server" Text='<%# Eval("Description") %>'
TextMode="MultiLine" Columns="60" /></td>
<td>
<asp:DropDownList ID="cboRank" runat="server"
DataValueField="ID"
DataTextField="Rating" >
</asp:DropDownList>
</td>
</tr>
</ItemTemplate>
<LayoutTemplate>
<table id="itemPlaceholderContainer" runat="server" >
<tr runat="server" style="">
<th runat="server">First Name</th>
<th runat="server">Last Name</th>
<th runat="server">City</th>
<th runat="server">Hotel Name</th>
<th runat="server">Description</th>
<th runat="server">Rating</th>
</tr>
<tr id="itemPlaceholder" runat="server">
</tr>
</table>
</LayoutTemplate>
</asp:ListView>
<asp:Button ID="cmdSave" runat="server" Text="Save Changes" CssClass="btn"
OnClick="cmdSave_Click" />
<asp:Button ID="cmdAddNew" runat="server" Text="New Hotel" CssClass="btn"
style="margin-left:20px" />
But with above, I can have ONE easy save button - saves all edits. No messy edit template, no messy itemtemplates for each row. No noting. And the above allows editing of all rows - one simple save button sends all changes to the database.
so, as listview, we get this:
I can post the save code, but this question was about setting up a combo box.
But, to "save" all and any edits, the above markup remains "as is", and we save boatloads of markup, and boatloads of code behind here.
So the ONE set of markup allows:
Display of data
Editing of data
Save of data
Now, I did original use the wizard and the datasouce it puts on the page. I then just went on a delete hunt, and removed all the templates - only need itemtemplate and I am done for display, and editing of the data.