dcsimg
Results 1 to 8 of 8

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

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Dec 2010
    Posts
    20

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

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

    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:

    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 2:

    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")
    Approach 3:

    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
    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.

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,575

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

    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.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Dec 2010
    Posts
    20

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

    Quote Originally Posted by jmcilhinney View Post
    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.

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,575

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

    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.

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Dec 2010
    Posts
    20

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

    Quote Originally Posted by jmcilhinney View Post
    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.

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,575

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

    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.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Dec 2010
    Posts
    20

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

    Quote Originally Posted by jmcilhinney View Post
    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.

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,575

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

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width