Answered by:
How will I create a Table from C# form to MS Access database?

Question
-
Hi, Please have a look at the code and tell me what should I need to do in order to make this code create a new table in my database with PublisherID as Primary Key....
please reply ASAP!
thanks
----------------------------------------------------------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace PracticalsEight
{
public partial class Publisher : Form
{
public Publisher()
{
InitializeComponent();
}
private void main2_btn_Click(object sender, EventArgs e)
{
Database Form = new Database();
Form.Show();
this.Hide();
}
private void create2_btn_Click(object sender, EventArgs e)
{
string dbconnection = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=BookCSharp.mdb";
string dbcommand = "Select * from Publisher;";
OleDbDataAdapter DataAdapterTest = new OleDbDataAdapter(dbcommand, dbconnection);
DataSet ds = new DataSet();
DataAdapterTest.Fill(ds);
DataTable publisher = new DataTable("Publisher");
DataColumn publisherId = new DataColumn();
publisherId.DataType = Type.GetType("System.Int32");
publisherId.ColumnName = "PublisherID";
publisherId.AllowDBNull = false;
publisherId.Unique = true;
publisherId.AutoIncrement = true;
publisherId.AutoIncrementSeed = 1;
publisherId.AutoIncrementStep = 1;
publisher.Columns.Add(publisherId);
DataColumn publishername = new DataColumn();
publishername.DataType = Type.GetType("System.String");
publishername.ColumnName = "PublisherName";
publishername.AllowDBNull = true;
publisher.Columns.Add(publishername);
DataColumn[] PK = new DataColumn[1];
PK[0] = publisher.Columns["PublisherID"];
publisher.PrimaryKey = PK;
MessageBox.Show("A new Data Table is successfully Created");
}
}
}
Asif- Moved by CoolDadTx Friday, December 16, 2011 6:31 PM Winforms related (From:Visual C# General)
Answers
-
Hi Asif,
So I will give you some last example, howto create a foreign key. Since you had problems with default parameters using ADOX I show you howto do that using the DDL:
private void button2_Click(object sender, EventArgs e) { // Create a foreing key using ADO.NET and DDL // Does not check for an existing foreign key .. using ( OleDbConnection connection = new OleDbConnection( _ConnectionString )) { try { OleDbCommand cmd = new OleDbCommand(); cmd.Connection = connection; cmd.CommandType = CommandType.Text; cmd.CommandText = "ALTER TABLE Books ADD FOREIGN KEY (PublisherKey) REFERENCES Publisher(PublisherID)"; connection.Open(); cmd.ExecuteNonQuery(); MessageBox.Show("Successfully created foreign key", "ForeignKey"); } catch (Exception ex) { MessageBox.Show(ex.Message, "Could not create ForeignKey"); } } }
In general I would propose when you have to work with MS Access files the following approach:
- Use ADOX just for creating the MS Asccess file itself, that is the *.mdb file and for nothing else. This is in contrast to the first example that I have shown to you earlier. But since you suffered already from COM parameters, I would not go any further with the usage of ADOX.
- Use ADO.NET and the DDL (data definition language) to create your tables, views, relations etc.
I could image of other possibilities but I found this approach is working for MS Access quite nicely.
Cheers,
Stefan
- Marked as answer by mUHAMMAD_aSIF Sunday, December 18, 2011 9:35 PM
All replies
-
-
-
Hi Asif,
You can not create a new Access Database file with ADO.NET, but you have to use ADOX instead. This is a COM component that must be referenced by the Visual Studio Project. I have create a small example that shows
- howto create a new Access Database
- howto add tables and indices to that database
Please be aware that Microsoft recommends to use one of the SQL server editions instead of MS Access. For further information please refer to the MSDN documentation and knowledgebase:
- http://support.microsoft.com/default.aspx?scid=kb;en-us;317881
- http://msdn.microsoft.com/en-us/library/windows/desktop/ms681520(v=vs.85).aspx
And here now the said example:
using System; using System.Collections.Generic; using System.ComponentModel; using System.Text; using System.Windows.Forms; // Add references for the following COM Objects to the project: // // - Microsoft ActiveX Data Objects 6.0 Library // - Microsoft ADO Ext. 6.0 for DDL and Security // using ADOX; using ADODB; namespace AccessTest { public partial class Form1 : Form { const string _ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=BookCSharp.mdb"; private Catalog OpenDatabase() { Catalog catalog = new Catalog(); Connection connection = new Connection(); try { connection.Open( _ConnectionString); catalog.ActiveConnection = connection; } catch (Exception) { catalog.Create(_ConnectionString); } return catalog; } private void button1_Click(object sender, EventArgs e) { // Only for demonstration purposes, no error checks: // This code will only work as long as the table "Publisher" does not exist // First create an new database if necessary Catalog cat = OpenDatabase(); // Create a new table "Publisher" using ADOX ... Table table = new Table(); table.Name = "Publisher"; cat.Tables.Append(table); // Add Column "PublisherID" with Autoincrement ADOX.Column col = new Column(); col.Name = "PublisherID"; col.ParentCatalog = cat; col.Type = ADOX.DataTypeEnum.adInteger; col.Properties["Nullable"].Value = false; col.Properties["AutoIncrement"].Value = true; table.Columns.Append(col); // Add column "PublisherName" col = new Column(); col.Name = "PublisherName"; col.ParentCatalog = cat; col.Type = ADOX.DataTypeEnum.adWChar; col.DefinedSize = 50; col.Attributes = ColumnAttributesEnum.adColNullable; table.Columns.Append(col); // Make "PublisherID" the primary key ADOX.Index index = new ADOX.Index(); index.PrimaryKey = true; index.Name = "PK_Publisher"; index.Columns.Append("PublisherID", table.Columns["PublisherID"].Type, table.Columns["PublisherID"].DefinedSize); table.Indexes.Append(index); MessageBox.Show("A new Data Table is successfully Created"); } public Form1() { InitializeComponent(); } } }
Hope that helps,
Stefan
-
I have tried nd modified your code but its not working...
I have added the references but it is showing some errors, is there any easy way to do this without using old references.
I have generated the sollowing code its just giving exception error...
-----------------------------------------------------------
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace PracticalsEight
{
public partial class Publisher : Form
{
private OleDbConnection con;
private void main2_btn_Click(object sender, EventArgs e)
{
Database Form = new Database();
Form.Show();
this.Hide();
}
private void create2_btn_Click(object sender, EventArgs e)
{
// Only for demonstration purposes, no error checks:
// This code will only work as long as the table "Publisher" does not exist
// First create an new database if necessary
// Create a new table "Publisher" using ADOX ...
DataTable publishers = new DataTable("Publishers");
// Add Column "PublisherID" with Autoincrement
DataColumn publisherKey = new DataColumn();
publisherKey.DataType = Type.GetType("System.Int32");
publisherKey.ColumnName = "PublisherKey";
publisherKey.AllowDBNull = false;
publisherKey.Unique = true;
publisherKey.AutoIncrement = true;
publisherKey.AutoIncrementSeed = 1;
publisherKey.AutoIncrementStep = 1;
publishers.Columns.Add(publisherKey);
// Add column "PublisherName"
DataColumn publisherName = new DataColumn();
publisherName.DataType = Type.GetType("System.String");
publisherName.ColumnName = "PublisherName";
publishers.Columns.Add(publisherName);
//set primary key that is CarId column
DataColumn[] PK = new DataColumn[1];
PK[0] = publishers.Columns["PublisherKey"];
publishers.PrimaryKey = PK;
//
DataRow newRow;
newRow = publishers.NewRow();
newRow["PublisherKey"] = "1";
newRow["PublisherName"] = "Pakistan Publication";
publishers.Rows.Add(newRow);
//
DataTable books = new DataTable("Books");
DataSet ds = new DataSet();
//
string dbconnection = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=BookCSharp.mdb";
string dbcommand = "Create Table Publishers(PublisherKey integer Primary Key, “ + “PublisherName char(50))";
OleDbConnection conn = new OleDbConnection(dbconnection);
OleDbCommand comm = new OleDbCommand(dbcommand, conn);
conn.Open();
int c = (int)comm.ExecuteNonQuery();
conn.Close();
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from Publishers", conn);
OleDbCommandBuilder dcb = new OleDbCommandBuilder(adapter);
conn.Open();
adapter.Update(ds, "Publishers");
ds.AcceptChanges();
conn.Close();
ds.Tables.AddRange(new[] { books, publishers });
//
ds.Relations.Add("BookPublisher", books.Columns["BookKey"], publishers.Columns["PublisherKey"]);
MessageBox.Show("A new Data Table is successfully Created");
}
public Publisher()
{
InitializeComponent();
con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=BookCSharp.mdb");
}
private void delete2_btn_Click(object sender, EventArgs e)
{
}
private void insert2_btn_Click(object sender, EventArgs e)
{
}
private void label3_Click(object sender, EventArgs e)
{
}
private void label2_Click(object sender, EventArgs e)
{
}
private void label1_Click(object sender, EventArgs e)
{
}
private void textBox3_TextChanged(object sender, EventArgs e)
{
}
private void textBox2_TextChanged(object sender, EventArgs e)
{
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
}
}
}
Asif -
Hi Asif,
I have tested my code on Window 7, there it is working fine. Did you actually add the references to the COM components and added also the "using" statements for ADODB and ADOX?
Your code seems not to use ADOX at all. As I stated before, ADOX is not ADO.NET but a COM component. You can not create a new Access Database file from ADO.NET. You could potentially find a way around that by providing an empty access dabase file - but in this case you coud also provide an access file that contains already all required tables ...
Cheers,
Stefan
-
I am grateful to you but it says
Error 1 No overload for method 'Open' takes '1' arguments C:\Documents and Settings\mUHAMMAD aSIF\My Documents\Visual Studio 2008\Projects\WindowsFormsApplication1\WindowsFormsApplication1\Form1.cs 28 17 WindowsFormsApplication1
Error 2 No overload for method 'Append' takes '1' arguments C:\Documents and Settings\mUHAMMAD aSIF\My Documents\Visual Studio 2008\Projects\WindowsFormsApplication1\WindowsFormsApplication1\Form1.cs 59 13 WindowsFormsApplication1
Asif -
-
-
Hi Asif,
OK, I have tried it with ADO 2.8 and ADOX 2.8, still works fine. Question: Do these problems occur on your machine also in my ummodified code? If you have modified my code I would kindly ask you to compile just my umodified code ...
Cheers,
Stefan
-
-
-
OK, I think you are not using the .NET framework 4 right? Which version are you using?
In that case some dummy parameters may be needed. I will try to find the information, but maybe later today. Currently I am quite busy with doing some other stuff ...
Cheers,
Stefan -
-
Hi Asif,
OK, I found exaclty the 4 mentioned errors when building the demo for the .NET framework 2.0. The problem is, that you have to pass all parameters to the proxy objects that are created for the COM objects. So I fixed the following line by providing parameters:
- Using now: connection.Open(_ConnectionString, null, null, 0);
instead of: connection.Open(_Connectionstring);
- Using now: table.Columns.Append(col, ADOX.DataTypeEnum.adIUnknown, 0);
instead of: table.Columns.Append(col);
- Using now: table.Columns.Append(col, ADOX.DataTypeEnum.adIUnknown, 0);
instead of: table.Columns.Append(col);
- Using now: table.Indexes.Append(index, null);
instead of: table.Indexes.Append(index);
I admit - it looks a little bit ugly, but it works ;-) The whole source looks that is able to compile and run also for .NET framework 2.0 now looks like this:
using System; using System.Collections.Generic; using System.ComponentModel; using System.Text; using System.Windows.Forms; // Add references for the following COM Objects to the project: // // - Microsoft ActiveX Data Objects 2.8 Library // - Microsoft ADO Ext. 2.8 for DDL and Security // using ADOX; using ADODB; namespace AccessTest { public partial class Form1 : Form { const string _ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=BookCSharp.mdb"; private Catalog OpenDatabase() { Catalog catalog = new Catalog(); Connection connection = new Connection(); try { connection.Open(_ConnectionString, null, null, 0); catalog.ActiveConnection = connection; } catch (Exception) { catalog.Create(_ConnectionString); } return catalog; } private void button1_Click(object sender, EventArgs e) { // Only for demonstration purposes, no error checks: // This code will only work as long as the table "Publisher" does not exist // First create an new database if necessary Catalog cat = OpenDatabase(); // Create a new table "Publisher" using ADOX ... Table table = new Table(); table.Name = "Publisher"; cat.Tables.Append(table); // Add Column "PublisherID" with Autoincrement ADOX.Column col = new Column(); col.Name = "PublisherID"; col.ParentCatalog = cat; col.Type = ADOX.DataTypeEnum.adInteger; col.Properties["Nullable"].Value = false; col.Properties["AutoIncrement"].Value = true; table.Columns.Append(col, ADOX.DataTypeEnum.adIUnknown, 0); // Add column "PublisherName" col = new Column(); col.Name = "PublisherName"; col.ParentCatalog = cat; col.Type = ADOX.DataTypeEnum.adWChar; col.DefinedSize = 50; col.Attributes = ColumnAttributesEnum.adColNullable; table.Columns.Append(col, ADOX.DataTypeEnum.adIUnknown, 0); // Make "PublisherID" the primary key ADOX.Index index = new ADOX.Index(); index.PrimaryKey = true; index.Name = "PK_Publisher"; index.Columns.Append("PublisherID", table.Columns["PublisherID"].Type, table.Columns["PublisherID"].DefinedSize); table.Indexes.Append(index, null); MessageBox.Show("A new Data Table is successfully Created"); } public Form1() { InitializeComponent(); } } }
Cheers,
Stefan
- Using now: connection.Open(_ConnectionString, null, null, 0);
-
Hi Stefan,
another kind of error has been generated in program.cs
I am using Visual C# 2010 Express,
the error is as follow:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
namespace WindowsFormsApplication1
{
static class Program
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
}
---------------------------
The type or namespace name 'Form1' could not be found (are you missing a using directive or an assembly reference?) C:\Documents and Settings\mUHAMMAD aSIF\My Documents\Visual Studio 2010\Projects\WindowsFormsApplication1\WindowsFormsApplication1\Program.cs 18 33 WindowsFormsApplication1
Cheers!
Asif -
Hi Stefan,
THANKS VERY MUCH!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I have sorted it out in a main form
which is my actual project.....
I am going to put my working code on it but want to know that Can you help me a little more in making the Data Relation (nameof datarelation) with the newly created "Publisher" Table and the existing "Books" Table including [parent column] and a [child column] where the "Books" Table has the following fields (AuthorKey, BookKey'Primary Key', Title, Pages, PublisherKey) and "Publisher"Table has following fields (PublisherKey, Publisher).
Thanks v. much!
---------------------------
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.OleDb; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using ADOX; using ADODB; namespace _1122700_PracticalEight { public partial class Publisher : Form { private OleDbConnection con; const string _ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=BookCSharp.mdb"; private Catalog OpenDatabase() { Catalog catalog = new Catalog(); Connection connection = new Connection(); try { connection.Open(_ConnectionString, null, null, 0); catalog.ActiveConnection = connection; } catch (Exception) { catalog.Create(_ConnectionString); } return catalog; } public Publisher() { InitializeComponent(); con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=BookCSharp.mdb"); } private void create2_btn_Click(object sender, EventArgs e) { { // Only for demonstration purposes, no error checks: // This code will only work as long as the table "Publisher" does not exist // First create an new database if necessary Catalog cat = OpenDatabase(); // Create a new table "Publisher" using ADOX ... Table table = new Table(); table.Name = "Publisher"; cat.Tables.Append(table); // Add Column "PublisherID" with Autoincrement ADOX.Column col = new Column(); col.Name = "PublisherID"; col.ParentCatalog = cat; col.Type = ADOX.DataTypeEnum.adInteger; col.Properties["Nullable"].Value = false; col.Properties["AutoIncrement"].Value = true; table.Columns.Append(col, ADOX.DataTypeEnum.adIUnknown, 0); // Add column "PublisherName" col = new Column(); col.Name = "PublisherName"; col.ParentCatalog = cat; col.Type = ADOX.DataTypeEnum.adWChar; col.DefinedSize = 50; col.Attributes = ColumnAttributesEnum.adColNullable; table.Columns.Append(col, ADOX.DataTypeEnum.adIUnknown, 0); // Make "PublisherID" the primary key ADOX.Index index = new ADOX.Index(); index.PrimaryKey = true; index.Name = "PK_Publisher"; index.Columns.Append("PublisherID", table.Columns["PublisherID"].Type, table.Columns["PublisherID"].DefinedSize); table.Indexes.Append(index, null); MessageBox.Show("A new Data Table is successfully Created"); } } private void insert2_btn_Click(object sender, EventArgs e) { OleDbCommand cmd = new OleDbCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "Insert into Publisher (PublisherID, PublisherName)" + "VALUES ('" + this.textBox2.Text + "' , '" + this.textBox3.Text + "')"; cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); con.Close(); MessageBox.Show("Record Inserted Successfully"); } private void delete2_btn_Click(object sender, EventArgs e) { OleDbCommand cmd = new OleDbCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "Delete from Publishers where (PublisherID=" + textBox2.Text + ")"; cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); con.Close(); MessageBox.Show("Record successfully Deleted"); } private void main2_btn_Click(object sender, EventArgs e) { Database Form = new Database(); Form.Show(); this.Hide(); } } }
Asif -
-
How we will show Table in listbox rather than text?
string dbconnection = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=BookCSharp.mdb"; string dbcommand = "Select * from Authors;"; OleDbDataAdapter DataAdapterTest = new OleDbDataAdapter(dbcommand, dbconnection); DataSet ds = new DataSet(); DataAdapterTest.Fill(ds); DataTable dt = ds.Tables[0]; foreach (DataRow row in dt.Rows) { ------------------> listBox1.Items.Add(row["AuthorKey"] + "(" + row["FirstName"] + ")" + "(" + row["Surname"] + ")"); }
Asif -
Hi Asif,
So I will give you some last example, howto create a foreign key. Since you had problems with default parameters using ADOX I show you howto do that using the DDL:
private void button2_Click(object sender, EventArgs e) { // Create a foreing key using ADO.NET and DDL // Does not check for an existing foreign key .. using ( OleDbConnection connection = new OleDbConnection( _ConnectionString )) { try { OleDbCommand cmd = new OleDbCommand(); cmd.Connection = connection; cmd.CommandType = CommandType.Text; cmd.CommandText = "ALTER TABLE Books ADD FOREIGN KEY (PublisherKey) REFERENCES Publisher(PublisherID)"; connection.Open(); cmd.ExecuteNonQuery(); MessageBox.Show("Successfully created foreign key", "ForeignKey"); } catch (Exception ex) { MessageBox.Show(ex.Message, "Could not create ForeignKey"); } } }
In general I would propose when you have to work with MS Access files the following approach:
- Use ADOX just for creating the MS Asccess file itself, that is the *.mdb file and for nothing else. This is in contrast to the first example that I have shown to you earlier. But since you suffered already from COM parameters, I would not go any further with the usage of ADOX.
- Use ADO.NET and the DDL (data definition language) to create your tables, views, relations etc.
I could image of other possibilities but I found this approach is working for MS Access quite nicely.
Cheers,
Stefan
- Marked as answer by mUHAMMAD_aSIF Sunday, December 18, 2011 9:35 PM