1 Imports System.Data.SqlClient
2 Imports System.IO
3
4 Public Class frmBookIssueRecord_Student
5 Public Sub GetData()
6 Try
7 con = New SqlConnection(cs)
8 con.Open()
9 cmd = New SqlCommand("Select RTRIM(BookIssue_Student.ID) as [ID],RTRIM(Book.AccessionNo) as [Accession No],RTRIM(BookTitle) as [Book Title],RTRIM(Author) as [Author],RTRIM(JointAuthors) as [Joint Authors],RTRIM(Student.AdmissionNo) as [Admission No],RTRIM(StudentName) as [Student Name],RTRIM(Classname) as [Class],IssueDate as [Issue Date],DueDate as [Due Date], RTRIM(BookIssue_Student.Status) as [Status], RTRIM(BookIssue_Student.Remarks) as [Remarks] from Book,BookIssue_Student,Student,Class,Section where Book.AccessionNo=BookIssue_Student.AccessionNo and BookIssue_Student.AdmissionNo=Student.AdmissionNo and Section.Class=Class.ClassName and Section.ID=Student.SectionID order by IssueDate desc", con)
10 adp = New SqlDataAdapter(cmd)
11 ds = New DataSet()
12 adp.Fill(ds, "BookIssue_Student")
13 dgw.DataSource = ds.Tables("BookIssue_Student").DefaultView
14 con.Close()
15 Catch ex As Exception
16 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
17 End Try
18 End Sub
19
20 Sub Reset()
21 txtStudentName.Text = ""
22 dtpDateTo.Value = Today
23 dtpdateFrom.Value = Today
24 DateTimePicker1.Text = Today
25 DateTimePicker2.Text = Today
26 GetData()
27 End Sub
28 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
29 Reset()
30 End Sub
31
32 Private Sub frmStudentRecord_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
33 GetData()
34 End Sub
35
36 Private Sub btnExportExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnExportExcel.Click
37 ExportExcel(dgw)
38 End Sub
39
40 Private Sub dgw_MouseClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
41 Try
42 If dgw.Rows.Count > 0 Then
43 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
44 If lblSet.Text = "Book Issue" Then
45 Me.Hide()
46 frmBookIssue.Show()
47 ' or simply use column name instead of index
48 'dr.Cells["id"].Value.ToString();
49 'JM_Name, SubscriptionNo, SubscriptionDate, Subscription, SubscriptionDateFrom, SubscriptionDateTo, BillNo, BillDate, Amount, PaidOn, IssueNo,IssueDate, Months, Jm_Year, Volume, V_num, DateOfReceipt, SupplierName, Department, Remarks
50 frmBookIssue.txtID.Text = dr.Cells(0).Value.ToString()
51 frmBookIssue.txtAccessionNo.Text = dr.Cells(1).Value.ToString()
52 frmBookIssue.txtBookTitle.Text = dr.Cells(2).Value.ToString()
53 frmBookIssue.txtAuthor.Text = dr.Cells(3).Value.ToString()
54 frmBookIssue.txtJointAuthor.Text = dr.Cells(4).Value.ToString()
55 frmBookIssue.txtAdmissionNo.Text = dr.Cells(5).Value.ToString()
56 frmBookIssue.txtStudentName.Text = dr.Cells(6).Value.ToString()
57 frmBookIssue.txtClass.Text = dr.Cells(7).Value.ToString()
58 frmBookIssue.dtpIssueDate.Text = dr.Cells(8).Value.ToString()
59 frmBookIssue.dtpDueDate.Text = dr.Cells(9).Value.ToString()
60 frmBookIssue.txtStatus.Text = dr.Cells(10).Value.ToString()
61 frmBookIssue.txtRemarks.Text = dr.Cells(11).Value.ToString()
62 frmBookIssue.btnUpdate.Enabled = True
63 frmBookIssue.btnDelete.Enabled = True
64 frmBookIssue.btnSave.Enabled = False
65 frmBookIssue.Button1.Enabled = False
66 frmBookIssue.Button2.Enabled = False
67 frmBookIssue.FillData()
68 Me.lblSet.Text = ""
69 End If
70 End If
71 Catch ex As Exception
72 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
73 End Try
74 End Sub
75
76 Private Sub dgw_RowPostPaint(sender As Object, e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
77 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
78 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
79 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
80 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
81 End If
82 Dim b As Brush = SystemBrushes.ControlText
83 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
84
85 End Sub
86
87 Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
88 Me.Close()
89 End Sub
90
91
92 Private Sub txtName_TextChanged(sender As System.Object, e As System.EventArgs)
93
94 End Sub
95
96 Private Sub txtStudentName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtStudentName.TextChanged
97 Try
98 con = New SqlConnection(cs)
99 con.Open()
100 cmd = New SqlCommand("Select RTRIM(BookIssue_Student.ID) as [ID],RTRIM(Book.AccessionNo) as [Accession No],RTRIM(BookTitle) as [Book Title],RTRIM(Author) as [Author],RTRIM(JointAuthors) as [Joint Authors],RTRIM(Student.AdmissionNo) as [Admission No],RTRIM(StudentName) as [Student Name],RTRIM(Classname) as [Class],IssueDate as [Issue Date],DueDate as [Due Date], RTRIM(BookIssue_Student.Status) as [Status], RTRIM(BookIssue_Student.Remarks) as [Remarks] from Book,BookIssue_Student,Student,Class,Section where Book.AccessionNo=BookIssue_Student.AccessionNo and BookIssue_Student.AdmissionNo=Student.AdmissionNo and Section.Class=Class.ClassName and Section.ID=Student.SectionID and StudentName like '%" & txtStudentName.Text & "%' order by IssueDate desc", con)
101 adp = New SqlDataAdapter(cmd)
102 ds = New DataSet()
103 adp.Fill(ds, "BookIssue_Student")
104 dgw.DataSource = ds.Tables("BookIssue_Student").DefaultView
105 con.Close()
106 Catch ex As Exception
107 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
108 End Try
109 End Sub
110
111 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
112 Try
113 con = New SqlConnection(cs)
114 con.Open()
115 cmd = New SqlCommand("Select RTRIM(BookIssue_Student.ID) as [ID],RTRIM(Book.AccessionNo) as [Accession No],RTRIM(BookTitle) as [Book Title],RTRIM(Author) as [Author],RTRIM(JointAuthors) as [Joint Authors],RTRIM(Student.AdmissionNo) as [Admission No],RTRIM(StudentName) as [Student Name],RTRIM(Classname) as [Class],IssueDate as [Issue Date],DueDate as [Due Date], RTRIM(BookIssue_Student.Status) as [Status], RTRIM(BookIssue_Student.Remarks) as [Remarks] from Book,BookIssue_Student,Student,Class,Section where Book.AccessionNo=BookIssue_Student.AccessionNo and BookIssue_Student.AdmissionNo=Student.AdmissionNo and Section.Class=Class.ClassName and Section.ID=Student.SectionID and IssueDate between @d1 and @d2 order by IssueDate desc", con)
116 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpdateFrom.Value.Date
117 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
118 adp = New SqlDataAdapter(cmd)
119 ds = New DataSet()
120 adp.Fill(ds, "BookIssue_Student")
121 dgw.DataSource = ds.Tables("BookIssue_Student").DefaultView
122 con.Close()
123 Catch ex As Exception
124 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
125 End Try
126 End Sub
127
128 Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
129 Try
130 con = New SqlConnection(cs)
131 con.Open()
132 cmd = New SqlCommand("Select RTRIM(BookIssue_Student.ID) as [ID],RTRIM(Book.AccessionNo) as [Accession No],RTRIM(BookTitle) as [Book Title],RTRIM(Author) as [Author],RTRIM(JointAuthors) as [Joint Authors],RTRIM(Student.AdmissionNo) as [Admission No],RTRIM(StudentName) as [Student Name],RTRIM(Classname) as [Class],IssueDate as [Issue Date],DueDate as [Due Date], RTRIM(BookIssue_Student.Status) as [Status], RTRIM(BookIssue_Student.Remarks) as [Remarks] from Book,BookIssue_Student,Student,Class,Section where Book.AccessionNo=BookIssue_Student.AccessionNo and BookIssue_Student.AdmissionNo=Student.AdmissionNo and Section.Class=Class.ClassName and Section.ID=Student.SectionID and DueDate between @d1 and @d2 order by DueDate", con)
133 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = DateTimePicker2.Value.Date
134 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = DateTimePicker1.Value.Date
135 adp = New SqlDataAdapter(cmd)
136 ds = New DataSet()
137 adp.Fill(ds, "BookIssue_Student")
138 dgw.DataSource = ds.Tables("BookIssue_Student").DefaultView
139 con.Close()
140 Catch ex As Exception
141 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
142 End Try
143 End Sub
144
145 Private Sub dtpDateTo_Validating(sender As System.Object, e As System.ComponentModel.CancelEventArgs) Handles dtpDateTo.Validating
146 If (dtpdateFrom.Value.Date) > (dtpDateTo.Value.Date) Then
147 MessageBox.Show("Invalid Selection", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
148 dtpDateTo.Focus()
149 End If
150 End Sub
151
152 Private Sub DateTimePicker1_Validating(sender As System.Object, e As System.ComponentModel.CancelEventArgs) Handles DateTimePicker1.Validating
153 If (DateTimePicker2.Value.Date) > (DateTimePicker1.Value.Date) Then
154 MessageBox.Show("Invalid Selection", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
155 DateTimePicker1.Focus()
156 End If
157 End Sub
158 End Class
