Visual Basic

i am using vb.net and mysql database and creating crystal report using xml file.


<pre lang="VB.NET">
Private Sub ToolStripButton1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
        conn.ConnectionString = &quot;server=localhost;userid=root;password=;database=amwps&quot;

        If ToolStripComboBox1.SelectedItem = &quot;Employee Information&quot; Then
            Dim daset As New DataSet
            strSql = &quot;SELECT * FROM tbl_employeeinfo&quot;
            conn.Open()
            dadapter.SelectCommand = New MySqlCommand(strSql, conn)
            dadapter.Fill(daset)
            daset.WriteXml(CurDir() &amp; &quot;\xmlEmpSummary.xml&quot;, XmlWriteMode.WriteSchema)
            conn.Close()
        ElseIf ToolStripComboBox1.SelectedItem = &quot;Attendance&quot; Then
            Dim daset As New DataSet
            strSql = &quot;SELECT * FROM tbl_attendance where dates = &#39;&quot; &amp; ToolStripTextBox1.Text &amp; &quot;&#39;&quot;
            conn.Open()
            dadapter.SelectCommand = New MySqlCommand(strSql, conn)
            dadapter.Fill(daset)
            daset.WriteXml(CurDir() &amp; &quot;\xmlAttSummary.xml&quot;, XmlWriteMode.WriteSchema)
            conn.Close()
        ElseIf ToolStripComboBox1.SelectedItem = &quot;Payroll&quot; Then
            Dim daset As New DataSet
            strSql = &quot;SELECT * FROM tbl_payroll where empNumber = &#39;&quot; &amp; ToolStripTextBox1.Text &amp; &quot;&#39;&quot;
            conn.Open()
            dadapter.SelectCommand = New MySqlCommand(strSql, conn)
            dadapter.Fill(daset)
            daset.WriteXml(CurDir() &amp; &quot;\xmlPayrollSummary.xml&quot;, XmlWriteMode.WriteSchema)
            conn.Close()
        Else
            MsgBox(&quot;Choose Filter!&quot;, MsgBoxStyle.Information, &quot;Summary Report&quot;)
        End If
    End Sub</pre>

#Region "Public Function SelectAttendanceLetters(ByVal schoolid As Integer, ByVal schoolyear As Integer) As DataTable"
    Public Function SelectAttendanceLetters(ByVal schoolid As Integer, ByVal schoolyear As Integer, ByVal milestone As Integer, ByVal term As Integer) As DataTable
        Dim dt As DataTable = New DataTable()

        'Const sql As String = "SELECT alm.SCHOOLYEAR, RTRIM(als.PERMNUM) AS PERMNUM, alm.SCHOOLNUM, alm.STULINK, alm.MILESTONE_CODE " _
        '    & ", RTRIM(als.LASTNAME) + ', ' + RTRIM(als.FIRSTNAME) + CASE WHEN MIDDLENAME IS NULL THEN '' WHEN RTRIM(MIDDLENAME) = '' THEN '' ELSE ' ' + SUBSTRING(RTRIM(MIDDLENAME), 1, 1) END AS STUDENTNAME " _
        '    & ", als.GRADE , alm.MILESTONE_DATE, alm.ABSENCES, alm.TARDIES, als.HOMELNGCOR, alm.SEMESTER " _
        '    & "FROM AtnLtrMilestone alm INNER JOIN AtnLtrASTU als ON alm.STULINK = als.STULINK " _
        '    & "WHERE alm.SCHOOLNUM = @schoolnum " _
        '    & "AND alm.SCHOOLYEAR = @schoolyear " _
        '    & "AND alm.MILESTONE_CODE = @milestone " _
        '    & "AND alm.SEMESTER = @semester " _
        '    & "AND (alm.PRINTED <> 'Y' OR alm.PRINTED IS NULL) " _
        '    & "AND (alm.PRINTED <> 'N' OR alm.PRINTED IS NULL) " _
        '    & "ORDER BY als.HOMELNGCOR, alm.MILESTONE_DATE, als.LASTNAME, als.FIRSTNAME, MIDDLENAME; "

        Const sql As String = ";WITH CTE_ASTU (SCHOOLYEAR,PERMNUM,SCHOOLNUM,STULINK,MILESTONE_CODE,STUDENTNAME,LASTNAME,FIRSTNAME,MIDDLENAME,GRADE,MILESTONE_DATE,ABSENCES,TARDIES,HOMELNGCOR,SEMESTER)  AS ( " _
          & "SELECT alm.SCHOOLYEAR as SCHOOLYEAR, RTRIM(als.PERMNUM) AS PERMNUM, alm.SCHOOLNUM as SCHOOLNUM, alm.STULINK as STULINK, alm.MILESTONE_CODE as MILESTONE_CODE " _
          & ", RTRIM(als.LASTNAME) + ', ' + RTRIM(als.FIRSTNAME) + CASE WHEN MIDDLENAME IS NULL THEN '' WHEN RTRIM(MIDDLENAME) = '' THEN '' ELSE ' ' + SUBSTRING(RTRIM(MIDDLENAME), 1, 1) END AS STUDENTNAME " _
          & ", als.LASTNAME as LASTNAME,als.FIRSTNAME as FIRSTNAME, CASE WHEN MIDDLENAME IS NULL THEN '' ELSE MIDDLENAME END AS MIDDLENAME " _
          & ", als.GRADE as GRADE , alm.MILESTONE_DATE as MILESTONE_DATE, alm.ABSENCES as ABSENCES, alm.TARDIES as TARDIES, als.HOMELNGCOR as HOMELNGCOR, alm.SEMESTER as SEMESTER" _
          & "FROM AtnLtrMilestone alm INNER JOIN AtnLtrASTU als ON alm.STULINK = als.STULINK " _
          & "WHERE alm.SCHOOLNUM = @schoolnum " _
          & "AND alm.SCHOOLYEAR = @schoolyear " _
          & "AND alm.MILESTONE_CODE = @milestone " _
          & "AND alm.SEMESTER = @semester " _
          & "AND (alm.PRINTED <> 'Y' OR alm.PRINTED IS NULL) " _
          & "AND (alm.PRINTED <> 'N' OR alm.PRINTED IS NULL) " _
          & ") " _
          & "SELECT * " _
          & " FROM CTE_ASTU " _
          & "ORDER BY HOMELNGCOR, MILESTONE_DATE, LASTNAME, FIRSTNAME, MIDDLENAME; "

     

        Using con As SqlConnection = DB.OpenConnectionCampusOPS()
            Using da As SqlDataAdapter = New SqlDataAdapter(sql, con)

                Try
                    da.SelectCommand.Parameters.Add("@schoolnum", SqlDbType.Int).Value = schoolid
                    da.SelectCommand.Parameters.Add("@schoolyear", SqlDbType.Int).Value = schoolyear
                    da.SelectCommand.Parameters.Add("@milestone", SqlDbType.Int).Value = milestone
                    da.SelectCommand.Parameters.Add("@semester", SqlDbType.Int).Value = term
                    da.Fill(dt)
                Catch ex As Exception

                End Try

            End Using
        End Using

        Return dt

    End Function
#End Region