Add new repeater row to an sql database RRS feed

  • 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)
                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")
                End With

    Wednesday, July 17, 2019 9:31 AM

All replies

  • 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:

    <asp:Repeater runat="server" ID="Repeater1">
                        <table border="1">
                                <%# Eval("CustomerId") %>
                                <%# Eval("Name") %>
                                <%# Eval("Country") %>
                    <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>
            <asp:Button Text="Add New" ID="btnAdd" OnClick="btnAdd_Click" runat="server" />
    Class SurroundingClass
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            If Not IsPostBack Then
            End If
        End Sub
        Public Sub Bind()
            Dim query As String = "select * from customer"
            Repeater1.DataSource = SqlHelper.ExecuteDataTable(query)
        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
                End Using
            End Using
        End Sub
    End Class

    The result:

    Best regards,


    Thursday, July 18, 2019 2:53 AM