ASP.NET

class Program
  {
    
    static void CompareList<T>(List<T> a, List<T> b, ref List<T> diff)
    {
      foreach(T item in a)
      {
        if(!b.Contains(item))
        {
          if(diff.Count == 0)
          {
            diff.Add(item);
          }
          else if(!diff.Contains(item))
          {
            diff.Add(item);
          }
        }
      }
    }

    /// <summary>
    /// Compare Two Lists of primitive types and return sorted list
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="a">The first list to compare</param>
    /// <param name="b">The second list to compare</param>
    /// <param name="diff">the ref param that returns the sorted list of items not in both lists</param>
    static void ListDifference<T>(List<T> a, List<T> b, ref List<T> diff)
    {
      CompareList(a,b,ref diff);
      CompareList(b,a,ref diff);
      diff.Sort();
    }

    static void Main(string[] args)
    {

      List<int> a = new List<int>{1,2,3,4,5,6,7};
      List<int> b = new List<int>{4,6,7,8,9,10};
      List<int> diff = new List<int>();

      List<string> c = new List<string>{"a", "b", "c", "d","e", "f", "t", "U"};
      List<string> d = new List<string>{"b","e","f","g","h"};
      //List<string> d = new List<string>();
      List<string> diff2 = new List<string>();

      ListDifference(a,b, ref diff);
      ListDifference(c, d, ref diff2);

      Console.WriteLine("-- Result of CompareLists for Int");
      foreach(int i in diff)
      {
        Console.WriteLine(i.ToString());
      }
      Console.WriteLine();
      Console.WriteLine("Results for CompareLists for strings");
      foreach(string s in diff2)
      {
        Console.WriteLine(s);
      }

      Console.Read();
    }
  }

"insert into tbl1([names]) values(@names)"

Please up vote if it helped you
Thanks And Regards
Sibeesh

"SELECT * FROM table_name WHERE column_name = value";

if(data == "present") {
   // data present
   Console.WriteLine("Data present! Thanks");
} else {
   // save the data to database or the location,
   Console.WriteLine("Data saved! Thanks");
}

samflex wrote:

'Protect against SQL Injection
strSearch = Replace(receiveddate, "'", "''", 1, -1, 1)
fromdate = Replace(fromdate, "'", "''", 1, -1, 1)
enddate = Replace(enddate, "'", "''", 1, -1, 1)

Dim cmd As New SqlCommand("-placeholder-", conn)
Dim sb As New StringBuilder( *** BASE QUERY HERE, up to but not including the "where" keyword *** )

' Hard-code the acceptable values here:
Dim andors As String = IF(ANDOR.SelectedValue = "OR", " OR ", " AND ")
Dim startedWhere As Boolean = False

If facilityT.SelectedValue <> "" Then
    sb.Append(If(startedWhere, andors, " WHERE "))
    sb.Append("i.instructorName = @InstructorName")
    cmd.Parameters.AddWithValue("@InstructorName", facilityT.SelectedValue)
    startedWhere = True
End If

If ViewSelect = "Range" Then
    sb.Append(If(startedWhere, andors, " WHERE "))
    sb.Append("d.trainingDates Between @FromDate And @EndDate")
    cmd.Parameters.AddWithValue("@FromDate", Date.Parse(fromdate))
    cmd.Parameters.AddWithValue("@EndDate", Date.Parse(enddate))
    startedWhere = True
    
ElseIf ViewSelect = "Specific" Then
    sb.Append(If(startedWhere, andors, " WHERE "))
    sb.Append("d.trainingDates = @SpecificDate")
    cmd.Parameters.AddWithValue("@SpecificDate", Date.Parse(strSearch))
    startedWhere = True
End If

cmd.CommandText = sb.ToString()

Dim cmd As New SqlCommand("-placeholder-", conn)

Dim sb As New StringBuilder( *** BASE QUERY HERE, up to but not including the "where" keyword *** )

