New Table Button

W1lk0

Does anyone know how to code a button to create a new instance of a table

• I have a database application that uses an MS Access db as the data source.
• I then have 4 tabs in the app, each with a different table showing in them - grid view.
  
• I have created a button that creates a new tab and a context menu for renaming the tab.
• Now all i need is a way to copy a new template table into the new tab via a button click....although this means that it would have to automatically create the grid layout and everything as well!

Anyone know how this is done or where i could find out, or is it just simply too must to ask for one button to achieve all this

Thanks

Matt Neerincx

You can just execute the following SQL statement to make a copy of the design of the table:

select * into table2 from table1 where 1=0

You can execute this using OledbCommand.ExecuteNonQuery.

This will do most of the work however if there are indexes on source table this will not create the indexes on the target table. So if indexes are important this may not be a super clean solution. There is not a nice copy table structure and associated indexes function. The in depth way to do this would be to use either ADOX or OledbConnection (see http://dotnetjunkies.com/Tutorial/1055A2E4-944F-4586-BB44-1992C34B7C54.dcik for example) to examine all the metadata on the table and re-create a new table using this info.

cverdon

Hi,
If you want to actually create a table in the database you have to execute an SQL statement like:
CREATE Table MyTable ...

If you want to dynamically create a new tab page with a grid and multiple controls, the easiest way is to create a user control. That control should contain all the controls the appear in a tab. Once that's done you just have to create a new instance of that control and add it to the new tab page. Don't forget to set ctrl.Dock = DockStyle.Fill;

Good luck,
Charles

W1lk0

Cheers for the answers.

Anyone know how I would go about adding a control programmatically via a button click Of course there is the positioning of it as well which needs to be the same as in all the other tabs as it's a replication of the other tabs.

All I really have is 4 tabs each with there own table (2004, 2005, 2006, 2007) and I want to be able to add a new tab at the end of each year.

Creating and adding a control dynamically sounds like the way to go, but could do with a few pointers or a good book recommendation.

Thanks

Matt Neerincx

Creating a control dynamically is not too difficult. But a better idea is to just create the control where you want it using the UI editor and then mark it as Visible=false. Then in your code all you need to do is set txtFoo.Visible = true to display the control. The benefit of doing it this way is getting the alignment setup properly is easier in UI versus caculating the x,y postion etc...

If you still need to create control dynamically, all you have to do is examine the code that the UI editor creates for you, it's all there. So create a simple form and add the control you want then go view the code to see how it's done.

cverdon

Hi,

If you do not want to modify your application next year to add a control, it's better to create them dynamically. Here are the basic steps:

1. Project, add new item
2. UserControl --> lets call it ContentTabControl, OK
3. The design view of the usercontrol is shown.
4. Drop a DataGridView on the design surface or copy paste some of your controls from your current dialog.
5. Open the code file
6. Add a public property named Year which will receive the year the tab will display
7. In the control's load event add the code to retreive the data (use the Year appropriately)
8. In the main dialog remove all the controls which are now in the ContentTabControl & associated code
9. The dialog should contain a tab control without tab pages
10. In the load event of the dialog, add code such as this:

Code Snippet

int thisYear = DateTime.Now.Year;

for (int year = 2004; year <= thisYear; year++) {

TabPage page = new TabPage(String.Format("{0}", year));

ContentTabControl content = new ContentTabControl();

content.Year = year;

page.Controls.Add(content);

content.Dock = DockStyle.Fill;

tabControl1.TabPages.Add(page);

}

Hope this helps,

Charles

W1lk0

Wow, thanks Charles. There is a lot I can learn from doing this as well!

I'll let you know how it goes.

W1lk0

hmmm....okay the first thing i need to do is create a copy of the template_table that i have in the Access database then give it a name (the next year)! This is the bit I'm struggling with at the moment.

Then i have to bind this new table to the new user control......or am I going backwards here

Should the control have the template_table already in place and then just rename that to create a new instance of the table - is this what the code is doing

Or, have i got to create a new table from the template_table and THEN add this to the new tab

Sorry, i'm a little confused.

Matt Neerincx

To make your life a little easier, you could easily do this:

#1. Add a Year field to the table.

#2. When you add new records ensure the Year is set to the proper year value.

#3. In each tab, when you fill the dataset, add the Year as a parameter to your SQL statement, i.e.

select * from MasterTable where Year=2004

This way you don't have to create a new table for each year, rather have the year as a filter on the table.

To bind to the records to the control use the DataGridView control and set it's DataSource property to a DataSet that you fill with the data.

Search MSDN for info on how to use the DataSet. Essentially you can fill the DataSet with records with about 4-5 lines of code, you would use OledbDataAdapter for MS Access to fill the DataSet. However I don't know all your requirements if you require updating the records then it becomes a bit more complicated.