Itemcommand to add a row in a Datalist to a new table

I have a Datalist that displays records from an XML file.  I have added a linkbutton with a specified CommandName in the Itemtemplate.  When clicked I want the associated record added to a table in a SQL database.  The Insert is working fine except rather than the selected row being inserted, EVERY record is being inserted.  How do I specify only one row be inserted to the table?
rpm962Asked:
Who is Participating?
 
Juan_BarreraConnect With a Mentor Commented:
Sorry rpm962, try by replacing:

Dim dl1 As DataListItem = e.Item.DataItem

with:

Dim dl1 As DataListItem = e.Item


0
 
Juan_BarreraCommented:
Hi rpm962,

Can you please post the code you use for inserting the row?

Thanks,
0
 
rpm962Author Commented:
Hey Juan, sorry this took so long.  Thanks for your help.
Protected Sub dlShowLines_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs) Handles dlShowLines.ItemCommand
        
        If e.CommandName = "AddAwayTeam" Then
            Dim conn As String = ConfigurationManager.ConnectionStrings("LocktoberConnect").ConnectionString()
            Dim connection As SqlConnection = New SqlConnection(conn)
            For Each dl1 As DataListItem In dlShowLines.Items
                Dim sql As String = "INSERT INTO Picks2 ([Team], [Line], [DatePicked], [DateGame], [UserId], [Opponent]) VALUES (@Team, @Line, @DatePicked, @DateGame, @UserId, @Opponent)"
                Dim cmd As SqlCommand = New SqlCommand(sql, connection)
                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("@Team", CType(dl1.FindControl("lblAwayTeam"), Label).Text)
                cmd.Parameters.AddWithValue("@Line", CType(dl1.FindControl("lblAwayLine"), Label).Text)
                cmd.Parameters.AddWithValue("@DatePicked", DateTime.Now)
                cmd.Parameters.AddWithValue("@DateGame", CType(dl1.FindControl("lblDateGame"), Label).Text)
                cmd.Parameters.AddWithValue("@UserId", UserIDValue.Text)
                cmd.Parameters.AddWithValue("@Opponent", CType(dl1.FindControl("lblHomeTeam"), Label).Text)
                connection.Open()
                cmd.ExecuteNonQuery()
                connection.Close()
            Next
        End If
    End Sub

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Juan_BarreraCommented:

 Protected Sub DataList1_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs) Handles DataList1.ItemCommand
        If e.CommandName = "AddAwayTeam" Then
            Dim dl1 As DataListItem = e.Item.DataItem
            Dim conn As String = ConfigurationManager.ConnectionStrings("LocktoberConnect").ConnectionString()
            Dim connection As SqlConnection = New SqlConnection(conn)
            Dim sql As String = "INSERT INTO Picks2 ([Team], [Line], [DatePicked], [DateGame], [UserId], [Opponent]) VALUES (@Team, @Line, @DatePicked, @DateGame, @UserId, @Opponent)"
            Dim cmd As SqlCommand = New SqlCommand(sql, connection)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@Team", CType(dl1.FindControl("lblAwayTeam"), Label).Text)
            cmd.Parameters.AddWithValue("@Line", CType(dl1.FindControl("lblAwayLine"), Label).Text)
            cmd.Parameters.AddWithValue("@DatePicked", DateTime.Now)
            cmd.Parameters.AddWithValue("@DateGame", CType(dl1.FindControl("lblDateGame"), Label).Text)
            cmd.Parameters.AddWithValue("@UserId", UserIDValue.Text)
            cmd.Parameters.AddWithValue("@Opponent", CType(dl1.FindControl("lblHomeTeam"), Label).Text)
            connection.Open()
            cmd.ExecuteNonQuery()
            connection.Close()
        End If
    End Sub

Open in new window

0
 
rpm962Author Commented:
I am getting "Object reference not set to an instance of an object"
Line 58:             cmd.Parameters.AddWithValue("@Team", CType(dl1.FindControl("lblAwayTeam"), Label).Text)
 
 

Open in new window

0
 
rpm962Author Commented:
Great!  Thank you.  I originally had it like your first suggestion with the .DataItem.  Turns out I wasted two days on 9 keystrokes.  Thank again!
0
 
Juan_BarreraCommented:
No worries!

Now, my initial mistake reminds me to make you a suggestion:
I've put  e.Item.DataItem becasue I'm used to work with the underlying data rather that with the presentation controls. If you can, use this instead of finding the controls and retrieving its value.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.