Question column datatype while creating a table..

[aydinozdemir]

hello again..firstly thank you for your previous helps on my way learning access and oledb. i recently have a problem with reading a programmatically created database table.
i use the code below to create the table..command works with no problem..
[XCODE]cmd = New OleDbCommand("CREATE TABLE 2011 (Month numeric (2) PRIMARY KEY, Day1 text,Day2 text, Day3 text, Day4 text, Day5 text, Day6 text, Day7 text)", con)[/XCODE]

after creating the table i try to fill the tables content into a combobox..only data i get into the combobox is something like null items..i tried to filter null and recurring records but no success.i still get empty combobox..

Dim ItemList As New ArrayList()
Dim row As DataRow 'Represents a row of data in Systems.data.datatable
          For Each row In dsogretmen.Tables(0).Rows
             If row(1).ToString.Length > 0 Then
                For i = 1 To 8
                   If Not IsDBNull(row(i).ToString) Then
                      If Not ItemList.Contains(row(i)) Then
                          ItemList.Add(row(i).ToString)
                      End If
                   End If
               Next
            End If
         Next

ComboBox2.Items.Clear()
For i = 0 To ItemList.Count - 1
     ComboBox2.Items.Add(ItemList.Item(i))
     ComboBox2.Sorted = True
Next

on other side, with the same code, i can fill the combobox with table contents if the table was created manually from inside ms-access..
i tried different types but no success (varchar, integer)..what can the problem be and how to solve it..thanks for any help.[/COLOR]

 

[jmcilhinney]

OK, let's take a step back. Why are you programmatically creating a table named '2011'? You're not creating a new table for every year are you? That is absolutely not the way to use a database. If that is what you're doing then you should explain exactly what you're trying to achieve and let us help you design your database properly.

 

[aydinozdemir]

actually that was 06_2011.mdb, i am studiying on a project to learn and understand database and visual basic relations..
my aim is to create a database for a teacher or teachers (i may add more teachers in future).
this teacher gives common lessons to students who are studying in different branchs and classrooms.
for each new month and new year, new databases will be added to the archive folder, and wont be deleted as i want to store them for future analysis;
1- database name will be given from current Dates Month and Year (06_2011, 07_2011) (i use a combobox to select past dates)
2- in each database there will be at least one teachers datatable with the name of that teacher(John_White)
3- rows will be added right after datatable creation, using that months day count(28,29,30 or 31)
4- columns will be added as one days workhours(columns name= Hour1, Hour2, Hour3..., Hour8)
5- record data will be added is, the Classroom where that teacher has been during a workhour (Science, Music, Biology, Chemistry, Foreign Language...etc)
may i show you a screenshot from the datatable?it is below..

my goal is to make 1 month analysis of that datatable..eg.
1- howmany workhours of that teacher is spare? or howmany hours did that teacher give lessons in total?
2- howmany workhours did that teacher give lesson to a Biology class or Music class?
3- Howmany different classes did the teacher give lesson? (Chemistry, Biology...8 in total in this example)
4- İf there is another teacher how can i prevent superposition of Workhours with Classrooms? (İn Day 3 of 06_2011 and Hour2, Biology Classroom, is there more than one teachers lesson there?)
...and similar kind of analysis..i am confused about the database-datatable-archive folder structure.any suggestion on structure will be appreciated.

my recent problem is;
i am able to do all analysis(may be with a poor code or database structure), beside the 3rd one..
in datagridview When i write and save "Arts" , and try to populate it into a combobox or listbox(with the code in my first post) i just get empty rows if that table shown in datagridview was been added programmatically..although i am not able to see it in combobox, i can see it in datagridview, edit it and can save it..