-2

Thanks everyone for thumbing down my question and not helping me out. This website is amazing.

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.

Saif
  • 1
  • 2

1 Answers1

0

I suspect that your DataSet (ds) is out of sync.

First, confirm that the new column is present within the DataSet: For any one of your three approaches, put a break-point just before the loop starts, and take a look at ds.Tables("SchoolMaticsDatabase").Columns and confirm that the new column is in fact listed there. Alternatively, put a Debug.Print column.ColumnName inside the loop and look for it in the Output window.

Second, assuming that the new column is in the Columns member, I would recommend making a little project on the side to explore your issue further. Make it simple, nothing fancy. Let it create a column (avoid using dates as names at first), give it a value, update its value, see how it goes.

Good luck!

rskar
  • 4,607
  • 25
  • 21