How to use insert parameter with textbox text in listview

Question

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?

One thing I notice is that your FormView's data source is set to SectionIDDataSource and your SqlDataSource's ID is SectionDataSource. Are all your SqlDataSources matched up correctly? — j.f.
– j.f., Commented Jul 23, 2014 at 15:30
@BubbleHearth That may be the problem. I am using two datasources through this part. However, I needed SectionIDDataSource to get the SectionItemID. Should I put the Insert Commands into the other data source or both? — user3339242
– user3339242, Commented Jul 23, 2014 at 15:37
Well I was going to say your next issue is going to be your ControlParameters. They use controls that are contained within data bound controls, so the IDs won't be as you have them listed anyway. I would suggest that you take the inserts out of the SqlDataSource and do it in your codebehind instead - in the ItemCommand handler like you had started for example. — j.f.
– j.f., Commented Jul 23, 2014 at 15:53
@BubbleHearth Am I on the right track? See code changes above — user3339242
– user3339242, Commented Jul 23, 2014 at 16:09

j.f. · Accepted Answer · 2014-07-23 21:23:31Z

1

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);

edited Jul 23, 2014 at 21:23

answered Jul 23, 2014 at 19:04

j.f.

3,9492 gold badges33 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3339242 Over a year ago

How do I handle the click event? I put OnItemCommand="SectionListView_OnItemCommand" in the ListView properties but it never reaches the SectionListView_OnItemCommand method

j.f. Over a year ago

and you are clicking a button within your ListView that has a CommandName attribute set?

user3339242 Over a year ago

Yes the button is in the insert template and the Command Name is Insert. Do I need an Onclick method for the button?

j.f. Over a year ago

No, you shouldn't need it. That should work. But for the sake of getting it working you could add the same code (except the condition) into a new OnClick handler.

j.f. Over a year ago

Use a parameterized query instead. It is much safer. See my edit above.

|

Collectives™ on Stack Overflow

How to use insert parameter with textbox text in listview

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related