Add a Column to Database

[billboy]

I am using the following code to add a row to an exisitng databse and now I am trying to add a column, the code executes ok with no error messages and the column is being added to the datatable, but it does not appear to be added to the database can anyone see what I am doing wrong?

 Dim da As New OleDbDataAdapter("Select * From " & Me.OpenFileDialog1.SafeFileName & "", con)
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow
        Dim dsNewCol As DataColumn = New DataColumn("Condition", System.Type.GetType("System.String"))
        ds.Tables("dataset").Columns.Add(dsNewCol)
        dsNewRow = ds.Tables("dataset").NewRow()

        dsNewRow.Item("Condition") = Me.subj_cond_txt.SelectedIndex

        If Me.subjectaddtxt.Text <> "" Then
            dsNewRow.Item("AddressDisplay") = Me.subjectaddtxt.Text
        End If
        If Me.subj_mls_txt.Text <> "" Then
            dsNewRow.Item("MLSNUM") = Me.subj_mls_txt.Text
        End If
        If Me.subj_orglp_txt.Text <> "" Then
            dsNewRow.Item("ListPriceOrig") = CInt(Me.subj_orglp_txt.Text)
        End If

        dsNewRow.Item("Beds") = Me.subj_bed_txt.Text
        dsNewRow.Item("BathsTotal") = Me.subj_ba_txt.Text
        If Me.subj_dom_txt.Text <> "" Then
            dsNewRow.Item("DaysOnMarket") = Me.subj_dom_txt.Text
        End If

        dsNewRow.Item("FirePlace") = Me.subj_fp_txt.Text
        dsNewRow.Item("NumGarages") = Me.subj_gar_txt.SelectedIndex
        dsNewRow.Item("SqFtTotal") = Convert.ToInt32(Me.subj_gla_txt.Text)
        dsNewRow.Item("PropertyAttached") = Me.subj_housetype_txt.Text
        dsNewRow.Item("ListDate") = Me.DateTimePicker1.Value
        If Me.subj_lotsize_txt.Text <> "" Then
            dsNewRow.Item("LotSize") = CInt(Me.subj_lotsize_txt.Text)
        End If
        If Me.subj_lp_txt.Text <> "" Then
            dsNewRow.Item("ListPrice") = CInt(Me.subj_lp_txt.Text)
        End If
        dsNewRow.Item("PoolDesc") = Me.subj_pool_txt.SelectedIndex
        dsNewRow.Item("SpecialConditions") = Me.subj_housetype_txt.SelectedIndex
        dsNewRow.Item("Stories") = Me.subj_levels_txt.Text
        dsNewRow.Item("ViewDesc") = Me.subj_view_txt.SelectedIndex
        If Me.subj_yb_txt.Text <> "" Then
            dsNewRow.Item("YearBuilt") = Me.subj_yb_txt.Text
        End If
        dsNewRow.Item("Style") = Me.subj_style_txt.SelectedIndex
        If Me.subj_hoaduestxt.Text <> "" Then
            dsNewRow.Item("AssocFee") = Me.subj_hoaduestxt.Text
        End If
        dsNewRow.Item("ListStatus") = Me.subj_status_txt.SelectedIndex
        ds.Tables("dataset").Rows.Add(dsNewRow)
        da.Update(ds, "dataset")

        MsgBox("New Record added to the Database")

[ForumAccount]

What type of database? MS Access? You can't just add a column to a DataTable and expect it to be added to a database, it's not that simple. You will have to run an ALTER TABLE query against the table. See here.

[billboy]

What type of database? MS Access? You can't just add a column to a DataTable and expect it to be added to a database, it's not that simple. You will have to run an ALTER TABLE query against the table. See here.

its a tab deliminated database

[billboy]

I tried the following but get a syntax error in the executenonquery or Operation not supported on a table that contains data.

ColCreate = New OleDbCommand("ALTER TABLE " & Me.OpenFileDialog1.SafeFileName & " ADD COLUMN Condition varchar(20)", con)
        ColCreate.ExecuteNonQuery()

[Shaggy Hiker]

That specific error may well relate to the database type you are using, and there is an ugly way around it, but I think the more relevant question is: Why are you trying to add columns?

There are reasons that you might want to alter the design of an existing database at the running of a program, but they are generally things that happen just one time. If you know the column to add, and this will happen only one time, then go ahead and add it in the database, rather than via code. If you expect to be adding many columns over time, then you have the wrong design. So, rather than dealing with what is going on in this particular case, it seems best to understand what the real problem is, first.

On the other hand, you answered that this was "a tab delimitted" database. That doesn't actually sound like a database, at all, it sounds like a text file, in which case some of the alternatives to adding columns will not work.

[billboy]

That specific error may well relate to the database type you are using, and there is an ugly way around it, but I think the more relevant question is: Why are you trying to add columns?

There are reasons that you might want to alter the design of an existing database at the running of a program, but they are generally things that happen just one time. If you know the column to add, and this will happen only one time, then go ahead and add it in the database, rather than via code. If you expect to be adding many columns over time, then you have the wrong design. So, rather than dealing with what is going on in this particular case, it seems best to understand what the real problem is, first.

On the other hand, you answered that this was "a tab delimitted" database. That doesn't actually sound like a database, at all, it sounds like a text file, in which case some of the alternatives to adding columns will not work.

I have created a program that reads a 3rd party database, actually you are correct its a tab delimated file or comma delimated .txt or .csv. These are files the user has and my program works with those files to perform calculations and such.

The reason for wanting to add a column is so the user can add a record of his own for future reference. I would like the record they add to have an additional column so it can be uniquely identified when the file is accessed in the future

Perhaps there is a better way to do this that i havent considered I am would appreciate your feedback and or suggestions . I am still very new to programming as a hobby so i am always open to learning

Thanks

[Shaggy Hiker]

If you have write permission to the file, then you should be able to add a column, but I would not be surprised to hear that an ALTER TABLE query would not work against a text file. You can use SQL to read those files, as you appear to know, but I'm not sure that ALTER TABLE is supported, or if it even makes sense for text files.

On the other hand, as long as you have write permission, you can write out the text file, replacing the one that is there. If you are working with lots of datatables or datasets, then it might make more sense to make the files XML files, as you would then be able to read or write the files into and out of datasets or datatables with considerable ease (it takes about one line for each operation). If you are stuck with the format, then go ahead and add the column to the datatable, as you are doing, but you will need to write out the file in a different fashion. I don't have any example of writing datatables to a delimitted file, that I can think of, but there are probably examples on this forum, so you could try searching for one. It would overwrite the file with the new information, though, so it would be a matter of reading in the entire file, making the changes, then writing the whole thing back out, including the changes.

If that won't work, somebody might have a suggestion for a workable ALTER TABLE query for text files. I don't KNOW that it is impossible, it would just surprise me a bit if it was possible.