hey guys i need some help on table values

for example i have a table for books and it has two fields one is number of Copies and Number of Borrowed
how do i subtract and add values to each fields for every single transaction on the same table?

Recommended Answers

All 8 Replies

If your table has the fields (for example)

myBooks
    ISBN
    Title
    Copies
    Borrowed

You can get a list of the number of available copies of all books by

SELECT Title, Copies-Borrowed AS Available FROM myBooks

If a book gets borrowed and you need to update the table you can do

UPDATE myBooks SET Borrowed=Borrowed+1 WHERE ISBN='some value'

And if a book is returned change "+" to "-"

Where do i exaclty put the Select Title?

That's why you are asked to share your code in the rules.

how do i subtract and add values to each fields for every single transaction on the same table?

When you are saying transaction, can you define transaction? Share the code for it?

how do i subtract and add values to each fields for every single transaction on the same table

That is very ambiguous question which I tried to answer. Are you saying you want to modify each field (doesn't make sense but that's what you said)?

Where do i exaclty put the Select Title?

After line 117.

commented: :D +8

That is very ambiguous question which I tried to answer. Are you saying you want to modify each field (doesn't make sense but that's what you said)?

What i mean about the "each field" is the Quantity and Borrowed only......

Okay so its working for adding values on the Borrowed field but its not Subtracting on the Quantity Field

Heres some code for hte Isssue button:

        Dim SqlStatement As String = "INSERT INTO tblIssue(ISBN, Title, BorrowerID, Name, Occupation, BorrowerType, School, DateIssued, DateToBeReturned) VALUES('" & txtBookISBN.Text & "','" & txtBTitle.Text & "','" & txtMemID.Text & "','" & txtMemName.Text & "','" & txtOccupation.Text & "','" & cboBtype.Text & "','" & txtSchool.Text & "','" & DateTimePicker1.Text & "','" & DateTimePicker2.Text & "')"
        Save(SqlStatement)

        ds = New DataSet
        da = New MySqlDataAdapter("update tblbooks set  Borrowed='" & +1 & "' where ISBN='" & txtBookISBN.Text & "'", conn)
        da.Fill(ds, "tblbooks")

        conn.ConnectionString = connstr

        Call showdata()

Showdata:

Sub showdata()

        Dim conn As MySqlConnection = New MySqlConnection
        Dim da As New MySqlDataAdapter
        Dim ds As New DataSet
        Dim str1 As String = "select * from tblbooks"

        conn.ConnectionString = connstr

        da.SelectCommand = New MySqlCommand(str1, conn)
        da.Fill(ds)
        conn.Close()
        frmBooks.dgvBooks.DataSource = ds.Tables(0)

    End Sub

Save:

 Public Sub Save(ByRef SqlStatement As String)
        Dim cmd As MySqlCommand = New MySqlCommand

        With cmd

            .CommandText = SqlStatement
            .CommandType = CommandType.Text
            .Connection = conn
            .ExecuteNonQuery()

        End With

        conn.Close()
        MsgBox("Success!")


        If MsgBox("Do You Want to Add New Record?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then



            txtMemID.Text = ""
            txtMemName.Text = ""
            txtBookISBN.Text = ""
            txtBTitle.Text = ""
            txtOccupation.Text = ""
            cboBtype.Text = ""
            txtSchool.Text = ""

            txtMemID.Focus()

        Else

            Me.Close()


        End If

        conn.Dispose()

    End Sub

I dont know if im doing it right here for hte Select code:

 Sub showdata1()

        Dim conn1 As MySqlConnection = New MySqlConnection
        Dim da1 As New MySqlDataAdapter
        Dim ds1 As New DataSet
        Dim str1 As String = "select Title, Quantity - Borrowed As Available from tblBooks"

        conn1.ConnectionString = connstr

        da1.SelectCommand = New MySqlCommand(str1, conn)
        da1.Fill(ds1)
        conn1.Close()
        frmBooks.dgvBooks.DataSource = ds1.Tables(0)

    End Sub

okay maybe not working.....

nevermind got it working again......

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.