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?
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?
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.
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......
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.