adding columns on the fly to existing mdb

[jerommeke]

Hello,

i am a bit confused. I have a OleDb connection to a Microsoft Access
database and I can't seem to programmatically add new columns to the
table. If you look at the example below I try to add a column "ttt" to
a table called "tablename"

When I loop over the rows in the table I can update the existing column
"etc" without a problem, when i check it in the access database
afterwards, it has been succesfully updated. The new column "ttt",
however does only exist in memory. It never gets physically created. Am
I missing something obvious here?

regards

Jeroen van Vlierden (The Netherlands)

string table = "tablename";
DataSet ds = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter();
string ssql = "Select * FROM " + table;
adapter.SelectCommand = new OleDbCommand(ssql, myOleDbConnection);
adapter.Fill (ds, table);
ds.Tables

.Columns.Add(new DataColumn("ttt",
typeof(System.Int32)));
foreach( DataRow anyRow in ds.Tables

.Rows)
{
anyRow["Etc" ] = "ttttt"; //this works, because string column etc
already existed in table
anyRow["ttt" ] = 5; // the column exists in memory, this doesnt
generate an exception
}
adapter.Update(ds.Tables

);

 

[Cor Ligthert]

Jeroen

The dataset is a disconnected datasource which has nothing to do with
whatever database than that it can be filled from there using a dataadapter
and that with the data of that can be done an update on the existing
datatables. It is not to create whatever tables or parts of those.

When you want to add columns in your databasse, you can use therefore SQL
statements and the command.executenonquery.

I hope this helps,

Cor

 

[jerommeke]

Hi Cor,

Thanks for your answer, I already had a suspicion that this might be
the case. I have already solved the problem (using SQL-statements) for
adding columns, though it doesn't let me set the default value of a
field in an access database.

The problem is now that I also will be needing to define relationships
between tables from my c# code, which would be very easy if that could
be done with ADO .NET

I used to be a programmer in C++ and used DAO whenever i needed to do
work like this. In DAO you could copy tables, relationships etc from
other databases into existing databases or define new relationships and
tables on the fly.

Is there no possibility to do these kind of things using ADO .NET ?? Or
another API maybe?

regards

Jeroen

 

[Cor Ligthert]

Jeroen,

I used to be a programmer in C++ and used DAO whenever i needed to do
work like this. In DAO you could copy tables, relationships etc from
other databases into existing databases or define new relationships and
tables on the fly.

Is there no possibility to do these kind of things using ADO .NET ?? Or
another API maybe?

In ADONET I am sure there is not. From Ado what is preferable than I don't I
know.

Although that the people from that newsgroup are sometimes active in this
newsgroup also, would I ask this in your case in this newsgroup.

microsoft.public.data.ado

The ones there active know VBNet.

I hope you find your solution soon

Cor

 

[Jerommeke]

In ADONET I am sure there is not. From Ado what is preferable than I don't

I know.

Although that the people from that newsgroup are sometimes active in this
newsgroup also, would I ask this in your case in this newsgroup.

microsoft.public.data.ado

The ones there active know VBNet.

I hope you find your solution soon

Cor

Thanks Cor. I have already found a solution for adding columns though COM
Interoperability using both AdoDb and ADOX. I have faith that there will be
a way for me to do the same with the relationships.