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.
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
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
IIS.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
Member
51 Points
187 Posts
get row index of repeater
Oct 21, 2019 11:32 AM|E.RU|LINK
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.
Contributor
3370 Points
1409 Posts
Re: get row index of repeater
Oct 23, 2019 10:16 AM|samwu|LINK
Hi E.RU,
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