Easy beginner's problem

[MNovy]

Hello,

I have trouble with access on MDB files through ADO due to incomplete examples in the web or
annoying information lacks.

What I want:
- I want to create once a complete new MDB
- I want to open an existing MDB and add a new table
- I want to open an existing MDB and add columns into an existing table
- I want want to insert data in the existing columns
- I want to update values in the columns

I was testing some code and this is what I have so far. Could you please help me with
the UPDATING for example?

Thanks in advance!



using System;
using ADOX;
using ADODB;
using System.IO;

public class Program
{
static int Main()
{
if (File.Exists("C:\\Exercise1.md")) File.Delete("C:\\Exercise1.mdb");


// CREATE FILE

ADOX.CatalogClass catDatabase;
string strDatabase = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Exercise1.mdb'";

catDatabase = new ADOX.CatalogClass();
catDatabase.Create(strDatabase);
Console.WriteLine("A new Microsoft JET database named " +
"Exercise1.mdb has been created\n");




// CREATE DB with a table
ConnectionClass conDatabase = new ConnectionClass();
try
{
object objAffected;
string strStatement = "CREATE TABLE Customers(FirstName char, " +
"MiddleName String, " +
"LastName varchar);";
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Exercise1.mdb';";

conDatabase.Open(strConnection, "", "", 0);
conDatabase.Execute(strStatement, out objAffected, 0);
}
finally
{
conDatabase.Close();
}




// ADD column
ADODB.Connection conADO = new ADODB.Connection();
object obj = new object();
string stStatement;

conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Exercise1.mdb'", "", "", 0);
stStatement = "ALTER TABLE Customers ADD COLUMN CellPhone string;";
conADO.Execute(stStatement, out obj, 0);

conADO.Execute("INSERT INTO Customers(FirstName, MiddleName, LastName) " +
"VALUES('James', 'Carlton', 'Male');", out obj, 0);

// UPDATING OF A COLUMN??? -------------------------------------------------------


Console.WriteLine("A new column named CellPhone has been added " +
"to the Employees table.");

conADO.Close();

return 0;
}
}

[vuyiswam]

Now when you update , you have to depend on a Unique key called a Primary key. Now the Bad thing about your Code that creates a table is that it does not create that Field that will make your Records unique, what i mean its difficult to update a table that is not normalized because you will not know which record to Delete, Update, but the Insert as you have done it will work. Just create an Identity Field. Another thing is that its not a good idea to createa table dynamically its better you decide what you are going to store and create the Stored Procedures to insert , Delete and Update your Records. Your Update code will look like this

see how i did it from the following Article



http://www.codeproject.com/KB/cs/N-Tier22.aspx