CODEWITHSUNDEEP
c#asp.netsql-server
user3339242
user3339242

Reputation: 641

How to use insert parameter with textbox text in listview

I am trying to use my SqlDataSource to enter items into the database with values from a TextBox, however nothing is inserting.

Here is my SqlDataSource:

<asp:SqlDataSource ID="SectionDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ORHP_Dev03182014ConnectionString %>" SelectCommand="SELECT DISTINCT SectionItem FROM Core.SectionItem_Lkup">               
</asp:SqlDataSource>

<asp:SqlDataSource ID="SectionIDDataSource" runat="server " ConnectionString="<%$ ConnectionStrings:ORHP_Dev03182014ConnectionString %>" InsertCommand="INSERT INTO Core.SectionItem_Lkup(SectionItemID, SectionItem, SectionItemLabel) VALUES (@SectionID, @SectionItem, @SectionItemLabel)" SelectCommand="  SELECT MAX(SectionItemID) + 1 AS SectionItemID FROM [ORHP_Dev03182014].[Core].[SectionItem_Lkup]" OnInserting="Section_OnInserted">
    <InsertParameters>
        <asp:Parameter Name="SectionID" />
        <asp:Parameter Name="SectionItem" />
        <asp:Parameter Name="SectionItemLabel" />
    </InsertParameters>
</asp:SqlDataSource>

Here are the Textboxes in the listview:

<InsertItemTemplate>
    <tr style="">
        <td>
            <asp:Button ID="InsertButton" runat="server" CommandName="Insert" Text="Insert" On />
            <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" Text="Clear" />
        </td>
        <td>
            <div style="font-size: .8em;">
            <asp:FormView ID="SectionIDFormView" runat="server" DataKeyNames="SectionItemID" DataSourceID="SectionIDDataSource">
            <ItemTemplate>
                <asp:Label ID="SectionIDLabel" runat="server" Text="SectionID" Font-Bold="true" Font-Size="1.2em" />
                <asp:TextBox ID="SectionIDTextBox" runat="server" Text='<%# Eval("SectionItemID") %>' Width="50px" />
                <asp:Label ID="SectionItemLabel" runat="server" Text="SectionItem" Font-Bold="true" Font-Size="1.2em" />
                <asp:TextBox ID="SectionItemTextBox" runat="server" Text="" />
                <asp:Label ID="SectionItemSubLabel" runat="server" Text="SectionItem Label" Font-Bold="true" Font-Size="1.2em" />
                <asp:TextBox ID="SectionItemLabelTextBox" runat="server" Text="" />
            </ItemTemplate>
            </asp:FormView>
        </div>
      </td>
   </tr>
</InsertItemTemplate>

Here is my code behind:

using (SqlConnection connection = new SqlConnection("Data Source=RCK-HRSA-DB01;Initial Catalog=ORHP_Dev03182014;User ID=userid;Password=password"))
{
    try
    {
        SqlCommand cmd1 = new SqlCommand("INSERT INTO Core.SectionItem_Lkup(SectionItemID, SectionItem, SectionItemLabel) VALUES (" + SectionIDTextBox.Text + ", " + SectionItemTextBox.Text + ", " + SectionItemLabelTextBox.Text + ")");
        connection.Open();
        cmd1.Connection = connection;
        cmd1.CommandType = CommandType.Text;
        cmd1.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
    }
}

Am I missing something?

Upvotes: 1

Views: 1675

Answers (1)

j.f.
j.f.

Reputation: 3949

The problem you are going to run into when going the route you are going is finding the controls for your ControlParameters. When a control is within a data bound control, the ID isn't what you declare it as. You can see this when you view the source on your page after it has rendered.

For FormViews, it's generally comes out to being something like SectionIDFormView$SectionIDLabel, which obviously isn't the same as SectionIDLabel. So using a ControlParameter like so may work for you:

<InsertParameters>
    <asp:ControlParameter ControlID="SectionIDFormView$SectionIDLabel"
        Direction="Input" Name="SectionID" PropertyName="Text" />
</InsertParameters>

As an alternative, when the Insert button on the ListView is clicked, handle the click event and do the insert within there. Take the inserting away from the SqlDataSource all together.

protected void ListView1_ItemCommand(object sender, ListViewCommandEventArgs e)
{
    if (e.CommandName == "Insert")
    {
        ListViewItem item = ListView1.InsertItem;
        FormView SectionIDFormView = (FormView)item.FindControl("SectionIDFormView");
        TextBox SectionIDTextBox = (TextBox)SectionIDFormView.FindControl("SectionIDTextBox");
        //Find your other controls
        //Do your insert
    }
}

Try using a parameterized query:

SqlCommand cmd1 = new SqlCommand("INSERT INTO Core.SectionItem_Lkup(SectionItemID, SectionItem, SectionItemLabel) VALUES (@SectionItemID, @SectionItem, @SectionItemLabel)");

cmd1.Parameters.AddWithValue("@SectionItemID", SectionIDTextBox.Text);
cmd1.Parameters.AddWithValue("@SectionItem", SectionItemTextBox.Text);
cmd1.Parameters.AddWithValue("@SectionItemLabel", SectionItemLabelTextBox.Text);

Upvotes: 1

Related Questions