In a VB.NET 2010 application, I am trying to change the t-sql 2012 that is commented out for 'sql' to use the 'sql' that is not commented about below in the code. My goal is to use the original applications logic as much as possible.
I am trying to use a basic cte so that I can eventually list more columns in the original select statement and logic
after data is selected from the cte to list a sort column value. I know the code listed below does not work since the logic ends up in the try catch block. When I look at the 'sql' in the debugger, i so that there is no space in from of the FRom statement. The sql looks like
'STUDENTNAMEFROM'.
Thus could you tell me the following:
1. Could you show me how to modify the sql I listed below so that the CTE works in VB.NET 2010?
2. Could you tell me what I can do so the catch block will tell me what the exact error message is that is occuring?
#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
|