Add new repeater row to an sql database

Question

User1717218719 posted

I have the following code which updates the values in the repeater both on screen and in the database. I would now like to code for the instance where when I add a new row to my repeater it updates the database. any help with how I can code for the event of adding a row would be great.

        Dim conConn As SqlConnection = Nothing
        Dim comComm As SqlCommand = Nothing
        Dim adpAdap As SqlDataAdapter = Nothing
        Dim trnTran As SqlTransaction = Nothing
        Dim intID As Integer = lblID.Text


            conConn = New SqlConnection(GblSqlCon)
            conConn.Open()
          

            Dim dtb As DataTable = GetComItms()
            comComm = New SqlCommand
            With comComm
                .Connection = conConn
                .Transaction = trnTran
                .CommandType = CommandType.Text
                .CommandText = "UPDATE tbl_T SET TypID = @TypID, Cde = @Cde, AmtA = @AmtA, Amti = @AmtAi, AmtD = @AmtD WHERE ID = @ID"

                .Parameters.Add("@ID", SqlDbType.BigInt)
                .Parameters.Add("@TypID ", SqlDbType.TinyInt)
                .Parameters.Add("@Cde ", SqlDbType.Char)
                .Parameters.Add("@AmtA ", SqlDbType.Decimal)
                .Parameters.Add("@Amti ", SqlDbType.Decimal)
                .Parameters.Add("@AmtD ", SqlDbType.Decimal)

                For i As Integer = 0 To dtbTaxes.Rows.Count - 1

                    .Parameters.Item("@ID").Value = dtb.Rows(i).Item("TaxID")
                    .Parameters.Item("@TypID ").Value = dtb.Rows(i).Item("TaxTypID")
                    .Parameters.Item("@Cde").Value = dtb.Rows(i).Item("Cde")
                    .Parameters.Item("@AmtA").Value = dtb.Rows(i).Item("AmtA")
                    .Parameters.Item("@Amti").Value = dtb.Rows(i).Item("Amti")
                    .Parameters.Item("@AmtD").Value = dtb.Rows(i).Item("AmtD")

                    .ExecuteNonQuery()

                Next

            End With

Answer 1

User288213138 posted

Hi E.RU,

UPDATE tbl_T SET TypID = @TypID, Cde = @Cde, AmtA = @AmtA, Amti = @AmtAi, AmtD = @AmtD WHERE ID = @ID

You want to insert a new row, why use the update statement? Shouldn't it be an insert?

Here's a demo you can use as a reference.

Take the data to be inserted and insert it into the database through SQL insert statements.

The code:

Aspx:
<asp:Repeater runat="server" ID="Repeater1">
                <HeaderTemplate>
                    <table border="1">
                        <tr>
                            <th>CustomerId</th>
                            <th>Name</th>
                            <th>Country</th>
                        </tr>
                </HeaderTemplate>
                <ItemTemplate>
                    <tr>
                        <td>
                            <%# Eval("CustomerId") %>
                        </td>
                        <td>
                            <%# Eval("Name") %>
                        </td>
                        <td>
                            <%# Eval("Country") %>
                        </td>
                    </tr>
                </ItemTemplate>
                <FooterTemplate>
                    </table>                   
                </FooterTemplate>
            </asp:Repeater>
        <table>
            <tr>
                <td>CustomerId:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox></td>
                <td>Name:<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox></td>
                <td>Country:<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox></td>
            </tr>
        </table>
        <asp:Button Text="Add New" ID="btnAdd" OnClick="btnAdd_Click" runat="server" />

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

    Public Sub Bind()
        Dim query As String = "select * from customer"
        Repeater1.DataSource = SqlHelper.ExecuteDataTable(query)
        Repeater1.DataBind()
    End Sub

    Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim customerId As String = TextBox1.Text
        Dim name As String = TextBox2.Text
        Dim country As String = TextBox3.Text
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        Dim query As String = "INSERT INTO Customer VALUES(@CustomerId,@Name, @Country)"
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr527").ConnectionString

        Using con As SqlConnection = New SqlConnection(constr)

            Using cmd As SqlCommand = New SqlCommand(query)
                cmd.Parameters.AddWithValue("@CustomerId", customerId)
                cmd.Parameters.AddWithValue("@Name", name)
                cmd.Parameters.AddWithValue("@Country", country)
                cmd.Connection = con
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using

        Bind()
    End Sub
End Class

The result:

Best regards,

Sam