none
Reflect Multiple dates from the Database in a month calender. RRS feed

  • Question

  • I want to display multiple dates in a month calender that have already been saved in a sql server table.

    I want to have windows form application for this and not Web based. I am struggling how to reflect the dates in the calender. I know how to connect to the sever and get data to a dataset .Then how I can reflect the multiple dates in the dataset in the Month calender by a different back color?


    Saturday, December 16, 2017 7:41 AM

Answers

  • In regards to Cor's response, he uses OleDb while you are using SqlClient, if you take his code and use the SqlClient, change the connection string this is a good solution.

    Also, I agree with Cor that a DataSet is not the right way to go, using a DataReader as Cor demonstrates is the better method to achieve obtaining data back.

    Here is an example using SQL-Server database

    I placed code in a class

    Imports System.Data.SqlClient
    
    Public Class Operations
        Private ConnectionString As String =
            "Data Source=KARENS-PC;Initial Catalog=DateTimeDatabase;Integrated Security=True"
        Public Function LoadDates() As DateTime()
            Dim dateList As New List(Of DateTime)
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT e.StartDate FROM dbo.Events AS e"
    
                    cn.Open()
    
                    Dim reader As SqlDataReader = cmd.ExecuteReader
                    While reader.Read
                        dateList.Add(reader.GetDateTime(0))
                    End While
    
                End Using
            End Using
            Return dateList.ToArray
        End Function
    End Class
    

    Form code

    Public Class Form1
        Private Sub cmdSetDates_Click(sender As Object, e As EventArgs) _
            Handles cmdSetDates.Click
    
            Dim ops As New Operations
            MonthCalendar1.AnnuallyBoldedDates = ops.LoadDates
    
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, December 16, 2017 11:49 AM
    Moderator

All replies

  • Hi,

    Do you mean continuous two dates by "the multiple dates", e.g. from Dec. 10 to Dec. 16?
    Or separated dates, e.g. Dec.10, Dec. 12, Dec. 15...?

    Regards,

    Ashidacchi

    Saturday, December 16, 2017 9:23 AM
  • Hi, 

    Separate dates. Basically database would have different dates on which a particular job is being carried out.

    Regards,

    Wasantha.

    Saturday, December 16, 2017 9:54 AM
  • Hi,

    I would like to confirm before displaying multiple date in MonthCalender....
    Can you set some colors in MonthCalendar like this?
        

    Regards,

    Ashidacchi

    Saturday, December 16, 2017 10:02 AM
  • Hello,

    When you say you know how to connection and get data, can you be more specific e.g. are you using TableAdapter with a BindingSource that was generated by Visual Studio or perhaps using SqlClient data provider? This can make a difference in the solution you are working on.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, December 16, 2017 10:31 AM
    Moderator
  • Hi,

    I found a ideal sample done in asp.net for exactly what I want in the following link . But I do not know how to apply the same for Windows Forms Application. There sample code have been given to change back color of the date cell.

    This is the link: http://www.vbasic.net/how-to-use-the-calendar-control-in-visual-basic-net/

    Regards,

    Wasantha.

    Saturday, December 16, 2017 10:52 AM
  • A dataset is really not the solution to do what you ask. I seldom use a datareader, but in this case it is the perfect class. 

    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
            Dim appointments As New List(Of DateTime)
            Using conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Test\TheDataBase.mdb;User Id=admin;Password=;")
                Using cmd As New OleDb.OleDbCommand("select Datum from TheTable where Datum between ? and ? ", conn)
                    cmd.Parameters.AddWithValue("?", New DateTime(2017, 11, 30))
                    cmd.Parameters.AddWithValue("?", New DateTime(2018, 1, 1))
                    cmd.Parameters.AddWithValue("?", "bug")
                    conn.Open()
    
                    Using reader As OleDb.OleDbDataReader = cmd.ExecuteReader
                        While reader.Read()
                            appointments.Add(CDate(reader(0)))
                        End While
                    End Using
                End Using
            End Using
            Me.MonthCalendar1.AnnuallyBoldedDates = appointments.toarray
        End Sub
    End Class


    Success
    Cor

    Saturday, December 16, 2017 11:05 AM
  • Hello,

    I will declare a SQL Data Adapter, Dataset and fill as below.

                 

    myCon.Open()

            da = New SqlDataAdapter

            ds = New DataSet

            da.SelectCommand = New SqlCommand("Select * from myTable", myCon)

            da.Fill(ds, "myTable")

    Wasantha.


    Saturday, December 16, 2017 11:11 AM
  • In regards to Cor's response, he uses OleDb while you are using SqlClient, if you take his code and use the SqlClient, change the connection string this is a good solution.

    Also, I agree with Cor that a DataSet is not the right way to go, using a DataReader as Cor demonstrates is the better method to achieve obtaining data back.

    Here is an example using SQL-Server database

    I placed code in a class

    Imports System.Data.SqlClient
    
    Public Class Operations
        Private ConnectionString As String =
            "Data Source=KARENS-PC;Initial Catalog=DateTimeDatabase;Integrated Security=True"
        Public Function LoadDates() As DateTime()
            Dim dateList As New List(Of DateTime)
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT e.StartDate FROM dbo.Events AS e"
    
                    cn.Open()
    
                    Dim reader As SqlDataReader = cmd.ExecuteReader
                    While reader.Read
                        dateList.Add(reader.GetDateTime(0))
                    End While
    
                End Using
            End Using
            Return dateList.ToArray
        End Function
    End Class
    

    Form code

    Public Class Form1
        Private Sub cmdSetDates_Click(sender As Object, e As EventArgs) _
            Handles cmdSetDates.Click
    
            Dim ops As New Operations
            MonthCalendar1.AnnuallyBoldedDates = ops.LoadDates
    
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, December 16, 2017 11:49 AM
    Moderator
  • Thank you both of you Cor and Karen.
    Saturday, December 16, 2017 12:42 PM