-
Notifications
You must be signed in to change notification settings - Fork 0
/
BOOKRETURN.vb
202 lines (186 loc) · 8.13 KB
/
BOOKRETURN.vb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
Imports System.Data.SqlClient
Public Class BOOKRETURN
Private Sub BOOKRETURN_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Try
Dim con As New SqlConnection(My.Settings.LIBRARY)
Dim cmd As New SqlCommand("SELECT Transaction_id FROM STAFFissue", con)
Dim adapter As New SqlDataAdapter(cmd)
Dim table As New DataTable()
adapter.Fill(table)
ComboBox2.DataSource = table
ComboBox2.DisplayMember = "Transaction_id"
ComboBox2.ValueMember = "Transaction_id"
Catch EX As Exception
MsgBox(EX.ToString)
End Try
Try
Dim con As New SqlConnection(My.Settings.LIBRARY)
Dim cmd As New SqlCommand("SELECT Transaction_id FROM issue", con)
Dim adapter As New SqlDataAdapter(cmd)
Dim table As New DataTable()
adapter.Fill(table)
ComboBox1.DataSource = table
ComboBox1.DisplayMember = "Transaction_id"
ComboBox1.ValueMember = "Transaction_id"
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Private Sub Button9_Click(sender As Object, e As EventArgs) Handles Button9.Click
Dim con1 As New SqlConnection(My.Settings.LIBRARY)
Dim cmd1 As New SqlCommand("SELECT [Issue],[Return_Date],[Student_id],[Student_Name],[Department],[Accession_no],[Book_Title],[Author],[Edition] FROM issue where Transaction_id=@ID", con1)
con1.Open()
cmd1.Parameters.AddWithValue("@ID", SqlDbType.NVarChar).Value = ComboBox1.Text
Dim DA As SqlDataReader = cmd1.ExecuteReader
While (DA.Read())
DateTimePicker1.Text = DA.GetValue(0)
DateTimePicker2.Text = DA.GetValue(1)
TextBox13.Text = DA.GetValue(2)
TextBox2.Text = DA.GetValue(3)
TextBox3.Text = DA.GetValue(4)
TextBox14.Text = DA.GetValue(5)
TextBox5.Text = DA.GetValue(6)
TextBox4.Text = DA.GetValue(7)
TextBox6.Text = DA.GetValue(8)
End While
con1.Close()
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim AQTY As Integer
Try
Dim conn As New SqlConnection(My.Settings.LIBRARY)
Dim comd As New SqlCommand("SELECT available_qty FROM addbook where accession_no=@acno", conn)
conn.Open()
comd.Parameters.AddWithValue("@acno", SqlDbType.NVarChar).Value = TextBox14.Text
Dim DA As SqlDataReader = comd.ExecuteReader
While (DA.Read())
AQTY = DA.GetValue(0)
End While
conn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
Try
Dim con As New SqlConnection(My.Settings.LIBRARY)
Dim cmd As New SqlCommand("UPDATE ISSUE SET status=@status WHERE TRANSACTION_ID=@ID", con)
cmd.Parameters.Add("@status", SqlDbType.NVarChar).Value = "Returned"
cmd.Parameters.Add("@ID", SqlDbType.NVarChar).Value = ComboBox1.Text
con.Open()
If cmd.ExecuteNonQuery() = 1 Then
AQTY += 1
Dim cmd1 As New SqlCommand("UPDATE addbook SET Available_Qty = @aqty WHERE Accession_no =@Accession_no", con)
cmd1.Parameters.Add("@aqty", SqlDbType.NVarChar).Value = AQTY
cmd1.Parameters.Add("@Accession_no", SqlDbType.NVarChar).Value = TextBox14.Text
cmd1.ExecuteNonQuery()
MsgBox("Book Returned Successfully")
Else
MsgBox("Some Error Occurs! Please Try Again Later", MsgBoxStyle.Critical)
End If
con.Dispose()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
Try
Dim con As New SqlConnection(My.Settings.LIBRARY)
Dim cmd As New SqlCommand("SELECT Transaction_id FROM issue", con)
Dim adapter As New SqlDataAdapter(cmd)
Dim table As New DataTable()
adapter.Fill(table)
ComboBox1.DataSource = table
ComboBox1.DisplayMember = "Transaction_id"
ComboBox1.ValueMember = "Transaction_id"
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Private Sub Button10_Click(sender As Object, e As EventArgs) Handles Button10.Click
Dim con1 As New SqlConnection(My.Settings.LIBRARY)
Dim cmd1 As New SqlCommand("SELECT [Issue],[Return_Date],[Staff_id],[Staff_Name],[Department],[Accession_no],[Book_Title],[Author],[Edition] FROM staffissue where Transaction_id=@ID", con1)
con1.Open()
cmd1.Parameters.AddWithValue("@ID", SqlDbType.NVarChar).Value = ComboBox2.Text
Dim DA As SqlDataReader = cmd1.ExecuteReader
While (DA.Read())
DateTimePicker4.Text = DA.GetValue(0)
DateTimePicker3.Text = DA.GetValue(1)
TextBox1.Text = DA.GetValue(2)
TextBox11.Text = DA.GetValue(3)
TextBox10.Text = DA.GetValue(4)
TextBox12.Text = DA.GetValue(5)
TextBox9.Text = DA.GetValue(6)
TextBox8.Text = DA.GetValue(7)
TextBox7.Text = DA.GetValue(8)
End While
con1.Close()
End Sub
Private Sub Button8_Click(sender As Object, e As EventArgs) Handles Button8.Click
Dim AQTY As Integer
Try
Dim conn As New SqlConnection(My.Settings.LIBRARY)
Dim comd As New SqlCommand("SELECT available_qty FROM addbook where accession_no=@acno", conn)
conn.Open()
comd.Parameters.AddWithValue("@acno", SqlDbType.NVarChar).Value = TextBox12.Text
Dim DA As SqlDataReader = comd.ExecuteReader
While (DA.Read())
AQTY = DA.GetValue(0)
End While
conn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
Try
Dim con As New SqlConnection(My.Settings.LIBRARY)
Dim cmd As New SqlCommand("DELETE FROM staffISSUE WHERE TRANSACTION_ID=@ID", con)
cmd.Parameters.Add("@ID", SqlDbType.NVarChar).Value = ComboBox2.Text
con.Open()
If cmd.ExecuteNonQuery() = 1 Then
AQTY += 1
Dim cmd1 As New SqlCommand("UPDATE addbook SET Available_Qty = @aqty WHERE Accession_no =@Accession_no", con)
cmd1.Parameters.Add("@aqty", SqlDbType.NVarChar).Value = AQTY
cmd1.Parameters.Add("@Accession_no", SqlDbType.NVarChar).Value = TextBox12.Text
cmd1.ExecuteNonQuery()
MsgBox("Book Returned Successfully")
Else
MsgBox("Some Error Occurs! Please Try Again Later", MsgBoxStyle.Critical)
End If
con.Dispose()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
Try
Dim con As New SqlConnection(My.Settings.LIBRARY)
Dim cmd As New SqlCommand("SELECT Transaction_id FROM issue", con)
Dim adapter As New SqlDataAdapter(cmd)
Dim table As New DataTable()
adapter.Fill(table)
ComboBox1.DataSource = table
ComboBox1.DisplayMember = "Transaction_id"
ComboBox1.ValueMember = "Transaction_id"
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
TextBox1.Text = ""
TextBox11.Text = ""
TextBox10.Text = ""
TextBox12.Text = ""
TextBox9.Text = ""
TextBox8.Text = ""
TextBox7.Text = ""
ComboBox2.Text = ""
DateTimePicker4.ResetText()
DateTimePicker3.ResetText()
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
TextBox13.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox6.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox14.Text = ""
ComboBox1.Text = ""
DateTimePicker1.ResetText()
DateTimePicker2.ResetText()
End Sub
End Class