[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

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?
0
rpm962
Asked:
rpm962
  • 4
  • 3
1 Solution
 
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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
Juan_BarreraCommented:
Sorry rpm962, try by replacing:

Dim dl1 As DataListItem = e.Item.DataItem

with:

Dim dl1 As DataListItem = e.Item


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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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