[RESOLVED] Read date from database to monthcalendar

[Radjesh Klauke]

Ola,

Now I've hardcoded my date into my app, but now I want to read it from the database. I knowhow to read the columns, but unfortunately I don't now how to make the date appear into the monthcalendar.

Select Case MyDGV.SelectedCells(8).Value.ToString()
    Case "Radjesh"
        With MyMonthCal
            .MarkedDates = New System.DateTime() {New System.DateTime(2010, 3, 3), New System.DateTime(2010, 3, 10), New System.DateTime(2010, 3, 17), New System.DateTime(2010, 3, 24), New System.DateTime(2010, 3, 31), _
            New System.DateTime(2010, 4, 7), New System.DateTime(2010, 4, 14), New System.DateTime(2010, 4, 21), New System.DateTime(2010, 4, 28), _
            New System.DateTime(2010, 5, 12), New System.DateTime(2010, 5, 19), New System.DateTime(2010, 5, 26)}
MyNotes.Text = "blablablabla"
        .UpdateMarkedDates()
    Case  "VB.NET"
        'etc...

Let me start by asking what the best way would be to setup the table?

Thanks for the help in advance.

[motil]

how about reading the date columns into array or list of Datetime and use them inside the calendar constructor ?

p.s i never used the calender control so pardon moi if i'm talking nonsense

[Radjesh Klauke]

how about reading the date columns into array or list of Datetime

Yes, but how. First I need to know how to design the table.
How should I set the date in the colum(s), how should I read it. First I need to know what would be the best way to setup the table(s).

[motil]

if you don't know how many dates each row should hold it would be better to give the dates a table of its own with relationship key and each date will have it's own row:

record_id (int/PK)  |  date_value (DateTime)   |  relationship key (the id that connect between this table and the table of the record  the dates are belonging to)
1                           1900-01-01 00:00                25

[Radjesh Klauke]

That's very interesting. Never used relationship-key's.
So actually you are saying to create a table for each "ID", if I'm getting this correct.

[motil]

i don't think you get me correct, let's say you have a primary table the holds list of countries, each country has it's own holiday dates, so the the countries table will look something like that:

tbl_countries:
----------------------------
country_id  |  country_name
 1                   Country 1
 2                   Country 2
 3                   Country 3
-------------------------------

now each country has different number of holidays so you use another table to hold all the countries holidays dates:

tbl_countries_dates:
id   |  holiday_date   | country_id
1       1900/01/01              1                 < --- this date record is holiday date of Country 1
2       1900/05/01              1                 < --- this date is also holiday date of Country 1
3       1900/05/25              3                 < --- this date is holiday date of Country 3
4       1900/05/05              2                 < --- this date is holiday date of Country 2

so Country 1 has two holidays dates and country 2 and 3 has one holiday date each.
this way you can hold unlimited number of dates for each country.

is it clear enough?

[Radjesh Klauke]

I think I get it. But how would my Query look like?

EDIT: Wait... I think in this case it is better to create a (date)table for each office.

[motil]

you can get the dates like so (using my example):

A) if you wish to get dates by country id with full details

SELECT A.country_id,A.country_name,B.holiday_date 
FROM tbl_countries A INNER JOIN tbl_countries_dates B
WHERE A.country_id = 1"

if you wish to get all dates of all countries just omit the where clause :

SELECT A.country_id,A.country_name,B.holiday_date 
FROM tbl_countries A INNER JOIN tbl_countries_dates B

[Radjesh Klauke]

Okay,

I created al the tables I needed. Each table has the same name as myDatagridview.SelectedCells(8).Value.Tostring.

Here's how each table looks like:



In the first column the dates are stored and in the second the notes.

The dates needs to be added into the monthcalendar and the note(s) into a RTB. Here's the code I have so far:

Dim mysqlstrConn As String = "server=myserver; Uid=table; database=table; pwd=password; connect timeout=30;"
Dim mysqlConn As New MySql.Data.MySqlClient.MySqlConnection
mysqlConn.ConnectionString = mysqlstrConn
 
Dim StrCommDate As String = "SELECT datum_zitting FROM " & myDatagridview.SelectedCells(8).Value.ToString()
Dim StrCommNotes As String = "SELECT extra_notes FROM " & myDatagridview.SelectedCells(8).Value.ToString()
 
mysqlConn.Open()
 
Dim mysqlCommDate As New MySqlCommand(StrCommDate, mysqlConn)
Dim mysqlCommNotes As New MySqlCommand(StrCommNotes, mysqlConn)
 
Dim mysqlAdapter As MySqlDataAdapter = New MySqlDataAdapter
Dim mysqlTable As DataTable = New DataTable
'Dim mysqlReader As MySqlDataReader

Now I'm stuck. I know what to do, but how...

Anyone? Thanks in advance.

[Radjesh Klauke]

Anyone?

[jmcilhinney]

First up, you need to get your database design sorted. It's a very bad idea to have separate tables when the data is basically the same. You should absolutely not have a separate table for dates for each office. You should have a table for offices and a table for dates and each date record contains an office ID. You can then query the database for all dates with a specific office ID. You can read the results into a List(Of Date) using a DataReader and call ToArray or you can use populate a DataTable using a DataAdapter and use LINQ to get an array of Dates. Once you've got your array, you use just as you did in your original code.

[Radjesh Klauke]

It seems that I first need to learn how to design proper databases. Any suggestions where to look?

[motil]

this is exactly what i suggested and even give you example of how to do it... read closely.

[Radjesh Klauke]

I know m8. But then I also have to re-design my whole database.
But the issue remains that I still don't know how to pull the dates into the monthcalendar.

[jmcilhinney]

the issue remains that I still don't know how to pull the dates into the monthcalendar.

Um, like this maybe.

You can then query the database for all dates with a specific office ID. You can read the results into a List(Of Date) using a DataReader and call ToArray or you can use populate a DataTable using a DataAdapter and use LINQ to get an array of Dates. Once you've got your array, you use just as you did in your original code.

[Radjesh Klauke]

Ai... accidentally set this one as resolved...

You can read the results into a List(Of Date) using a DataReader and call ToArray or you can use populate a DataTable using a DataAdapter and use LINQ to get an array of Dates. Once you've got your array, you use just as you did in your original code.

Uuuuuh.... very nice, but don't know how to code that. Also searched the web for examples how to load "date" from a server, but nothing...

[jmcilhinney]

You're trying to complicate something that's very simple. Retrieving data from a database is the same regardless of the data type. If you know how to get any data from a database then you know how to get dates from a database. Just execute a query exactly as you usually would. The data you get back will be Dates if the data your retrieved is dates.