My program creates columns at runtime with the following code:
I add data into the newly inserted column with the following code: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()
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.)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")
Approach 1:
Approach 2:Code: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 3:Code: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")
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.Code: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




Reply With Quote
