Library

Output: Sql>Create table bookdetail(booknumber number(10),bookname varchar2(20),author varchar2(20),noofcopies number(3)); Table created Sql>Create table booktrans(booknumber number(10),bookname varchar2(20),author varchar2(20),studno number(10),studentname varchar2(20)); Table created Form1

Form2

Code window: Book Detail form Option Explicit Dim fcnbook As New ADODB.Connection Dim frsbook As New ADODB.Recordset Dim lzsql As String Dim lzflag As String Private Sub cmdcancel_Click() txtbookno.Text = "" txtbookname.Text = "" txtauthor.Text = "" txtnoofcopies.Text = "" End Sub Private Sub cmdexit_Click() Unload Me End Sub Private Sub cmdOk_Click() Addition End Sub Private Sub Form_Load() Fcnbook Open "Provider=MSDAORA 1;Password=cho;User ID=cho;Data source=oracle" End Sub Public Sub Addition() lzsql = "Insert into bookdetail values(" & txtbookno.Text & ",'" lzsql = lzsql & txtbookname.Text & "','" lzsql = lzsql & txtauthor.Text & "'," lzsql = lzsql & txtnoofcopies.Text & ")" fcnbook.Execute (lzsql) MsgBox "Book details Added" End Sub

Library Transaction Option Explicit Dim fcnlibrary As New ADODB.Connection Dim frslibrary As New ADODB.Recordset Dim lzsql As String Dim lzflag As String Private Sub cmdcancel_Click() txtbookno.Text = "" txtbookname.Text = "" txtauthor.Text = "" txtstudno.Text = "" txtstudentname.Text = "" End Sub Private Sub cmdexit_Click() Unload Me End Sub Private Sub cmdOk_Click() If cmboperation.ListIndex = 0 Then Issue reducebook ElseIf cmboperation.ListIndex = 1 Then addbook End If End Sub Private Sub Form_Load() fcnemp.Open "Provider=MSDAORA.1;Password=cho;User ID=cho;Data source=oracle" cmboperation.AddItem "Issue" cmboperation.AddItem "Return" cmboperation.ListIndex = 0 End Sub

Public Sub Issue() lzsql = "Insert into booktrans values(" & txtbookno.Text & ",'" lzsql = lzsql & txtbookname.Text & "','" lzsql = lzsql & txtauthor.Text & "'," lzsql = lzsql & txtstudno.Text & ",'" lzsql = lzsql & txtstudentname.Text & "')" fcnlibrary.Execute (lzsql) End Sub Public Sub reducebook() lzsql = "Update bookdetail set noofcopies = noofcopies - 1 " lzsql = lzsql & " where bookno=" & txtbookno.Text fcnlibrary.Execute (lzsql) MsgBox "Book Issued" End Sub Public Sub addbook() lzsql = "Update bookdetail set noofcopies = noofcopies + 1 " lzsql = lzsql & " where bookno=" & txtbookno.Text fcnlibrary.Execute (lzsql) MsgBox "Book Returned" End Sub Public Sub displaybook() lzsql = "Select * from bookdetail where bookno=" & txtbookno.Text frslibrary.Open lzsql, fcnlibrary, adOpenDynamic, adLockBatchOptimistic If Not (frslibrary.BOF And frslibrary.EOF) Then txtbookname.Text = frslibrary.Fields("bookname") txtauthor.Text = frslibrary.Fields("author") Else MsgBox "Invalid Book number" End If End Sub

Public Sub displaystud() lzsql = "Select * from booktrans where studno=" & txtstudno.Text frslibrary.Open lzsql, fcnlibrary, adOpenDynamic, adLockBatchOptimistic If Not (frslibrary.BOF And frslibrary.EOF) Then txtstudentname.Text = frslibrary.Fields("studname") txtbookno.Text = frslibrary.Fields("bookno") txtbookname.Text = frslibrary.Fields("bookname") txtauthor.Text = frslibrary.Fields("authorname") Else MsgBox "Invalid student number" End If End Sub Private Sub txtbookno_LostFocus() If cmboperation.ListIndex = 0 Then displaybook End If End Sub Private Sub txtstudno_LostFocus() If cmboperation.ListIndex = 1 Then displaystud End If End Sub

Form2-Return

SQL> select * from bookdetail; BOOKNO BOOKNAME AUTHOR NOOFCOPIES ---------- ------------------------- ------------------------- ---------- ---------------------1 DBMS Sylbertz 19 SQL> select * from booktrans; BOOKNO BOOKNAME AUTHOR STUDNO STUDENTNAME ------------------------------------- ------------------- -------------------- -------------------------1 DBMS Sylbertz 1 JP