1,105,456 Community Members

Table values

Member Avatar
kenomote
Light Poster
46 posts since Oct 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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?

Member Avatar
Reverend Jim
Noli mentula
5,459 posts since Aug 2010
Reputation Points: 761 [?]
Q&As Helped to Solve: 653 [?]
Skill Endorsements: 51 [?]
Moderator
Featured
 
0
 

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

Member Avatar
kenomote
Light Poster
46 posts since Oct 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Where do i exaclty put the Select Title?

Member Avatar
adam_k
Veteran Poster
1,056 posts since Jun 2011
Reputation Points: 239 [?]
Q&As Helped to Solve: 212 [?]
Skill Endorsements: 17 [?]
 
0
 

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?

Member Avatar
Reverend Jim
Noli mentula
5,459 posts since Aug 2010
Reputation Points: 761 [?]
Q&As Helped to Solve: 653 [?]
Skill Endorsements: 51 [?]
Moderator
Featured
 
1
 

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.

Member Avatar
kenomote
Light Poster
46 posts since Oct 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
kenomote
Light Poster
46 posts since Oct 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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
Member Avatar
kenomote
Light Poster
46 posts since Oct 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

okay maybe not working.....

Member Avatar
kenomote
Light Poster
46 posts since Oct 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

nevermind got it working again......

Question Answered as of 1 Year Ago by Reverend Jim and adam_k
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article