Member Avatar for annquin

hi can someone please help me in updating one column in a table but you will be using three values from a textbox..
it was like this.

    tablebook
    column  in tablebook is quantity
i need to update a book quantity using the value from textbox1. but
wat if i have a value from textbox2 and textbox3
i need to update textbox1,textbox2,textbox3 values into book quantity where it has different bookname

sample of my coding

If TextBox1.Text <> "" And TextBox2.Text <> "" And TextBox4.Text <> "" Then
            SqlCon.Open()
            sqlCommand.Connection = SqlCon
            sqlCommand.CommandText = "UPDATE books SET quantity = " & qt1.Text - 1 & " WHERE book_ID = '" & TextBox1.Text & "'"
            sqlCommand.ExecuteNonQuery()
            SqlCon.Close()
            update_quantity_borrow()
            SqlCon.Open()
            sqlCommand.CommandText = "UPDATE books SET quantity = " & q2.Text - 1 & " WHERE book_ID = '" & TextBox2.Text & "'"
            sqlCommand.ExecuteNonQuery()
            SqlCon.Close()
            update_quantity_borrow()
            SqlCon.Open()
            sqlCommand.CommandText = "UPDATE books SET quantity = " & q3.Text - 1 & " WHERE book_ID = '" & TextBox4.Text & "'"
            sqlCommand.ExecuteNonQuery()
            SqlCon.Close()
            update_quantity_borrow()
        End If
  • 3 Contributors
  • 2 Replies
  • 224 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by TimCadieux

Dani AI

Generated

Short summary and recommended approach: the goal is to update multiple book rows in one action while avoiding repeated open/close calls and SQL injection. Treat each book-ID/quantity pair as a unit, validate and parse the inputs, open a single connection (use a transaction if the updates must be atomic), and run parameterized updates in a loop or a single batch statement. This follows the looping idea suggested by and answers the clarification prompt from while replacing string concatenation (which is unsafe).

A compact, safe pattern in VB.NET (assumes three TextBox pairs named differently from the original post):

Dim ids = New TextBox() {txtBookIdA, txtBookIdB, txtBookIdC}
Dim qtyBoxes = New TextBox() {txtQtyA, txtQtyB, txtQtyC}

Using conn As New SqlConnection(connString)
    conn.Open()
    Using tran = conn.BeginTransaction()
        Using cmd As New SqlCommand("UPDATE books SET quantity = quantity - @dec WHERE book_ID = @id AND quantity >= @dec", conn, tran)
            cmd.Parameters.Add("@id", SqlDbType.VarChar, 50)
            cmd.Parameters.Add("@dec", SqlDbType.Int)

            For i = 0 To ids.Length - 1
                Dim bookId = ids(i).Text.Trim()
                Dim dec As Integer
                If String.IsNullOrEmpty(bookId) OrElse Not Integer.TryParse(qtyBoxes(i).Text, dec) Then Continue For
                If dec <= 0 Then Continue For

                cmd.Parameters("@id").Value = bookId
                cmd.Parameters("@dec").Value = dec

                Dim rows = cmd.ExecuteNonQuery()
                ' rows = 0 means book not found or not enough stock; handle as needed
            Next
        End Using
        tran.Commit()
    End Using
End Using

Notes and cautions: validate inputs with Integer.TryParse, prevent negative quantities by checking row count or adding the quantity condition (as shown). Keep one open connection and use Using blocks to ensure disposal. If multiple clients will modify stock concurrently, consider making the decrement logic server-side (stored procedure) or add optimistic concurrency (rowversion) to avoid race conditions. This approach keeps the code safe, efficient, and maintainable compared with the repeated open/close and string-built SQL in the original post.

Recommended Answers

All 2 Replies

Member Avatar for M.Waqas Aslam

can you please explain little bit , ?what you want to do ? may be we can give to better solution .

Regards

Member Avatar for TimCadieux

Maybe I'm reading this wrong, but you'll have multiple rows, each with a book and a possible Quantity. you want the user to hit Update once and it will update each Row with the Quantity if it exists?

If that's correct, why not just do a Loop through the Rows, for each Row, if txtQty > 0 then Update based on current RowID ?

Also, they way you are performing your SQL Querys is a huge no-no, it's begging for SQL injection and a Hack!

Edited by TimCadieux because: more data
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.