Private Sub btnDisplay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisplay.Click
SuspendLayout()
cnn.ConnectionString = "Data Source=.\sqlexpress;Initial Catalog=MYDB;Integrated Security=True"
If cnn.State = ConnectionState.Closed Then cnn.Open()
cmdGrade = cnn.CreateCommand
cmdGrade.CommandText = "SELECT DISTINCT * FROM tblEnrol WHERE SectionName = '" & cboSection.Text & "' AND SubjectName = '" & cboSubjectName.Text & "' AND YearLevel = '" & cboYearLevel.Text & "'"
daGrade.MissingSchemaAction = MissingSchemaAction.AddWithKey
dsGrade.Clear()
'dsSection.Clear()
'dsSubject.Clear()
daGrade.SelectCommand = cmdGrade
daGrade.Fill(dsGrade, "tblEnrol")
cnn.Close()
Dim si() As FarPoint.Win.Spread.SortInfo = New FarPoint.Win.Spread.SortInfo() {New FarPoint.Win.Spread.SortInfo(1, True), New FarPoint.Win.Spread.SortInfo(2, True)}
''HideSheets()
'bind fpspread1 to datasource
'1st Grading
FpSpread1.Sheets(0).DataSource = dsGrade
FpSpread1.Sheets(0).DataMember = "tblEnrol"
'Create CellType and specify number decimal for 1st grading sheet
With FpSpread1.Sheets(0)
.DataAutoCellTypes = False
Dim num As New FarPoint.Win.Spread.CellType.NumberCellType
num.DecimalPlaces = 0
.Columns(12, 81).CellType = num 'column 12 to 81
.Columns(84, 85).CellType = num
.Columns(88, 89).CellType = num
.Columns(92, 93).CellType = num
.Columns(96, 97).CellType = num
.Columns(100, 101).CellType = num
.Columns(104, 105).CellType = num
.Columns(109).CellType = num
'sort multi-column
.SortRows(0, FpSpread1.Sheets(0).RowCount, si)
End With
FormatSpread()
'bind FpSpread1.Sheets(1) to datasource
'2nd Grading
FpSpread1.Sheets(1).DataSource = dsGrade
FpSpread1.Sheets(1).DataMember = "tblEnrol"
'Create CellType and specify number decimal for 1st grading sheet
With FpSpread1.Sheets(1)
.DataAutoCellTypes = False
Dim num As New FarPoint.Win.Spread.CellType.NumberCellType
num.DecimalPlaces = 0
'for 2nd grading cell type
.Columns(135, 204).CellType = num 'Assignment to Total Score (A)
.Columns(207, 208).CellType = num 'Total Quiz to Total Score (Q)
.Columns(211, 212).CellType = num 'Total Rec to Total Score (R)
.Columns(215, 216).CellType = num 'Total Proj to Total Score (P)
.Columns(219, 220).CellType = num 'Total Speech to Total Score (SP)
.Columns(223, 224).CellType = num 'Total Reading to Total Score (RDG)
.Columns(227, 228).CellType = num 'Total Exam to Total Score (E)
.Columns(232).CellType = num 'Average
End With
FormatSpread1()
End Sub