locked
incorrect syntax near ',' RRS feed

  • Question

  • Hi all,

    I am sorry to annoying for the simple things also, but i am not an experienced person.

    when i added webpart i am getting this message. Can anybody tell me, which line is the error please. I was unable to understand the stack trace. I am pasting the stack trace

    Thank you very much.

    [SqlException (0x80131904): Incorrect syntax near ','.]
       System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +212
       System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +245
       System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1099
       System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +2858575
       System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +470
       System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +182
       EoamSharepointMyClassAdmin.StaffTimetable.StaffTimetable._CreateStudentConnection(String _Username) +275
       EoamSharepointMyClassAdmin.StaffTimetable.StaffTimetable._RunStudent(String _Username) +62
       EoamSharepointMyClassAdmin.StaffTimetable.StaffTimetable._StudentCodeDropdown_SelectedIndexChanged(Object sender, EventArgs e) +278
       System.EventHandler.Invoke(Object sender, EventArgs e) +0
       System.Web.UI.WebControls.ListControl.OnSelectedIndexChanged(EventArgs e) +115
       System.Web.UI.Page.RaiseChangedEvents() +89
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2777

    Monday, July 16, 2012 1:03 AM

Answers

  • I think you have too many parans ")" in the SELECT DISTINCT statement:

    AND (
      (ttmast.year_num=DatePart(Year,GetDate()))   )
      AND (ttmast.semester='3')
      AND (teacher.e_mail='" + _Username + "')
    )


    and again in the SELECT ttmast... statement:

    AND (
      (ttmast.year_num=Year,GetDate())    )
      AND (ttmast.semester='3')
      AND (studsub.stud_code='" + _Username + "')
    )

     

     


    Mike Smith TechTrainingNotes.blogspot.com

    • Proposed as answer by SPKoder Tuesday, July 17, 2012 8:41 PM
    • Marked as answer by Lhan Han Friday, July 27, 2012 9:47 AM
    Monday, July 16, 2012 11:17 PM

