i'm struggling on how to solve my problem with print preview with my datagridview. this is the whole code:
Imports System.Data.SqlClient
Public Class FrmPrintClassSchedule
Dim strConn As String = "Data Source=Jansen;Initial Catalog=SLCBRegistrarDB;Integrated Security=True"
Dim sqlCon As SqlConnection = New SqlConnection(strConn)
Dim CMD As SqlCommand
Dim Adapter As SqlDataAdapter
Dim Reader As SqlDataReader
Dim DT As DataTable
Private Sub LoadCLASSSCHEDULES()
sqlCon = New SqlConnection("Data Source=Jansen;Initial Catalog=SLCBRegistrarDB;Integrated Security=True")
sqlCon.Open()
CMD = sqlCon.CreateCommand
CMD.CommandText = ("SELECT [Class Schedule LINE].SchedID, ListofSubjects.[Course No.], ListofSubjects.[Descriptive Title],Curriculum.[Lab.]+ Curriculum.[Lec.] , UtlyTIMETable.[FROM Time]+'-'+ UtlyTIMETable.[TO Time]+' '+ UtlyDAY.Day+' '+ UtlyRoom.Building+' '+UtlyRoom.[Room No.], UtlyInstructor.[Last Name]+', '+ UtlyInstructor.[First Name], YearLevel.[Year Level], CourseOfferings.Course, CourseOfferings.[Degree Course], ListCurricularYear.[Curriculum Year], [Class Schedule Status].[Class Schedule Status] " &
"FROM [Class Schedule LINE] INNER JOIN Curriculum ON [Class Schedule LINE].[Subject Code] = Curriculum.[Subject Code] INNER JOIN CourseOfferings ON Curriculum.CourseID = CourseOfferings.CourseID INNER JOIN ListofSubjects ON Curriculum.SubjectID = ListofSubjects.SubjectID INNER JOIN Section ON [Class Schedule LINE].Section = Section.SectionID INNER JOIN UtlyDAY ON [Class Schedule LINE].DayID = UtlyDAY.DayID INNER JOIN UtlyInstructor ON [Class Schedule LINE].InstructorID = UtlyInstructor.IntructorID INNER JOIN UtlyRoom ON [Class Schedule LINE].RoomID = UtlyRoom.RoomID INNER JOIN UtlyTIMETable ON [Class Schedule LINE].TimeID = UtlyTIMETable.TimeID INNER JOIN YearLevel ON Curriculum.YearLevelID = YearLevel.[Year Level] INNER JOIN ListCurricularYear ON Curriculum.CurriculumID = ListCurricularYear.CurriculumID INNER JOIN [Class Schedule Status] ON [Class Schedule LINE].StatusofSchedule = [Class Schedule Status].StatusofClassScheduleID " &
"WHERE (Section.Section = '" & cmbSection.Text & "') AND (YearLevel.[Year Level] = '" & txtYearLevel.Text & "') AND (CourseOfferings.Course = '" & cmbCOURSE.Text & "') AND (CourseOfferings.[Degree Course] = '" & cmbDegreeTitle.Text & "') AND (ListCurricularYear.[Curriculum Year] = '" & cmbCurricularyear.Text & "') AND ([Class Schedule Status].[Class Schedule Status] = 'Active')")
Reader = CMD.ExecuteReader()
If Reader.HasRows Then
Dim DT As New DataTable
DT.Load(Reader)
dgvPrintClassSchedule.DataSource = DT
dgvPrintClassSchedule.Columns(6).Visible = False
dgvPrintClassSchedule.Columns(7).Visible = False
dgvPrintClassSchedule.Columns(8).Visible = False
dgvPrintClassSchedule.Columns(9).Visible = False
dgvPrintClassSchedule.Columns(10).Visible = False
dgvPrintClassSchedule.RowHeadersWidth = 25
dgvPrintClassSchedule.Columns(0).Width = 75
dgvPrintClassSchedule.Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
dgvPrintClassSchedule.Columns(0).HeaderText = "Schedule No."
dgvPrintClassSchedule.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
dgvPrintClassSchedule.Columns(3).HeaderText = "Units"
dgvPrintClassSchedule.Columns(4).HeaderText = "Schedule and Room Assignment"
dgvPrintClassSchedule.Columns(5).HeaderText = "Instructor"
dgvPrintClassSchedule.Columns(1).Width = 100
dgvPrintClassSchedule.Columns(3).Width = 50
dgvPrintClassSchedule.Columns(2).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
dgvPrintClassSchedule.Columns(4).Width = 200
dgvPrintClassSchedule.Columns(5).Width = 150
dgvPrintClassSchedule.Columns(5).DefaultCellStyle.Font = New Font("Calibri", 9)
End If
Reader.Close()
sqlCon.Close()
End Sub
Private Sub LoadCURRICULUM()
Try
sqlCon.Open()
Dim QUERY As String
QUERY = "SELECT * FROM ListCurricularYear"
CMD = New SqlCommand(QUERY, sqlCon)
Reader = CMD.ExecuteReader
While Reader.Read
cmbCurricularyear.Items.Add(Reader.GetString(1))
End While
sqlCon.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
Private Sub LoadCOURSES()
Try
sqlCon.Open()
Dim QUERY As String
QUERY = "SELECT Course, [Degree Course] FROM CourseOfferings"
CMD = New SqlCommand(QUERY, sqlCon)
Reader = CMD.ExecuteReader
While Reader.Read
cmbCOURSE.Items.Add(Reader.GetString(0))
cmbDegreeTitle.Items.Add(Reader.GetString(1))
End While
sqlCon.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
Private Sub cmbCOURSE_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbCOURSE.SelectedIndexChanged
Try
sqlCon.Open()
Dim QUERY As String
QUERY = "SELECT * FROM CourseOfferings WHERE Course='" & cmbCOURSE.Text & "'"
CMD = New SqlCommand(QUERY, sqlCon)
Reader = CMD.ExecuteReader
While Reader.Read
cmbDegreeTitle.Text = Reader.GetString(2)
End While
sqlCon.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
Private Sub cmbSection_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbSection.SelectedIndexChanged
LoadCLASSSCHEDULES()
End Sub
''' <summary>
''' structire to hold printed page details
''' </summary>
''' <remarks></remarks>
Private Structure pageDetails
Dim columns As Integer
Dim rows As Integer
Dim startCol As Integer
Dim startRow As Integer
End Structure
''' <summary>
''' dictionary to hold printed page details, with index key
''' </summary>
''' <remarks></remarks>
Private pages As Dictionary(Of Integer, pageDetails)
Dim maxPagesWide As Integer
Dim maxPagesTall As Integer
''' <summary>
''' this just loads some text values into the dgv
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub FrmPrintClassSchedule_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'LoadCLASSSCHEDULES()
LoadCURRICULUM()
LoadCOURSES()
'dgvPrintClassSchedule.RowHeadersWidth = CInt(dgvPrintClassSchedule.RowHeadersWidth * 1.35)
'For r As Integer = 1 To 100
'Dim y As Integer = r
'Dim fmt As String = "R{0}C{1}"
'dgvPrintClassSchedule.Rows.Add()
'dgvPrintClassSchedule.Rows(r - 1).SetValues(Enumerable.Range(1, 10).Select(Function(x) String.Format(fmt, y, x)).ToArray)
'dgvPrintClassSchedule.Rows(r - 1).HeaderCell.Value = r.ToString
'Next
End Sub
''' <summary>
''' shows a PrintPreviewDialog
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub btnPreview_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrintPreview.Click
Dim ppd As New PrintPreviewDialog
ppd.Document = PrintDocument1
ppd.WindowState = FormWindowState.Maximized
ppd.ShowDialog()
End Sub
''' <summary>
''' starts print job
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub btnPrint_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPrint.Click
PrintDocument1.Print()
End Sub
''' <summary>
''' the majority of this Sub is calculating printed page ranges
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub PrintDocument1_BeginPrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs) Handles PrintDocument1.BeginPrint
''this removes the printed page margins
PrintDocument1.OriginAtMargins = True
PrintDocument1.DefaultPageSettings.Margins = New Drawing.Printing.Margins(0, 0, 0, 0)
pages = New Dictionary(Of Integer, pageDetails)
Dim maxWidth As Integer = CInt(PrintDocument1.DefaultPageSettings.PrintableArea.Width) - 40
Dim maxHeight As Integer = CInt(PrintDocument1.DefaultPageSettings.PrintableArea.Height) - 40 + Label1.Height
Dim pageCounter As Integer = 0
pages.Add(pageCounter, New pageDetails)
Dim columnCounter As Integer = 0
Dim columnSum As Integer = dgvPrintClassSchedule.RowHeadersWidth
For c As Integer = 0 To dgvPrintClassSchedule.Columns.Count - 1
If columnSum + dgvPrintClassSchedule.Columns(c).Width < maxWidth Then
columnSum += dgvPrintClassSchedule.Columns(c).Width
columnCounter += 1
Else
pages(pageCounter) = New pageDetails With {.columns = columnCounter, .rows = 0, .startCol = pages(pageCounter).startCol}
columnSum = dgvPrintClassSchedule.RowHeadersWidth + dgvPrintClassSchedule.Columns(c).Width
columnCounter = 1
pageCounter += 1
pages.Add(pageCounter, New pageDetails With {.startCol = c})
End If
If c = dgvPrintClassSchedule.Columns.Count - 1 Then
If pages(pageCounter).columns = 0 Then
pages(pageCounter) = New pageDetails With {.columns = columnCounter, .rows = 0, .startCol = pages(pageCounter).startCol}
End If
End If
Next
maxPagesWide = pages.Keys.Max + 1
pageCounter = 0
Dim rowCounter As Integer = 0
Dim rowSum As Integer = dgvPrintClassSchedule.ColumnHeadersHeight
For r As Integer = 0 To dgvPrintClassSchedule.Rows.Count - 2
If rowSum + dgvPrintClassSchedule.Rows(r).Height < maxHeight Then
rowSum += dgvPrintClassSchedule.Rows(r).Height
rowCounter += 1
Else
pages(pageCounter) = New pageDetails With {.columns = pages(pageCounter).columns, .rows = rowCounter, .startCol = pages(pageCounter).startCol, .startRow = pages(pageCounter).startRow}
For x As Integer = 1 To maxPagesWide - 1
pages(pageCounter + x) = New pageDetails With {.columns = pages(pageCounter + x).columns, .rows = rowCounter, .startCol = pages(pageCounter + x).startCol, .startRow = pages(pageCounter).startRow}
Next
pageCounter += maxPagesWide
For x As Integer = 0 To maxPagesWide - 1
pages.Add(pageCounter + x, New pageDetails With {.columns = pages(x).columns, .rows = 0, .startCol = pages(x).startCol, .startRow = r})
Next
rowSum = dgvPrintClassSchedule.ColumnHeadersHeight + dgvPrintClassSchedule.Rows(r).Height
rowCounter = 1
End If
If r = dgvPrintClassSchedule.Rows.Count - 2 Then
For x As Integer = 0 To maxPagesWide - 1
If pages(pageCounter + x).rows = 0 Then
pages(pageCounter + x) = New pageDetails With {.columns = pages(pageCounter + x).columns, .rows = rowCounter, .startCol = pages(pageCounter + x).startCol, .startRow = pages(pageCounter + x).startRow}
End If
Next
End If
Next
maxPagesTall = pages.Count \ maxPagesWide
End Sub
''' <summary>
''' this is the actual printing routine.
''' using the pagedetails i calculated earlier, it prints a title,
''' + as much of the datagridview as will fit on 1 page, then moves to the next page.
''' this is setup to be dynamic. try resizing the dgv columns or rows
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub PrintDocument1_PrintPage(ByVal sender As System.Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs) Handles PrintDocument1.PrintPage
Dim rect As New Rectangle(20, 20, CInt(PrintDocument1.DefaultPageSettings.PrintableArea.Width), Label1.Height)
Dim sf As New StringFormat
sf.Alignment = StringAlignment.Center
sf.LineAlignment = StringAlignment.Center
e.Graphics.DrawString(Label1.Text, Label1.Font, Brushes.Black, rect, sf)
sf.Alignment = StringAlignment.Near
Dim startX As Integer = 50
Dim startY As Integer = rect.Bottom
Static startPage As Integer = 0
For p As Integer = startPage To pages.Count - 1
Dim cell As New Rectangle(startX, startY, dgvPrintClassSchedule.RowHeadersWidth, dgvPrintClassSchedule.ColumnHeadersHeight)
e.Graphics.FillRectangle(New SolidBrush(SystemColors.ControlLight), cell)
e.Graphics.DrawRectangle(Pens.Black, cell)
startY += dgvPrintClassSchedule.ColumnHeadersHeight
For r As Integer = pages(p).startRow To pages(p).startRow + pages(p).rows - 1
cell = New Rectangle(startX, startY, dgvPrintClassSchedule.RowHeadersWidth, dgvPrintClassSchedule.Rows(r).Height)
e.Graphics.FillRectangle(New SolidBrush(SystemColors.ControlLight), cell)
e.Graphics.DrawRectangle(Pens.Black, cell)
e.Graphics.DrawString(dgvPrintClassSchedule.Rows(r).HeaderCell.Value.ToString, dgvPrintClassSchedule.Font, Brushes.Black, cell, sf)
startY += dgvPrintClassSchedule.Rows(r).Height
Next
startX += cell.Width
startY = rect.Bottom
For c As Integer = pages(p).startCol To pages(p).startCol + pages(p).columns - 1
cell = New Rectangle(startX, startY, dgvPrintClassSchedule.Columns(c).Width, dgvPrintClassSchedule.ColumnHeadersHeight)
e.Graphics.FillRectangle(New SolidBrush(SystemColors.ControlLight), cell)
e.Graphics.DrawRectangle(Pens.Black, cell)
e.Graphics.DrawString(dgvPrintClassSchedule.Columns(c).HeaderCell.Value.ToString, dgvPrintClassSchedule.Font, Brushes.Black, cell, sf)
startX += dgvPrintClassSchedule.Columns(c).Width
Next
startY = rect.Bottom + dgvPrintClassSchedule.ColumnHeadersHeight
For r As Integer = pages(p).startRow To pages(p).startRow + pages(p).rows - 1
startX = 50 + dgvPrintClassSchedule.RowHeadersWidth
For c As Integer = pages(p).startCol To pages(p).startCol + pages(p).columns - 1
cell = New Rectangle(startX, startY, dgvPrintClassSchedule.Columns(c).Width, dgvPrintClassSchedule.Rows(r).Height)
e.Graphics.DrawRectangle(Pens.Black, cell)
e.Graphics.DrawString(dgvPrintClassSchedule(c, r).Value.ToString, dgvPrintClassSchedule.Font, Brushes.Black, cell, sf)
startX += dgvPrintClassSchedule.Columns(c).Width
Next
startY += dgvPrintClassSchedule.Rows(r).Height
Next
If p <> pages.Count - 1 Then
startPage = p + 1
e.HasMorePages = True
Return
Else
startPage = 0
End If
Next
End Sub
End Class
After data are loaded in the datagridview, then when i click print preview button, it should be working but this error always comes out:
Object reference not set to an instance of an object. Error Code:
e.Graphics.DrawString(dgvPrintClassSchedule.Rows(r).HeaderCell.Value.ToString, dgvPrintClassSchedule.Font, Brushes.Black, cell, sf)
I can't find a way on how to get rid of this error.. please help..Thanks
