get row index of repeater

Question

User1717218719 posted

I have the following code. I am looking to update a column in my sql database. eg. change ID from 1 to 2 when the row for that number is checked and the sumbit button is clicked. I want this ID and this ID only to be updated in the sql database. I am filling the repeater from and sql stored procedure.I am unsure how to code the part in my code where it says "  ''UPDATE A VALUE IN THE DB FOR THE ROW THAT HAS BEEN CHECKED ". any help with this would be great.      

 <asp:Repeater ID="Rpt" runat="server">
                <HeaderTemplate>
                    <table id="tbl" style="width: 50%">
                        <tr>
                            <td>Nbr</td>
                            <td>
                                <asp:CheckBox ID="chkAll" runat="server" />
                                Select All</td>
                        </tr>
                </HeaderTemplate>
                <ItemTemplate>
                    <tr>
                        <td style="border-bottom-color: transparent">
                            <asp:TextBox CssClass="lbl" ID="txt" runat="server" ReadOnly="true" Text='<%#Eval("Nbr") %>'></asp:TextBox>
                        </td>
                        <td style="border-bottom-color: transparent">
                            <asp:CheckBox ID="chkRowDataData" runat="server" />
                    </tr>
                </ItemTemplate>
                <FooterTemplate>
                    </table>
                </FooterTemplate>

            </asp:Repeater>

 Protected Sub BtnSubmit_Click(sender As Object, e As EventArgs) Handles BtnSubmit.Click

   For Each ri As RepeaterItem In Rpt.Items
            If ri.ItemType = ListItemType.Item Or ri.ItemType = ListItemType.AlternatingItem Then
                Dim cb As New CheckBox()
                cb = CType(ri.FindControl("chkRowDataData"), CheckBox)

                If cb.Checked Then
                   ''UPDATE A VALUE IN THE DB FOR THE ROW THAT HAS BEEN CHECKED 
				call UpdateToDataBase()
                End If

            End If
        Next
  End Sub

  comComm = New SqlCommand
            With comComm
                .Connection = conConn
                .Transaction = trnTran
                .CommandType = CommandType.Text
                .CommandText = "UPDATE tbl1 SET ID = @ID, Nbr = @Nbr, Mth = @Mth,IDUpdate = @IDUpdate"

                .Parameters.AddWithValue("@ID", intID)
                .Parameters.AddWithValue("@Nbr", intNbr)
                .Parameters.AddWithValue("@Mth", Val(Me.txtMnth.Text))
                .Parameters.AddWithValue("@IDUpdate", 2)

                .ExecuteNonQuery()

                intTktID = .Parameters.Item("@ID").Value
                intTktDocNbr = .Parameters.Item("@Nbr").Value

            End With


            conConn.Close()

        Catch ex As Exception

            MsgBox("Error [Updatedatabase]: " & ex.Message & ex.StackTrace)

        Finally

Answer 1

User288213138 posted

Hi E.RU,

I want this ID and this ID only to be updated in the sql database

According to your description, i made demo for you.

If you want to update the ID only in database, you only need to update the database and not bind the repeater.

The code:

aspx:

<form id="form1" runat="server">
        <div>
            <asp:Repeater ID="Rpt" runat="server">
                <HeaderTemplate>
                    <table id="tbl" style="width: 50%">
                        <tr>
                            <td>Nbr</td>
                            <td>ID</td>
                            <td>Mth</td>
                            <td>IDUpdate</td>
                            <td><asp:CheckBox ID="chkAll" runat="server" />Select All</td>
                        </tr>
                </HeaderTemplate>
                <ItemTemplate>
                    <tr>
                        <td style="border-bottom-color: transparent">
                            <asp:TextBox CssClass="lbl" ID="txt" runat="server" ReadOnly="true" Text='<%#Eval("Nbr") %>'></asp:TextBox>
                        </td>
                        <td><asp:TextBox ID="TextBox1" runat="server" Text='<%#Eval("ID") %>'></asp:TextBox></td>
                        <td><asp:TextBox ID="TextBox2" runat="server" Text='<%#Eval("Mth") %>'></asp:TextBox></td>
                        <td><asp:TextBox ID="TextBox3" runat="server" Text='<%#Eval("IDUpdate ") %>'></asp:TextBox></td>
                        <td style="border-bottom-color: transparent">
                           <asp:CheckBox ID="chkRowDataData" runat="server" /></td>
                    </tr>
                </ItemTemplate>
                <FooterTemplate>
                    </table>
                </FooterTemplate>
            </asp:Repeater>
            <asp:Button ID="BtnSubmit" runat="server" Text="Button" OnClick="BtnSubmit_Click" />
        </div>
    </form>


aspx.cs:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        If Not IsPostBack Then
            Bind()
        End If
    End Sub

    Private constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

    Public Sub Bind()
        Using con As SqlConnection = New SqlConnection(constring)

            Using cmd As SqlCommand = New SqlCommand("GetData", con)
                cmd.CommandType = CommandType.StoredProcedure
                con.Open()
                Dim idr As IDataReader = cmd.ExecuteReader()
                Rpt.DataSource = idr
                Rpt.DataBind()
            End Using
        End Using
    End Sub

    Protected Sub BtnSubmit_Click(ByVal sender As Object, ByVal e As EventArgs)
        For Each ri As RepeaterItem In Rpt.Items

            If ri.ItemType = ListItemType.Item Or ri.ItemType = ListItemType.AlternatingItem Then
                Dim cb As CheckBox = New CheckBox()
                cb = CType(ri.FindControl("chkRowDataData"), CheckBox)

                If cb.Checked Then
                    Dim Tid As TextBox = CType(ri.FindControl("TextBox1"), TextBox)
                    Dim id As Integer = Convert.ToInt32(Tid.Text) + 1
                    Dim Nbr As String = (CType(ri.FindControl("txt"), TextBox)).Text
                    Dim query As String = "UPDATE Test60 SET ID=@ID where Nbr=@Nbr"

                    Using con As SqlConnection = New SqlConnection(constring)

                        Using cmd As SqlCommand = New SqlCommand(query)
                            cmd.Parameters.AddWithValue("@ID", id)
                            cmd.Parameters.AddWithValue("@Nbr", Nbr)
                            cmd.Connection = con
                            con.Open()
                            cmd.ExecuteNonQuery()
                            con.Close()
                        End Using
                    End Using
                End If
            End If
        Next
    End Sub

StoredProcedure:

CREATE PROCEDURE  GetData
AS
BEGIN
SELECT * from Test60
END
GO

The result:

Best regards,

Sam