locked
web from 2010 problem with sql RRS feed

  • Question

  • User-1997257017 posted
    In a VB.NET 2010 web form 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? **I also posted this question in vb.net asp net formum since I do not know what the correct forum to use is, #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

    Wednesday, March 16, 2016 10:23 PM

Answers

  • User-219423983 posted

    Hi wendy Elizabeth,

    1. Could you show me how to modify the sql I listed below so that the CTE works in VB.NET 2010?

    You should add a space before the “FROM” of below code. Because, the keywords “from” in the final query string is merged with “SEMESTER”

    & "FROM AtnLtrMilestone alm INNER JOIN AtnLtrASTU als ON alm.STULINK = als.STULINK "

    Besides, I suggest you could copy the result to the SQL Server and then find out the issue point. Or, you could first build the SQL Query string and then copy it to your VB code, after that you could combine the multiple lines into one.

    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?

    In your code, you could use ex.Message to Exception.StackTrace Property to show the detailed error inforamtion.

    https://msdn.microsoft.com/en-us/library/5ytxh3s3(v=vs.100).aspx

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 17, 2016 5:06 AM