how do i add htmltablecell data to database after i click submit

Question

i created an htmltable in .aspx.vb, because i have a lot of rows that come from database and they depend on the querystring. that part is fine. But when someone makes a change in one of the cells how do i save data back to database?

code -

Dim tr As New HtmlTableRow
Dim td As New HtmlTableCell
td = New HtmlTableCell
Dim txtbox1 As New TextBox
txtbox1.ID = "price_" & rd("id")
txtbox1.Text = rd("price")
td.Controls.Add(txtbox1)
tr.Cells.Add(td)
tb1.Rows.Add(tr)

now when someone changes the price in the textbox, how do i save it in database? this is code for submit button -

Protected Sub submit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles submit.Click
    Dim sqlcnn As SqlConnection = Nothing, sqlstr As String = "", sqlcmd As SqlCommand
    sqlstr = "UPDATE ???"
    sqlcmd = New SqlCommand(sqlstr, sqlcnn)
    sqlcmd.ExecuteScalar()
End Sub

Please answer in detail. thanks

Answer 1

I would suggest using the built in grid controls. Here is a walkthrough Walkthrough: Editing and Inserting Data in Web Pages with the DetailsView Web Server Control . It might be better to start with Walkthrough: Basic Data Access in Web Pages.

If you choose to move forward with HTML then here is one way to do it based on what you provided. You can add an update button and try this code in that event:

'Create an update Command with the apropriate paramaters'
Dim sql = ("UPDATE SO_Prices SET Price = @Price WHERE ID = @ID")
Dim Cmd As New SqlCommand(sql, connection)
Cmd.Parameters.Add("@Price", SqlDbType.SmallMoney)
Cmd.Parameters.Add("@ID", SqlDbType.Int)

connection.Open()

'Loop through the fields returned'
For Each Key As String In Request.Form.Keys

    'Make sure you only process fields you want'
    If Key.StartsWith("price") Then

        'Pull the ID out of the field name by splitting on the underscore'
        ' then choosing the second item in the array'
        Dim ID As String = Key.Split("_")(1)

        'Update the paramaters for the update query'
        Cmd.Parameters("@ID").Value = ID
        Cmd.Parameters("@Price").Value = Request.Form(Key)

        'Run the SQL to update the record'
        Cmd.ExecuteNonQuery()

    End If
Next

connection.Close()

Let me know if you need any additional assitance. If not, please mark this as the correct answer.