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 Im 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 isnt 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 Im doing something like

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

Im stuck. I cant 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 isnt 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 Im using a "dummy" database. This is because I couldnt find a way to create a new database like you could back in VB6 (CreateDatabase). Any hints here?

Thanks in advance,
TommesBX

 

[Nerseus]

You dont want to use an Adapter which is used to query data. You want a command object so you can issue your "CREATE TABLE" command. Heres 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 cant 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 dont know of any way to create an empty Access database through code. There may be a way, I just dont know

-nerseus