Member Avatar for annquin

Hi can someone please help me how to update after inserting ?
In this code I am going to insert a transaction that borrow books in library system BUT In every borrow of books I need to lessen the quantity of the book.. I have an error in this code.. the first and second update lessen 1 in quantity bUT the third one did not... I was wondering why..? Is my code correct? Is there a new approach i can use? please help me..
this is my code

Private Sub btnOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOk.Click
SqlCon.Open()
        If BooksBorrow.TextBox1.Text <> "" And BooksBorrow.TextBox2.Text <> "" And BooksBorrow.TextBox4.Text <> "" Then
            Try
                sqlCommand.Connection = SqlCon
                sqlCommand.CommandText = "INSERT INTO transaction (book_ID, borrower_ID, Date_Borrowed, Date_Due) VALUES ('" & BooksBorrow.TextBox1.Text & "','" & BooksBorrow.brwr_id.Text & "','" & Dateb.Text & "','" & Dated.Text & "');"
                sqlCommand.ExecuteNonQuery()
                sqlCommand.CommandText = "UPDATE books SET quantity = " & BooksBorrow.qt1.Text - 1 & " WHERE book_ID = '" & BooksBorrow.TextBox1.Text & "'"
                sqlCommand.ExecuteNonQuery()
                sqlCommand.CommandText = "INSERT INTO transaction (book_ID, borrower_ID, Date_Borrowed, Date_Due) VALUES ('" & BooksBorrow.TextBox2.Text & "','" & BooksBorrow.brwr_id.Text & "','" & Dateb.Text & "','" & Dated.Text & "');"
                sqlCommand.ExecuteNonQuery()
                sqlCommand.CommandText = "UPDATE books SET quantity = " & BooksBorrow.q2.Text - 1 & " WHERE book_ID = '" & BooksBorrow.TextBox2.Text & "'"
                sqlCommand.ExecuteNonQuery()
                sqlCommand.CommandText = "INSERT INTO transaction (book_ID, borrower_ID, Date_Borrowed, Date_Due) VALUES ('" & BooksBorrow.TextBox4.Text & "','" & BooksBorrow.brwr_id.Text & "','" & Dateb.Text & "','" & Dated.Text & "');"
                sqlCommand.ExecuteNonQuery()
                sqlCommand.CommandText = "UPDATE books SET quantity = " & BooksBorrow.q3.Text - 1 & " WHERE book_ID = '" & BooksBorrow.TextBox3.Text & "'"
                sqlCommand.ExecuteNonQuery()
                MsgBox("Successfully Borrowed Book!")
            Catch ex As Exception
                MsgBox(ex.ToString, vbCritical)
            End Try
        End If
        SqlCon.Close()
        update_ins()
            Me.Close()
            BooksBorrow.Close()
            Transaction.Show()
    End Sub
  • 3 Contributors
  • 2 Replies
  • 106 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Dili1234
Member Avatar for mikeybware

You can not do a calculation on a string, so try converting the string to an integer throughout your code:

"UPDATE books SET quantity = " & CInt(BooksBorrow.qt1.Text) - 1 & " WHERE book_ID = '" & BooksBorrow.TextBox1.Text & "'"
Member Avatar for Dili1234

do you need to konw the availble before you update the 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.