All replies

  • What kind of web part?

      custom Visual Studio?
      list?
      Content Editor?
      Content Query?
      Data View?
      etc?

    Usually start your search with the first error listed, in this case "SqlException (0x80131904): Incorrect syntax near ','". This error implies an invalid SQL statement.


    Mike Smith TechTrainingNotes.blogspot.com

    Monday, July 16, 2012 2:21 AM
  • It is a custom webpart created in vs2010.

    Thank you Mike Smith for your quick reply

    Monday, July 16, 2012 4:28 AM
  • Hi,

    Are you using any SQL statement in your webpart? If yes then look back to your query for this issue. There must be some wrong statement  / key near your query.

    If you are not able to find then share your code with us

    Let us know your result


    Cheers, Hemendra-MCTS "Yesterday is just a memory,Tomorrow we may never see"

    Monday, July 16, 2012 4:36 AM
  • Your sql query is wrong, can you share us your code so that we will give fix for your error.

    Thanks,


    Senthil

    Monday, July 16, 2012 4:43 AM
  • Thank you Hemendra and SenthilGopal.

    I can share my code . can you help me plase. Thank you Very much

    using System;
    using System.ComponentModel;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using Microsoft.SharePoint;
    using Microsoft.SharePoint.WebControls;
    using System.Data.SqlClient;
    using System.Web.UI.HtmlControls;
    
    namespace EoamSharepointMyClassAdmin.StaffTimetable
    {
        [ToolboxItemAttribute(false)]
        public class StaffTimetable : WebPart
        {
            string[] _PeriodDescriptions;
            string[] _TimetableSubjectArray = new string[121];
            string[] _Days;
            string _Username;
            string _IsTeacher = "";
            int _PeriodEnter, _Period, _Day, _PeriodDate, PeriodDate, _CurrentPeriod, _TimeTablePath;
            UpdatePanel _MyRefreshPanel = new UpdatePanel();
            Table _Table;
            TableRow _TableRow;
            TableCell _TableCell;
            string _ConnectionString = @"Initial Catalog=tass;Data Source=172.16.0.11;User ID=sa;Password=99226400";
            SqlConnection _SqlConnection;
            DropDownList _StudentCodeDropdown = new DropDownList();
            DropDownList _SelectYearDropDown = new DropDownList();
    
            DropDownList _SelectTeacherDropDown = new DropDownList();
            AsyncPostBackTrigger Stafftrigger = new AsyncPostBackTrigger();
            AsyncPostBackTrigger Studenttrigger = new AsyncPostBackTrigger();
            AsyncPostBackTrigger Yeartrigger = new AsyncPostBackTrigger();
    
    
            protected override void CreateChildControls()
            {
                base.CreateChildControls();
                _SqlConnection = new SqlConnection(_ConnectionString);
                _SqlConnection.Open();
                _GetUserDetails(out _Username, out _TimeTablePath);
    
                _StudentCodeDropdown.Visible = false;
                _SelectYearDropDown.Visible = false;
                _SelectTeacherDropDown.Visible = false;
                _StudentCodeDropdown.AutoPostBack = true;
                _SelectYearDropDown.AutoPostBack = true;
    
                _SelectYearDropDown.ID = "_SelectYearDropDown";
                _StudentCodeDropdown.ID = "_StudentCodeDropdown";
                _MyRefreshPanel.ID = "RefreshPanel";
                _MyRefreshPanel.ChildrenAsTriggers = true;
                _MyRefreshPanel.UpdateMode = UpdatePanelUpdateMode.Conditional;
    
    
                Studenttrigger.ControlID = "_StudentCodeDropdown";
                Studenttrigger.EventName = "SelectedindexChanged";
                Yeartrigger.ControlID = "_SelectYearDropDown";
                Yeartrigger.EventName = "SelectedindexChanged";
    
    
                _MyRefreshPanel.Triggers.Add(Studenttrigger);
                _MyRefreshPanel.Triggers.Add(Yeartrigger);
    
                _MyRefreshPanel.ContentTemplateContainer.Controls.Add(_StudentCodeDropdown);
                _MyRefreshPanel.ContentTemplateContainer.Controls.Add(_SelectYearDropDown);
    
    
    
                CssLink EoamCssLink = new CssLink();
                EoamCssLink.DefaultUrl = "/_layouts/1033/styles/TimeTable.css";
                this.Controls.Add(EoamCssLink);
    
                this.Controls.Add(_MyRefreshPanel);
                switch (_TimeTablePath)
                {
    
                    case 2:
                        _RunTeacher(_Username);
                        break;
    
                }
                _SqlConnection.Close();
            }
            private void _RunStudent(string _Username)
            {
                _TimetableSubjectArray = _CreateStudentConnection(_Username);
                _PeriodDate = GetPeriodDate();
                _DeclareArrays(out _PeriodDescriptions, out _Days);
                _CreateTable();
                _SetupDays();
                _CreateStudentSubjects(_PeriodDescriptions, _TimetableSubjectArray, _PeriodDate);
            }
            private void _RunTeacher(string _Username)
            {
                _SelectTeacherDropDown.Visible = true;
                _SelectYearDropDown.Visible = true;
                _SelectYearDropDown.Items.Add("Select Year");
                _SelectYearDropDown.Items.Add("7");
                _SelectYearDropDown.Items.Add("8");
                _SelectYearDropDown.Items.Add("9");
                _SelectYearDropDown.Items.Add("10");
                _SelectYearDropDown.Items.Add("11");
                _SelectYearDropDown.Items.Add("12");
                _SelectYearDropDown.AutoPostBack = true;
                _SelectYearDropDown.SelectedIndexChanged += new EventHandler(_SelectYearDropDown_SelectedIndexChanged);
    
                _StudentCodeDropdown.AutoPostBack = true;
                _StudentCodeDropdown.SelectedIndexChanged += new EventHandler(_StudentCodeDropdown_SelectedIndexChanged);
            }
    
            private void _RunTeacherTimetable(string _Username)
            {
                _TimetableSubjectArray = _CreateTeacherConnection(_Username);
                _PeriodDate = GetPeriodDate();
                _DeclareArrays(out _PeriodDescriptions, out _Days);
                _CreateTable();
                _SetupDays();
                _CreateTeacherSubjects(_PeriodDescriptions, _TimetableSubjectArray, _PeriodDate);
            }
            void _SelectTeacherDropDown_SelectedIndexChanged(object sender, EventArgs e)
            {
                string _TeacherPassthrough = _SelectTeacherDropDown.SelectedValue.ToString();
                _IsTeacher = "Yes";
                _MyRefreshPanel.ContentTemplateContainer.Controls.Remove(_Table);
                _RunTeacherTimetable(_TeacherPassthrough);
            }
            void _StudentCodeDropdown_SelectedIndexChanged(object sender, EventArgs e)
            {
                string _UsernameTeacherPassthrough = _StudentCodeDropdown.SelectedValue.ToString();
                _IsTeacher = "Yes";
                _MyRefreshPanel.ContentTemplateContainer.Controls.Remove(_Table);
                _RunStudent(_UsernameTeacherPassthrough);
            }
            void _SelectYearDropDown_SelectedIndexChanged(object sender, EventArgs e)
            {
                _StudentCodeDropdown.Visible = true;
                _SqlConnection.Open();
                string _StudentYear = _SelectYearDropDown.SelectedValue.ToString();
                SqlCommand _StudentCodeSqlCommand = new SqlCommand("SELECT Name = preferred_name + ' ' + surname, stud_code FROM tass.dbo.student WHERE year_grp='" + _StudentYear + "' and cmpy_code ='01' and dol is NULL ORDER BY surname", _SqlConnection);
                _StudentCodeSqlCommand.ExecuteNonQuery();
                SqlDataReader _StudentCodeSqlReader = _StudentCodeSqlCommand.ExecuteReader();
                _StudentCodeDropdown.DataSource = _StudentCodeSqlReader;
                _StudentCodeDropdown.DataValueField = "stud_code";
                _StudentCodeDropdown.DataTextField = "Name";
                _StudentCodeDropdown.DataBind();
                _StudentCodeDropdown.Items.Insert(0, "Select a Student");
                _StudentCodeSqlReader.Close();
                _SqlConnection.Close();
    
            }
            private void _GetUserDetails(out string _Username, out int _TimeTablePath)
            {
                string _StudentCheck, _ParentCheck;
                _Username = SPContext.Current.Web.CurrentUser.LoginName;
                _Username = _Username.Remove(0, 8);
                _StudentCheck = _Username.Substring(0, 2);
                _ParentCheck = _Username.Substring(0, 3);
                if (_StudentCheck == "00")
                {
                    _TimeTablePath = 1;
                }
                else if (_ParentCheck == "000")
                {
                    _TimeTablePath = 3;
    
                }
                else
                {
                    _TimeTablePath = 2;
    
                }
            }
            private void _CreateStudentSubjects(string[] _PeriodDescriptions, string[] _TimetableSubjectArray, int _PeriodDate)
            {
                int a = 1;
                int b;
                int _RowCheck;
                int _PeriodCheck;
    
                while (a != 13)
                {
                    _TableRow = new TableRow();
                    b = 1;
                    while (b != 11)
                    {
                        _CurrentPeriod = _GetPeriod();
                        _PeriodEnter = (((b - 1) * 12) + a);
                        _RowCheck = (((a - 1) * 10) + b);
                        _PeriodCheck = (_RowCheck - (((a - 1) * 10) + 1));
                        if (b < 6)
                        {
                            if (_PeriodCheck == 0)
                            {
                                _TableCell = new TableCell();
                                _TableCell.Text = _PeriodDescriptions[a];
                                _TableCell.CssClass = "GenericLayout";
                                _TableRow.Controls.Add(_TableCell);
                                _TableCell = new TableCell();
                            }
                            else
                            {
                                _TableCell = new TableCell();
                            }
                        }
                        else
                        {
                            _TableCell = new TableCell();
                        }
                        if (_TimetableSubjectArray[_PeriodEnter] == "")
                        {
                            _TableCell.Text = " ";
                            _TableRow.Controls.Add(_TableCell);
                            if (_PeriodDate == b)
                            {
    
                                if (_CurrentPeriod == a)
                                {
                                    _TableCell.CssClass = "ApplyPersonalClassStyle";
                                }
                                else
                                {
                                    _TableCell.CssClass = "CurrentClassesLayout";
                                }
                            }
                            else
                            {
                                if ((a == 5) || (a == 6) || (a == 8) || (a == 9))
                                {
                                    _TableCell.CssClass = "GenericOtherLayout";
                                }
                                else
                                {
                                    _TableCell.CssClass = "GenericClassesLayout";
                                }
                            }
                        }
                        else
                        {
                            _TableCell.Text = _TimetableSubjectArray[_PeriodEnter];
                            if (_PeriodDate == b)
                            {
    
                                if (_CurrentPeriod == a)
                                {
                                    _TableCell.CssClass = "ApplyPersonalClassStyle";
                                }
                                else
                                {
                                    _TableCell.CssClass = "CurrentClassesLayout";
                                }
                            }
                            else
                            {
                                if ((a == 5) || (a == 6) || (a == 8) || (a == 9))
                                {
                                    _TableCell.CssClass = "GenericOtherLayout";
                                }
                                else
                                {
                                    _TableCell.CssClass = "GenericClassesLayout";
                                }
                            }
                        }
                        b = b + 1;
                        _TableRow.Controls.Add(_TableCell);
                    }
                    _Table.Controls.Add(_TableRow);
                    a = a + 1;
                }
                _MyRefreshPanel.ContentTemplateContainer.Controls.Add(_Table);
            }
            private void _CreateTeacherSubjects(string[] _PeriodDescriptions, string[] _TimetableSubjectArray, int _PeriodDate)
            {
                int a = 1;
                int b;
                int _RowCheck;
                int _PeriodCheck;
    
                while (a != 13)
                {
                    _TableRow = new TableRow();
                    b = 1;
                    while (b != 11)
                    {
                        _CurrentPeriod = _GetPeriod();
                        _PeriodEnter = (((b - 1) * 12) + a);
                        _RowCheck = (((a - 1) * 10) + b);
                        _PeriodCheck = (_RowCheck - (((a - 1) * 10) + 1));
                        if (b < 6)
                        {
                            if (_PeriodCheck == 0)
                            {
                                _TableCell = new TableCell();
                                _TableCell.Text = _PeriodDescriptions[a];
                                _TableCell.CssClass = "GenericLayout";
                                _TableRow.Controls.Add(_TableCell);
                                _TableCell = new TableCell();
                            }
                            else
                            {
                                _TableCell = new TableCell();
                            }
                        }
                        else
                        {
                            _TableCell = new TableCell();
                        }
                        if (_TimetableSubjectArray[_PeriodEnter] == "")
                        {
                            _TableCell.Text = " ";
                            _TableRow.Controls.Add(_TableCell);
                            if (_PeriodDate == b)
                            {
    
                                if (_CurrentPeriod == a)
                                {
                                    _TableCell.CssClass = "ApplyPersonalClassStyle";
                                }
                                else
                                {
                                    _TableCell.CssClass = "CurrentClassesLayout";
                                }
                            }
                            else
                            {
                                if ((a == 5) || (a == 6) || (a == 8) || (a == 9))
                                {
                                    _TableCell.CssClass = "GenericOtherLayout";
                                }
                                else
                                {
                                    _TableCell.CssClass = "GenericClassesLayout";
                                }
                            }
                        }
                        else
                        {
                            _TableCell.Text = _TimetableSubjectArray[_PeriodEnter];
                            if (_PeriodDate == b)
                            {
    
                                if (_CurrentPeriod == a)
                                {
                                    _TableCell.CssClass = "ApplyPersonalClassStyle";
                                }
                                else
                                {
                                    _TableCell.CssClass = "CurrentClassesLayout";
                                }
                            }
                            else
                            {
                                if ((a == 5) || (a == 6) || (a == 8) || (a == 9))
                                {
                                    _TableCell.CssClass = "GenericOtherLayout";
                                }
                                else
                                {
                                    _TableCell.CssClass = "GenericClassesLayout";
                                }
                            }
                        }
                        b = b + 1;
                        _TableRow.Controls.Add(_TableCell);
                    }
                    _Table.Controls.Add(_TableRow);
                    a = a + 1;
                }
                _MyRefreshPanel.ContentTemplateContainer.Controls.Add(_Table);
            }
            private void _CreateTable()
            {
                _Table = new Table();
                _Table.CssClass = "MyTable";
            }
    
            private string[] _CreateTeacherConnection(string _Username)
            {
                int i = 0;
                while (i < 121)
                {
                    _TimetableSubjectArray[i] = "";
                    i = i + 1;
                }
    
                SqlCommand _SqlCommand = new SqlCommand("SELECT DISTINCT ttdays.day_code, ttmast.prd_code, subtab.sub_abrev, ttmast.room_code FROM tass.dbo.studsub,tass.dbo.subtab,tass.dbo.teacher, tass.dbo.ttdays,tass.dbo.ttmast WHERE studsub.cmpy_code = ttmast.cmpy_code AND studsub.semester = ttmast.semester AND teacher.tch_code = ttmast.tch_code AND subtab.sub_code = ttmast.sub_code AND subtab.cmpy_code = studsub.cmpy_code AND studsub.sub_code = ttmast.sub_code AND ttdays.day_code = ttmast.day_code AND ttdays.tt_id = ttmast.tt_id AND studsub.yr_study = ttmast.year_num AND ttmast.tt_id ='57' AND ttmast.class = studsub.class AND ((ttmast.year_num=DatePart(Year,GetDate()))) AND (ttmast.semester='3') AND (teacher.e_mail='" + _Username + "')) ORDER BY ttdays.day_code, ttmast.prd_code, subtab.sub_abrev, ttmast.room_code", _SqlConnection);
                _SqlCommand.ExecuteNonQuery();
                SqlDataReader _SqlReader = _SqlCommand.ExecuteReader();
                while (_SqlReader.Read())
                {
    
                    _Day = Convert.ToInt32((string)_SqlReader["day_code"]);
                    switch (Convert.ToInt32((string)_SqlReader["Prd_code"]))
                    {
                        case 1:
                            _Period = 1;
                            break;
                        case 2:
                            _Period = 2;
                            break;
                        case 3:
                            _Period = 3;
                            break;
                        case 4:
                            _Period = 4;
                            break;
                        case 5:
                            _Period = 6;
                            break;
                        case 6:
                            _Period = 7;
                            break;
                        case 7:
                            _Period = 10;
                            break;
                        case 8:
                            _Period = 11;
                            break;
                        case 9:
                            _Period = 12;
                            break;
                    }
                    _PeriodEnter = ((_Day - 1) * 12) + _Period;
    
                    if (_TimetableSubjectArray[_PeriodEnter] == null)
                    {
                        _TimetableSubjectArray[_PeriodEnter] = (string)_SqlReader["Sub_Abrev"] + " " + (string)_SqlReader["Room_code"];
                    }
                    else
                    {
                        _TimetableSubjectArray[_PeriodEnter] = _TimetableSubjectArray[_PeriodEnter] + "<br>" + (string)_SqlReader["Sub_Abrev"] + " " + (string)_SqlReader["Room_code"];
                    }
                }
                _SqlReader.Close();
    
                return _TimetableSubjectArray;
            }
            private string[] _CreateStudentConnection(string _Username)
            {
                _SqlConnection.Open();
                SqlCommand _SqlCommand = new SqlCommand("SELECT ttmast.year_num, ttmast.semester, ttdays.day_desc, ttmast.prd_code, studsub.stud_code, subtab.sub_abrev, teacher.surname, student.year_grp, ttmast.room_code, ttmast.day_code FROM tass.dbo.student,tass.dbo.studsub,tass.dbo.subtab, tass.dbo.teacher,tass.dbo.ttdays,tass.dbo.ttmast WHERE studsub.cmpy_code = ttmast.cmpy_code AND studsub.semester = ttmast.semester AND teacher.tch_code = ttmast.tch_code AND subtab.sub_code = ttmast.sub_code AND subtab.cmpy_code = studsub.cmpy_code AND studsub.sub_code = ttmast.sub_code AND ttdays.day_code = ttmast.day_code AND ttdays.tt_id = ttmast.tt_id AND ttmast.tt_id = '57' AND studsub.yr_study = ttmast.year_num AND ttmast.class = studsub.class AND student.cmpy_code = ttmast.cmpy_code AND student.stud_code = studsub.stud_code AND ((ttmast.year_num=Year,GetDate())) AND (ttmast.semester='3') AND (studsub.stud_code='" + _Username + "')) ORDER BY studsub.stud_code, ttdays.day_code, ttmast.prd_code", _SqlConnection);
                _SqlCommand.ExecuteNonQuery();
                SqlDataReader _SqlReader = _SqlCommand.ExecuteReader();
                while (_SqlReader.Read())
                {
    
                    _Day = Convert.ToInt32(_SqlReader["day_code"].ToString());
                    switch (Convert.ToInt32(_SqlReader["Prd_code"].ToString()))
                    {
                        case 1:
                            _Period = 1;
                            break;
                        case 2:
                            _Period = 2;
                            break;
                        case 3:
                            _Period = 3;
                            break;
                        case 4:
                            _Period = 4;
                            break;
                        case 5:
                            _Period = 6;
                            break;
                        case 6:
                            _Period = 7;
                            break;
                        case 7:
                            _Period = 10;
                            break;
                        case 8:
                            _Period = 11;
                            break;
                        case 9:
                            _Period = 12;
                            break;
                    }
                    _PeriodEnter = ((_Day - 1) * 12) + _Period;
    
                    if (_TimetableSubjectArray[_PeriodEnter] == null)
                    {
                        _TimetableSubjectArray[_PeriodEnter] = (string)_SqlReader["Sub_Abrev"] + " " + (string)_SqlReader["Room_code"] + "<br>" + (string)_SqlReader["surname"];
                    }
                    else
                    {
                        _TimetableSubjectArray[_PeriodEnter] = _TimetableSubjectArray[_PeriodEnter] + "<br><br>" + (string)_SqlReader["Sub_Abrev"] + " " + (string)_SqlReader["Room_code"] + "<br>" + (string)_SqlReader["surname"];
                    }
                }
                _SqlReader.Close();
                _SqlConnection.Close();
    
                return _TimetableSubjectArray;
            }
            private int _GetPeriod()
            {
                int _PeriodNumber;
                DateTime _GetTime = System.DateTime.Now;
                if ((System.DateTime.Now > Convert.ToDateTime("7:00 AM")) && (System.DateTime.Now < Convert.ToDateTime("8:20 AM")))
                {
                    _PeriodNumber = 1;
                }
                else if ((System.DateTime.Now > Convert.ToDateTime("8:20 AM")) && (System.DateTime.Now < Convert.ToDateTime("8:25 AM")))
                {
                    _PeriodNumber = 2;
                }
                else if ((System.DateTime.Now > Convert.ToDateTime("8:25 AM")) && (System.DateTime.Now < Convert.ToDateTime("9:25 AM")))
                {
                    _PeriodNumber = 3;
                }
                else if ((System.DateTime.Now > Convert.ToDateTime("9:25 AM")) && (System.DateTime.Now < Convert.ToDateTime("10:30 AM")))
                {
                    _PeriodNumber = 4;
                }
                else if ((System.DateTime.Now > Convert.ToDateTime("10:30")) && (System.DateTime.Now < Convert.ToDateTime("10:50 AM")))
                {
                    _PeriodNumber = 5;
                }
                else if ((System.DateTime.Now > Convert.ToDateTime("10:50 AM")) && (System.DateTime.Now < Convert.ToDateTime("11:25 AM")))
                {
                    _PeriodNumber = 6;
                }
                else if ((System.DateTime.Now > Convert.ToDateTime("11:25 AM")) && (System.DateTime.Now < Convert.ToDateTime("12:25 PM")))
                {
                    _PeriodNumber = 7;
                }
                else if ((System.DateTime.Now > Convert.ToDateTime("12:25 PM")) && (System.DateTime.Now < Convert.ToDateTime("12:50 PM")))
                {
                    _PeriodNumber = 8;
                }
                else if ((System.DateTime.Now > Convert.ToDateTime("12:50 AM")) && (System.DateTime.Now < Convert.ToDateTime("1:10 AM")))
                {
                    _PeriodNumber = 9;
                }
                else if ((System.DateTime.Now > Convert.ToDateTime("1:10 PM")) && (System.DateTime.Now < Convert.ToDateTime("2:10 PM")))
                {
                    _PeriodNumber = 10;
                }
                else if ((System.DateTime.Now > Convert.ToDateTime("2:10 PM")) && (System.DateTime.Now < Convert.ToDateTime("3:10 PM")))
                {
                    _PeriodNumber = 11;
                }
                else if ((System.DateTime.Now > Convert.ToDateTime("3:10 PM")) && (System.DateTime.Now < Convert.ToDateTime("5:00 PM")))
                {
                    _PeriodNumber = 12;
                }
                else
                {
                    _PeriodNumber = 13;
                }
                return _PeriodNumber;
            }
            private void _SetupDays()
            {
                _TableRow = new TableRow();
                _TableCell = new TableCell();
                _TableCell.Text = " ";
                _TableCell.CssClass = "GenericLayout";
                _TableRow.Controls.Add(_TableCell);
                _TableCell = new TableCell();
                _TableCell.Text = "Week A";
                _TableCell.ColumnSpan = 5;
                if (_PeriodDate < 6)
                {
                    _TableCell.CssClass = "CurrentLayout";
                }
                else
                {
                    _TableCell.CssClass = "GenericLayout";
                }
                _TableRow.Controls.Add(_TableCell);
    
                _TableCell = new TableCell();
                _TableCell.Text = "Week B";
                _TableCell.ColumnSpan = 5;
    
                if (_PeriodDate > 5)
                {
                    _TableCell.CssClass = "CurrentLayout";
                }
                else
                {
                    _TableCell.CssClass = "GenericLayout";
                }
                _TableRow.Controls.Add(_TableCell);
    
                _Table.Controls.Add(_TableRow);
    
                _TableRow = new TableRow();
                _TableCell = new TableCell();
                _TableCell.Text = " ";
                _TableCell.CssClass = "GenericLayout";
                _TableRow.Controls.Add(_TableCell);
                int i = 0;
                while (i != 10)
                {
                    _TableCell = new TableCell();
                    _TableCell.Text = _Days[i];
                    _TableRow.Controls.Add(_TableCell);
                    if (_PeriodDate == (i + 1))
                    {
                        _TableCell.CssClass = "CurrentLayout";
                    }
                    else
                    {
                        _TableCell.CssClass = "GenericLayout";
                    }
                    i = i + 1;
                }
                _Table.Controls.Add(_TableRow);
            }
            private int GetPeriodDate()
            {
                string Day = DateTime.Today.Day.ToString();
                string Month = DateTime.Today.Month.ToString();
                string CurrentDayQuery;
                if (Month == "1" || Month == "2" || Month == "3" || Month == "4" || Month == "5" || Month == "6" || Month == "7" || Month == "8" || Month == "9")
                {
                    CurrentDayQuery = "2012-0" + Month + "-" + Day + " 00:00:00.000";
                }
                else
                {
                    CurrentDayQuery = "2012-" + Month + "-" + Day + " 00:00:00.000";
                }
                _SqlConnection.Open();
                SqlCommand _CalendarSqlCommand = new SqlCommand("SELECT day_code FROM tass.dbo.ttcalendar WHERE tt_id='57' AND ttdate='" + CurrentDayQuery + "'", _SqlConnection);
                _CalendarSqlCommand.ExecuteNonQuery();
                SqlDataReader _CalendarSqlReader = _CalendarSqlCommand.ExecuteReader();
                while (_CalendarSqlReader.Read())
                {
                    string test;
                    test = _CalendarSqlReader["day_code"].ToString();
                    PeriodDate = Convert.ToInt32(test);
                }
                _CalendarSqlCommand.Dispose();
                _CalendarSqlReader.Close();
                _SqlConnection.Close();
    
                return PeriodDate;
            }
            private void _DeclareArrays(out string[] _PeriodDescriptions, out string[] _Days)
            {
                _PeriodDescriptions = new string[13];
                _Days = new string[10];
                _PeriodDescriptions[1] = "Before School<br>7:00am-8:20am";
                _PeriodDescriptions[2] = "Rollcall<br>8:20am-8:25am";
                _PeriodDescriptions[3] = "Period 1<br>8:25am-9:25am";
                _PeriodDescriptions[4] = "Period 2<br>9:25am-10:30am";
                _PeriodDescriptions[5] = "Recess<br>10:30am-10:50am";
                _PeriodDescriptions[6] = "Assembly<br>10:50am-11:25am";
                _PeriodDescriptions[7] = "Period 3<br>11:25am-12:25pm";
                _PeriodDescriptions[8] = "Lunch 1<br>12:25pm-12:50pm";
                _PeriodDescriptions[9] = "Lunch 2<br>12:50pm-1:10pm";
                _PeriodDescriptions[10] = "Period 4<br>1:10pm-2:10pm";
                _PeriodDescriptions[11] = "Period 5<br>2:10pm-3:10pm";
                _PeriodDescriptions[12] = "After School<br>3:10pm-5:00pm";
                _Days[0] = "MONDAY";
                _Days[1] = "TUESDAY";
                _Days[2] = "WEDNESDAY";
                _Days[3] = "THURSDAY";
                _Days[4] = "FRIDAY";
                _Days[5] = "MONDAY";
                _Days[6] = "TUESDAY";
                _Days[7] = "WEDNESDAY";
                _Days[8] = "THURSDAY";
                _Days[9] = "FRIDAY";
            }
        }
    }
    

    Monday, July 16, 2012 11:02 PM
  • I think you have too many parans ")" in the SELECT DISTINCT statement:

    AND (
      (ttmast.year_num=DatePart(Year,GetDate()))   )
      AND (ttmast.semester='3')
      AND (teacher.e_mail='" + _Username + "')
    )


    and again in the SELECT ttmast... statement:

    AND (
      (ttmast.year_num=Year,GetDate())    )
      AND (ttmast.semester='3')
      AND (studsub.stud_code='" + _Username + "')
    )

     

     


    Mike Smith TechTrainingNotes.blogspot.com

    • Proposed as answer by SPKoder Tuesday, July 17, 2012 8:41 PM
    • Marked as answer by Lhan Han Friday, July 27, 2012 9:47 AM
    Monday, July 16, 2012 11:17 PM
  • thank you micke smith. I will remove those parans and try .

    Thanks alot.

    Tuesday, July 17, 2012 5:35 AM