SELECT L.LocationId," & _
       "c.courseId, " & _
        "c.coursename, " & _
       "CASE WHEN L.Seating_Capacity - COALESCE(TS.TakenSeats,0) = 0 THEN 'Not Available' " & _
           " ELSE 'Available' END AS 'AvailableSeats'," & _
        "d.dateid, " & _
       " d.trainingDates, " & _
        "d.trainingtime, " & _
       " c.CourseDescription, " & _
       " i.instructorName, " & _
        "l.location " & _
      " FROM tblLocations L " & _
      " Inner Join tblCourses c on l.locationId = c.locationId " & _
      " Inner Join tblTrainingDates d on c.dateid=d.dateid " & _
     "  Inner Join tblCourseInstructor ic on c.courseId = ic.CourseId " & _
     "  Inner Join tblInstructors i on ic.instructorId = i.instructorId " & _
     "  OUTER APPLY (Select Count(*) TakenSeats " & _
     "  FROM tblTrainings T " & _
     "  where(L.LocationId = T.LocationId) " & _
     "  AND c.courseId = t.courseId) TS where "

Dim sb As New StringBuilder("SELECT L.LocationId," & _
       "c.courseId, " & _
        "c.coursename, " & _
       "CASE WHEN L.Seating_Capacity - COALESCE(TS.TakenSeats,0) = 0 THEN 'Not Available' " & _
           " ELSE 'Available' END AS 'AvailableSeats'," & _
        "d.dateid, " & _
       " d.trainingDates, " & _
        "d.trainingtime, " & _
       " c.CourseDescription, " & _
       " i.instructorName, " & _
        "l.location " & _
      " FROM tblLocations L " & _
      " Inner Join tblCourses c on l.locationId = c.locationId " & _
      " Inner Join tblTrainingDates d on c.dateid=d.dateid " & _
     "  Inner Join tblCourseInstructor ic on c.courseId = ic.CourseId " & _
     "  Inner Join tblInstructors i on ic.instructorId = i.instructorId " & _
     "  OUTER APPLY (Select Count(*) TakenSeats " & _
     "  FROM tblTrainings T " & _
     "  where(L.LocationId = T.LocationId) " & _
     "  AND c.courseId = t.courseId) TS")

If dt.Rows.Count = 0 Then
    message.Text = "No matching records found."
    
    cmd.Parameters.Clear()
    cmd.Parameters.AddWithValue("@FromDate", Date.Today.AddMonths(-1))
    cmd.Parameters.AddWithValue("@EndDate", Date.Today)
    
    cmd.CommandText = "SELECT L.LocationId," & _
       "c.courseId, " & _
        "c.coursename, " & _
       "CASE WHEN L.Seating_Capacity - COALESCE(TS.TakenSeats,0) = 0 THEN 'Not Available' " & _
           " ELSE 'Available' END AS 'AvailableSeats'," & _
        "d.dateid, " & _
       " d.trainingDates, " & _
        "d.trainingtime, " & _
       " c.CourseDescription, " & _
       " i.instructorName, " & _
        "l.location " & _
      " FROM tblLocations L " & _
      " Inner Join tblCourses c on l.locationId = c.locationId " & _
      " Inner Join tblTrainingDates d on c.dateid=d.dateid " & _
     "  Inner Join tblCourseInstructor ic on c.courseId = ic.CourseId " & _
     "  Inner Join tblInstructors i on ic.instructorId = i.instructorId " & _
     "  OUTER APPLY (Select Count(*) TakenSeats " & _
     "  FROM tblTrainings T " & _
     "  where(L.LocationId = T.LocationId) " & _
     "  AND c.courseId = t.courseId) TS " & _
     " where d.trainingDates Between @FromDate And @EndDate"
    
    dt = GetData(cmd)
End If

