• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 429
  • Last Modified:

Best method to set insert field in Form View

I'm on the asp.net learning curve and would like advice on the best way to achieve the following please..
(1)I have a database table which lists labour rates for customers. It consists of id / customer_id and rate_id.
(2) I've setup a gridview control to list the labour rates by customer
(3) I've added a dropdown to filter the gridview - so we see only one customer's rates at any time.
(4) I added a Form view control to insert a new record, i.e. adding a new rate_id for the chosen customer_id.
I don't want the user to have to worry about selecting a customer when adding a new record as they've already selected the customer in the drop-down - so I'm now unsure how to automatically enter the current customer_id from the drop-down into the insert statement. (My attached code below just contains 2 textboxes on the form for testing.)
(I started looking at assigning the formview customer_id textbox in the onchange event of the dropdown but didn't get very far)
Any guidance appreciated. Thanks
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="admin_customer_rates.aspx.vb" Inherits="admin_customer_rates" 
    MasterPageFile="MasterPage.master" title="Admin:Customer Labour Rates"%>

<asp:Content ContentPlaceHolderId="Main" runat="server">
    <h2>Admin: Customer Labour Rates</h2>
        <asp:Label ID="Label1" runat="server" Text="Select Customer"></asp:Label>
        <asp:DropDownList ID="ddl_customers1" runat="server" AutoPostBack="True DataSourceID="sql_customers" DataTextField="description" DataValueField="id">
        <asp:FormView ID="FormView1" runat="server" 
            DataSourceID="sql_customer_labour_rates" DefaultMode="Insert" Width="196px">
                Add Labour Rate:<asp:TextBox ID="TextBox1" runat="server" 
                    Text='<%# Bind("rate_id") %>'></asp:TextBox>
                &nbsp;Customer id:
                <asp:TextBox ID="txt_customer_id" runat="server">
                <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" 
                    CommandName="Insert" Text="Save" />
                &nbsp;<asp:LinkButton ID="InsertCancelButton" runat="server" 
                    CausesValidation="False" CommandName="Cancel" Text="Cancel" />
    <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
        AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="id" 
            <asp:CommandField ShowDeleteButton="True" />
            <asp:BoundField DataField="customer_id" HeaderText="Customer" 
                SortExpression="customer_id" />
            <asp:BoundField DataField="rate_description" HeaderText="Labour Rate" 
                SortExpression="rate_description" />

    <asp:SqlDataSource ID="sql_customer_labour_rates" runat="server" 
        ConnectionString="<%$ ConnectionStrings:responseConnectionString %>" 
        DeleteCommand="DELETE FROM [customer_labour_rates] WHERE [id] = @original_id AND [customer_id] = @original_customer_id AND [rate_id] = @original_rate_id" 
        InsertCommand="INSERT INTO [customer_labour_rates] ([customer_id], [rate_id]) VALUES (@customer_id, @rate_id)" 
        SelectCommand="SELECT customer_labour_rates.id, customer_labour_rates.customer_id, customer_labour_rates.rate_id, labour_rates.rate_description FROM customer_labour_rates INNER JOIN labour_rates ON customer_labour_rates.rate_id = labour_rates.id WHERE (customer_labour_rates.customer_id = @customer_id) ORDER BY customer_labour_rates.customer_id" 
        UpdateCommand="UPDATE [customer_labour_rates] SET [customer_id] = @customer_id, [rate_id] = @rate_id WHERE [id] = @original_id AND [customer_id] = @original_customer_id AND [rate_id] = @original_rate_id" 
            <asp:Parameter Name="original_id" Type="Int32" />
            <asp:Parameter Name="original_customer_id" Type="Int32" />
            <asp:Parameter Name="original_rate_id" Type="Int32" />
            <asp:Parameter Name="customer_id" Type="Int32" />
            <asp:Parameter Name="rate_id" Type="Int32" />
            <asp:ControlParameter ControlID="ddl_customers1" Name="customer_id" 
                PropertyName="SelectedValue" Type="Int32" />
            <asp:Parameter Name="customer_id" Type="Int32" />
            <asp:Parameter Name="rate_id" Type="Int32" />
            <asp:Parameter Name="original_id" Type="Int32" />
            <asp:Parameter Name="original_customer_id" Type="Int32" />
            <asp:Parameter Name="original_rate_id" Type="Int32" />
    <asp:SqlDataSource ID="sql_customers" runat="server" 
        ConnectionString="<%$ ConnectionStrings:responseConnectionString %>" 
        SelectCommand="SELECT * FROM [customers] ORDER BY [description]">
    <asp:SqlDataSource ID="sql_labour_rates" runat="server" 
        ConnectionString="<%$ ConnectionStrings:responseConnectionString %>" 
        SelectCommand="SELECT [id], [rate_description] FROM [labour_rates] ORDER BY [rate_description]">


Open in new window

1 Solution
If your dropdown list is bound to customer_id, then can't you just use that value in your insert function?
nigelr99Author Commented:
Of course! I think referencing the dropdown control was the major issue but solution as follows:

Protected Sub FormView2_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewInsertEventArgs)
   Dim ctContent As Control
   Dim hourly_rate As Single, rate_description As String

   ctContent = Me.Controls(0).FindControl("Main")
   hourly_rate = CType(FindControlRecursive(ctContent, "ddl_customer_rates"), DropDownList).SelectedValue
   rate_description = CType(FindControlRecursive(ctContent, "ddl_customer_rates"), DropDownList).SelectedItem.ToString

   e.Values("hourly_rate") = hourly_rate
   e.Values("labour_rate_description") = rate_description

End Sub

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now