Column Data Type problem (ADO.NET, C#)

[nvx]

Hi,
I need to be able to add or remove columns from an Access database
table (table is bound to a DataGridView). I know I have to use ALTER
TABLE to do this. After I add a System.String column (AllowDBNull =
false, MaxLength = 35) to my DataSet and a VARCHAR(35) column to the DB
("ALTER TABLE tbl ADD col VARCHAR(35) NOT NULL" ->
cmd.ExecuteNonQuery()) I'm able to generate correct INSERT, DELETE and
UPDATE commands using CommandBuilder, but dAdapter.Update(dSet, "tbl")
ends up in an exception "Data types in the expression do not match." In
case I do not add any columns .Update(...) is executed without an
exception being thrown.

Here's the code:

dSet.Tables["tbl"].Columns.Add("col");
dSet.Tables["tbl"].Columns["col"].MaxLength = 35;

// column is filled in with values which I need to be there (in case
// this would be done after AllowDBNull = false an exception
// would be thrown...)

dSet.Tables["tbl"].Columns["col"].AllowDBNull = false;

// a column is added to the DataGridView and bound to the dSet column
// created above now

// other code here

OleDbCommand cmd = new OleDbCommand("ALTER TABLE tbl ADD col
VARCHAR(35) NOT NULL", conn);
conn.Open();
cmd.ExecuteNonQuery(); // columns in dSet match columns in DB now
bindingSource.EndEdit();
dAdapter.Update(dSet, "tbl");
conn.Close();

I'm not able to figure out why this doesn't work... Any help would be
appreciated.

With regards
nvx

 

[Nitin]

Why do you want to remove a column, If you want to hide a column from
datagrid just set autogenerate datacolums to false and add bound columns to
the grid.
Regards

 

[nvx]

Hi Nitin,
thank you for your response.

I'm afraid this is not what I need. I need user to be able to add or
remove columns and then save them to the DB file. For number of reasons
I can't afford to use AutoGenerateColumns. Although that means I have to
add/remove columns to/from the DataGridView myself, I do not care, since
I tried to use AutoGenerateColumns and it was causing more problems than
the obvious advantages. Anyway, AutoGenerateColumns does not solve my
problem...

If you meant to suggest I could have a large amount of columns in the DB
file and hide those I do not need, this is not a solution either. As the
number of columns might be ten or ten thousand, it would be a disk space
wasting (inefficiently large file) and would seriously slow down DB
opening (mainly when loading a remote file over a network). You can
easily imagine a really angry user finding fifty column database file
can't fit on his USB flash disk...

With regards
nvx


Nitin napsal(a):

Why do you want to remove a column, If you want to hide a column from
datagrid just set autogenerate datacolums to false and add bound columns to
the grid.
Regards

Hi,
I need to be able to add or remove columns from an Access database
table (table is bound to a DataGridView). I know I have to use ALTER
TABLE to do this. After I add a System.String column (AllowDBNull =
false, MaxLength = 35) to my DataSet and a VARCHAR(35) column to the DB
("ALTER TABLE tbl ADD col VARCHAR(35) NOT NULL" ->
cmd.ExecuteNonQuery()) I'm able to generate correct INSERT, DELETE and
UPDATE commands using CommandBuilder, but dAdapter.Update(dSet, "tbl")
ends up in an exception "Data types in the expression do not match." In
case I do not add any columns .Update(...) is executed without an
exception being thrown.

Here's the code:

dSet.Tables["tbl"].Columns.Add("col");
dSet.Tables["tbl"].Columns["col"].MaxLength = 35;

// column is filled in with values which I need to be there (in case
// this would be done after AllowDBNull = false an exception
// would be thrown...)

dSet.Tables["tbl"].Columns["col"].AllowDBNull = false;

// a column is added to the DataGridView and bound to the dSet column
// created above now

// other code here

OleDbCommand cmd = new OleDbCommand("ALTER TABLE tbl ADD col
VARCHAR(35) NOT NULL", conn);
conn.Open();
cmd.ExecuteNonQuery(); // columns in dSet match columns in DB now
bindingSource.EndEdit();
dAdapter.Update(dSet, "tbl");
conn.Close();

I'm not able to figure out why this doesn't work... Any help would be
appreciated.

With regards
nvx