Private Sub showGrid()

    Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("conString").ConnectionString)
    ' Dim strQuery As String

    Dim cmd As New SqlCommand("-placeholder-", conn)
    Dim sb As New StringBuilder("SELECT L.LocationId," & _
           "c.courseId, " & _
            "c.coursename, " & _
           "CASE WHEN L.Seating_Capacity - COALESCE(TS.TakenSeats,0) = 0 THEN 'Not Available' " & _
               " ELSE 'Available' END AS 'AvailableSeats'," & _
            "d.dateid, " & _
           " d.trainingDates, " & _
            "d.trainingtime, " & _
           " c.CourseDescription, " & _
           " i.instructorName, " & _
            "l.location " & _
          " FROM tblLocations L " & _
          " Inner Join tblCourses c on l.locationId = c.locationId " & _
          " Inner Join tblTrainingDates d on c.dateid=d.dateid " & _
         "  Inner Join tblCourseInstructor ic on c.courseId = ic.CourseId " & _
         "  Inner Join tblInstructors i on ic.instructorId = i.instructorId " & _
         "  OUTER APPLY (Select Count(*) TakenSeats " & _
         "  FROM tblTrainings T " & _
         "  where(L.LocationId = T.LocationId) " & _
         "  AND c.courseId = t.courseId) TS")

    ' Hard-code the acceptable values here:
    Dim andors As String = If(ANDOR.SelectedValue = "OR", " OR ", " AND ")
    Dim startedWhere As Boolean = False

    If DropDownList1.SelectedValue <> "" Then
        sb.Append(If(startedWhere, andors, " WHERE "))
        sb.Append("i.instructorName = @InstructorName")
        cmd.Parameters.AddWithValue("@InstructorName", DropDownList1.SelectedValue)
        startedWhere = True
    End If

    If ViewSelect = "Range" Then
        sb.Append(If(startedWhere, andors, " WHERE "))
        sb.Append("d.trainingDates Between @FromDate And @EndDate")
        cmd.Parameters.AddWithValue("@FromDate", Date.Parse(txtFromDate.Text))
        cmd.Parameters.AddWithValue("@EndDate", Date.Parse(txtToDate.Text))
        startedWhere = True

    ElseIf ViewSelect = "Specific" Then
        sb.Append(If(startedWhere, andors, " WHERE "))
        sb.Append("d.trainingDates = @SpecificDate")
        cmd.Parameters.AddWithValue("@SpecificDate", Date.Parse(txtSpecificDate.Text))
        startedWhere = True
    End If

    cmd.CommandText = sb.ToString()

    ' Dim cmd As New SqlCommand(strQuery) 'This says cmd has already been declared
    Dim dt As DataTable = GetData(cmd)
    If dt.Rows.Count = 0 Then

        cmd.Parameters.Clear()
        cmd.Parameters.AddWithValue("@FromDate", Date.Today.AddMonths(-1))
        cmd.Parameters.AddWithValue("@EndDate", Date.Today)

        cmd.CommandText = "SELECT L.LocationId," & _
           "c.courseId, " & _
            "c.coursename, " & _
           "CASE WHEN L.Seating_Capacity - COALESCE(TS.TakenSeats,0) = 0 THEN 'Not Available' " & _
               " ELSE 'Available' END AS 'AvailableSeats'," & _
            "d.dateid, " & _
           " d.trainingDates, " & _
            "d.trainingtime, " & _
           " c.CourseDescription, " & _
           " i.instructorName, " & _
            "l.location " & _
          " FROM tblLocations L " & _
          " Inner Join tblCourses c on l.locationId = c.locationId " & _
          " Inner Join tblTrainingDates d on c.dateid=d.dateid " & _
         "  Inner Join tblCourseInstructor ic on c.courseId = ic.CourseId " & _
         "  Inner Join tblInstructors i on ic.instructorId = i.instructorId " & _
         "  OUTER APPLY (Select Count(*) TakenSeats " & _
         "  FROM tblTrainings T " & _
         "  where(L.LocationId = T.LocationId) " & _
         "  AND c.courseId = t.courseId) TS " & _
         " where d.trainingDates Between @FromDate And @EndDate"

        dt = GetData(cmd)
    End If

    GridView1.DataSource = dt
    GridView1.DataBind()

End Sub