Not able to save a column added dynamically to an existing table
I need to add a new column in the database but cannot. Can someone help me. My code is as below
My code:
private void UpdateDatabase()
{
DataTable dtprojectinfo = GetTable("ProjectInfo");
DataTable dtparticipants = m_DBClient.GetTable(TABLETYPE.PARTICIPANTS);
DataColumn dc = new DataColumn("PaymentStatus");
dc.DataType = System.Type.GetType("System.Boolean");
dc.DefaultValue = false;
dtparticipants.Columns.Add(dc);
SaveTable(dtparticipants);
}
public DataTable GetTable(string TableName)
{
OleDbDataAdapter dataAdapter;
DataTable datatable = null;
OpenConnection();
dataAdapter = new OleDbDataAdapter();
datatable = new DataTable();
dataAdapter.SelectCommand = new OleDbCommand(@"SELECT * FROM [" + TableName + "]", m_DBConnection);
dataAdapter.SelectCommand.Parameters.Add("@Icon", OleDbType.Binary);
dataAdapter.Fill(datatable);
datatable.TableName = TableName;
return datatable;
}
Public void SaveTable(DataTable dataTable)
{
OleDbDataAdapter dataAdapter;
dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = new OleDbCommand(@""ALTER TABLE [" + ActualTableName + "] ADD COLUMN Payment TEXT(25)", m_DBConnection);
OleDbCommandBuilder cb = new OleDbCommandBuilder(dataAdapter);
dataAdapter.AcceptChangesDuringUpdate = true;
OpenConnection();
dataAdapter.Update(dataTable);
}
Re: Not able to save a column added dynamically to an existing table
I think you messed up Function SaveTable. The first thing you should do is add one column in database table. Only after this step is done, you can do the second step -- use DataAdapter and CommandBuilder to update data in that table. About the second step, you'd better check msdn help about CommandBuilder class.
Re: Not able to save a column added dynamically to an existing table
"dataAdapter.Update(dataTable);" does not update the table's schema, it only updates table's data.
You will have to run ALTER TABLE query through SqlCommand.ExecuteNonQuery() or other similar methods for your OLEDB.