|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Column Data Type problem (ADO.NET, C#)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 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 Show quote "nvx" <nvx2***@hotmail.com> wrote in message news:1154415468.801728.32420@s13g2000cwa.googlegroups.com... > 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 > 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): Show quote > 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" <nvx2***@hotmail.com> wrote in message > news:1154415468.801728.32420@s13g2000cwa.googlegroups.com... >> 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 >> > >  
Other interesting topics
|
|||||||||||||||||||||||
