Question save a dynamically created column of a data table to the database?

[rdcsekar]

I have to dynamically add new columns to a table in a database and also save it for persistence. I am unable to do that. I am submitting the code sample I used to do the task.



Dim newcol = NewDataColumn("AccountNumber", GetType(Integer))
newcol.AllowDBNull = true

TestaddcolsDataSet.bank.Columns.Add(newcol)
str = TestaddcolsDataSet.bank.Columns.Count
TestaddcolsDataSet.bank.Rows.Add(7, "Stephen","HSBC bank","52368965212")
Try

Me.BankTableAdapter.Update(TestaddcolsDataSet.bank)

Catch ex AsException

MsgBox("unable to update")
EndTry

I have 3 exiting columns in the table and want to add a fourth column with the above code. With the MsgBox code, I could find that the new column is created but after closing the application, it is not saved in the database.Can somebody help me to correctly code this problem?
Thanks in advancr to all the contributors
　

 

[jmcilhinney]

You can't save data to a column in a database table unless that column exists in that table. It's not going to magic into existence just because you have a DataColumn in a DataTable. Not only that, your table adapter doesn't contain any SQL code to save data to that column even if it exists. The whole point of a typed DataSet is that it is generated specifically to match the schema of your database so if you want to change your database then you need to change your typed DataSet as well.

Get rid of that code that adds the DataColumn. That is completely useless. What you need to do is modify the schema of your database manually, then run the configuration wizard again on your Data Source so that it updates the appropriate DataTable and table adapter in your typed DataSet. You then use that column exactly as you do all the others.

 

[rdcsekar]

Thanks for the reply.
But my problem is that the no. of columns are likely to be added as the application is running. I have no way of knowing the no. of column beforehand to hardcode in my database.
Is there any workaround?

 

[jmcilhinney]

This is not the sort of problem that you work around. If you want the application to work that way then you have to design the application to work that way from the ground up, which you haven't. To be frank, what you're talking sounds rather silly. It's conceivable that an application would work that way but it would be EXTREMELY unusual. I think that you should provide us with a more detailed explanation of what this application actually does and why you think that adding arbitrary columns to the database at run time is a requirement. We can then evaluate whether what you're trying to do even makes sense. I'm guessing that it doesn't but, if it does, we can suggest how to design your application properly to accommodate it.

 

[Herman]

Adding columns on the fly is wrong, period. You might want to add a column if for example you are updating a product database with a feature that didn't exist before, but otherwise your database schema should always be predefined, never dynamic.

 

[rdcsekar]

First of all I would like to thank the contributors for taking time to read and answer my question. I don't have a formal education in programming and so my explanation of the problem may irk some experts here for being unable to explain my problem as a Programmer.
The situation is like this:
I am developing an application for a company that sells its products in bulk to its buyers. The buyers after buying the product may pay the amount in single or many installments. My table structure looks something like this.
The payment table has columns like this:
column 1 : contains the Buyer identity
column 2: contains the purchase identification details of the buyer
column 3: purchase amount
column 4: payment details
This is wherr my problem lies. If the payment is not made in single installment, it can be done in many( which is most usually the case). So in this place I need to store these multiple payment details in multiple columns (which may correspond to the no. of installments paid)
I hope I have made my problem clear. You might suggest that my database design is ugly ( with due apologies to lack of formal programming education), but this is what I would do if I were asked to record manually.
Now please tell me how to proceed further.

 

[Herman]

Nope, usual mistake. Not multiple columns, multiple ROWS and multiple TABLES.

Table Customers has customer details, and an unique ID column.
Table Items has item details.
Table PurchHdr has the transaction header (including a foreign key ExCustId)
Table PurchDet has the transaction detail (including a foreign key ExItemId, a price, taxes for the detail line, etc...)
Table PaymentHdr has the payment header (including a foreign key ExPurchHdrId)
Table PaymentDet has the payment details. In this table you can enter many payment details for a single payment header.

Also what you are designing right now is called a Customer Aging. This is a basic accounting object, and you need to calculate it to know how much a customer owes you at a particular date. I would read up on some CRM documentation to know more.