Table values

[kenomote]

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?

[Reverend Jim]

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 "-"

[kenomote]

Where do i exaclty put the Select Title?

[adam_k]

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?

[Reverend Jim]

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.

[kenomote]

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......

[kenomote]

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

[kenomote]

okay maybe not working.....

[kenomote]

nevermind got it working again......