VB.NET / Access DB - Column created at runtime is not Updating (Please Help ASAP))

[LionSword]

My program creates columns at runtime with the following code:

    Cmd = New OleDb.OleDbCommand("ALTER TABLE [Parent] ADD [" & ColumnDate & "] int", con)
    objCmd = New OleDb.OleDbCommand("ALTER TABLE [Parent] ADD [" & ColumnDate & "] int", con)
    objCmd.ExecuteNonQuery()

I add data into the newly inserted column with the following code:

 da.SelectCommand = New OleDb.OleDbCommand(sql, con)
    Dim cb As New OleDb.OleDbCommandBuilder(da)
    cb.QuotePrefix = "["
    cb.QuoteSuffix = "]"
    ds.Tables("SchoolMaticsDatabase").Rows(inc).Item(ColumnDate) = Hours * Num
    da.Update(ds, "SchoolMaticsDatabase")

All of the above works fine; the issue arises when I try to edit the information originally placed in the newly added column. These are the approaches that I have taken. (None of them give an error message; it simply won't update within the database.)

Approach 1:

da.SelectCommand = New OleDb.OleDbCommand(sql, con)
    Dim cb As New OleDb.OleDbCommandBuilder(da)
    cb.QuotePrefix = "["
    cb.QuoteSuffix = "]"

    For Each column As DataColumn In ds.Tables("SchoolMaticsDatabase").Columns
        If IsDate(column.ColumnName) = True Then
           ds.Tables("SchoolMaticsDatabase").Rows(inc).Item(column.ColumnName) = DataGridView3.Item(column.ColumnName, 0).Value
        End If
    Next
    da.Update(ds, "SchoolMaticsDatabase")

Approach 2:

da.SelectCommand = New OleDb.OleDbCommand(sql, con)
    Dim cb As New OleDb.OleDbCommandBuilder(da)
    cb.QuotePrefix = "["
    cb.QuoteSuffix = "]"
    For count = 13 To MaxColumns - 1
        ds.Tables("SchoolMaticsDatabase").Rows(inc).Item(count) = DataGridView3.Item(count, 0).Value
    Next
    da.Update(ds, "SchoolMaticsDatabase")

Approach 3:

  For Each column As DataColumn In ds.Tables("SchoolMaticsDatabase").Columns
        If IsDate(column.ColumnName) Then
           Cmd = New OleDb.OleDbCommand("UPDATE [Parent] SET [" & column.ColumnName & "]=" & DataGridView3.Item(column.ColumnName, 0).Value & " WHERE [ID]=" & inc + 1, con)
           objCmd = New OleDb.OleDbCommand("UPDATE [Parent] SET [" & column.ColumnName & "]=" & DataGridView3.Item(column.ColumnName, 0).Value & " WHERE [ID]=" & inc + 1, con)
           objCmd.ExecuteNonQuery()
         End If
    Next

I added a column to the table manually via opening the access database and all the above approaches work for editing data stored in that column. So I believe it is something to do with the fact that the columns are created at run time.

[jmcilhinney]

I thik the first thing to addres is why you're defining the columns as type 'int' when they are apparently supposed to store dates.

[LionSword]

I thik the first thing to addres is why you're defining the columns as type 'int' when they are apparently supposed to store dates.

They are not going to be storing dates the column name is going to be a date but they will be storing integers.

[jmcilhinney]

Now that I look closer, I think I understand what you're trying to do. You are adding a new column for each date, where the column name is the actual date and then the column contains numbers for that date. Is that correct? If so then that is absolutely the wrong way to use a database and you need to completely rethink your design. Please provide a full and clear description of what data you want to store and how you want to use it and we can advise the best way to do so.

[LionSword]

Now that I look closer, I think I understand what you're trying to do. You are adding a new column for each date, where the column name is the actual date and then the column contains numbers for that date. Is that correct? If so then that is absolutely the wrong way to use a database and you need to completely rethink your design. Please provide a full and clear description of what data you want to store and how you want to use it and we can advise the best way to do so.

What do you mean that is the wrong way to use a database? It has been working fine for all other sections of the program but the only issue i'm having is updating the data within a column that was created at run time. Whether the column needs updating in future (not the same run when the column was created) or straight after the column was created it simply won't update and I need a solution for this problem quickly as the project is due within a few days and I have been stuck on this for about a week almost. I can't, at this stage, change my whole project otherwise I wouldn't be finished in time.

[jmcilhinney]

If you are indeed adding a new column for each new date then that is the exact opposite of how to use a database. You add rows, not columns. If you need to add new data for each new date, you add a row for each date in a table whose columns are created at the outset and never change. Each date row contains an ID that is then stored in other records to indicate what date they are related to.

You really need to change that design. If this is a commercial application then you're providing a poor solution for your customer. If this is an academic project then you will undoubtedly get a poor mark for was is a very poor design.

[LionSword]

If you are indeed adding a new column for each new date then that is the exact opposite of how to use a database. You add rows, not columns. If you need to add new data for each new date, you add a row for each date in a table whose columns are created at the outset and never change. Each date row contains an ID that is then stored in other records to indicate what date they are related to.

You really need to change that design. If this is a commercial application then you're providing a poor solution for your customer. If this is an academic project then you will undoubtedly get a poor mark for was is a very poor design.

I understand what you mean but it has to be that way because each row is a student and the added columns are dates corresponding to a register. I'm making it for a tuition center for one of my friends.

[jmcilhinney]

No it doesn't have to be that way. Undr no circumstances, ever, should you use a database like that. You add rows, not columns. In your case, it sounds like you need three tables: one for students, one for dates and one for payments. Each time you get a new student you add a new row to the Student table with a StudentID. Every time there is a new date you add a new row to the Date table with a DateID. Every time a student makes a payment for a date you add a row to the Payment table with the appropriate StudentID and DateID. It's these relations between tables that give relational databases their name.