Advanced database actions

[tommesbx]

Hi there.

 

I got a couple of questions about ADO.NET concerning the creation of new tables into an existing (or even better a new) data base.

 

My first question would be: is there any possibilty to create a new table into an existing (Access) database ? If I'm creating a new OleDbConnection, I always have to submit the name of an existing database. Furthermore, the OleDbDataAdapter requires a fitting SQL-statement. But how should I declare such if there isn't a table in my database?

To be more graphical: I created a new empty Access-mdb "empty.mdb". First line of code to access it of course would be

 

Dim oleDBConn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=h:\empty.mdb")

 

Now the cricital action begins. If I'm doing something like

 

Dim oleDBDataAdap As New OleDb.OleDbDataAdapter("select * from whatEverTable", oleDBConn)

 

I'm stuck. I can't submit a Select-Statement as there simply is no table in the database. My thought was to create such via the OleDBCommand ("CREATE table dummy [...]"), but this solution required an open connection. Then I came to the conclusion to do it with the help of a dataset. So my code looked like this:

 

Dim oleDBConn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=h:\empty.mdb")

Dim oleDBDataAdap As New OleDb.OleDbDataAdapter("select * from newTable", oleDBConn)

Dim oleDBCB As New OleDb.OleDbCommandBuilder(oleDBDataAdap)

Dim dsTest As New DataSet()

 

Dim dtable As New DataTable("newTable")

 

Try

 

With dtable.Columns

.Add("FirstEntry", System.Type.GetType("System.Int32"))

.Add("SomeText", System.Type.GetType("System.String"))

.Add("EvenMoreText", System.Type.GetType("System.String"))

End With

 

dsTest.Tables.Add(dtable)

 

oleDBDataAdap.Update(dsTest, "newTable")

 

Catch er As IO.IOException [...]

 

This simpy did nothing. Even though I could run this code, the database seemes to be untouched, as there still isn't any table in it.

Sow, to shorten things: how on earth can I submit a newly created table to my database through the dataadapter?

 

This leads me to my second question: as you can see I'm using a "dummy" database. This is because I couldn't find a way to create a new database like you could back in VB6 (CreateDatabase). Any hints here?

 

Thanks in advance,

TommesBX

[Nerseus]

You don't want to use an Adapter which is used to query data. You want a command object so you can issue your "CREATE TABLE" command. Here's a sample:

OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\temp\\db1.mdb");
conn.Open();
OleDbCommand cmd = new OleDbCommand("CREATE TABLE tblTest (Col1 Number, Col2 Text)", conn);
Debug.WriteLine(cmd.ExecuteNonQuery());

 

You can't create tables by using a DataSet, as you saw. The Update method will attempt to take any rows in your DataTables that are changed and update the corresponding table in your database. But the table you created was ONLY in the DataSet, not the Database so no update occurred.

 

I don't know of any way to create an empty Access database through code. There may be a way, I just don't know :)

 

-nerseus