Create new table ? [Resolved]

[Pirate]

with code and append it to the database file ?

[lschaeff1]

You can perform all these operations through a SQLCommand object by writing the command text for your operation, i.e. "Create Table etc......" then just perform an execute non query on the command object.

LS

[Pirate]

Can you give me the complete command statement ?

Thanks for your help !

[Pirate]

*bump*

[powdir]

What DB are you using?

[Pirate]

MS Access (.mdb) .

[ggprogram]

This might help. Change the CREATE PROCEDURE to CREATE TABLE and SQL to OLEDB

http://msdn.microsoft.com/library/de...operations.asp

[Visual Basic]
Dim createStr As String = "CREATE PROCEDURE InsertCategory " & _
" @CategoryName nchar(15), " & _
" @Identity int OUT " & _
"AS " & _
"INSERT INTO Categories (CategoryName) VALUES(@CategoryName) " & _
"SET @Identity = @@Identity " & _
"RETURN @@ROWCOUNT"

Dim createCMD As SqlCommand = New SqlCommand(createStr, nwindConn)
createCMD.ExecuteNonQuery()
[C#]
string createStr = "CREATE PROCEDURE InsertCategory " +
" @CategoryName nchar(15), " +
" @Identity int OUT " +
"AS " +
"INSERT INTO Categories (CategoryName) VALUES(@CategoryName) " +
"SET @Identity = @@Identity " +
"RETURN @@ROWCOUNT";

SqlCommand createCMD = new SqlCommand(createStr, nwindConn);
createCMD.ExecuteNonQuery();

[Pirate]

That's for SQL Server . Apprently there is no way for MS Access by .NET . anyways . I used ADOX and it seems to work fine , though I hate doing things through interoperate. but eh .

Thanks everyone . Edneeis gave me a good solution .

[Pirate]

Hmm , if anyone got .NET solution for MS Access , plz inform me . I really need that .

Thank you .

[ggprogram]

Here it is. It creates a table in an Access database file. then it creates a dataset on the fly and puts 10 records into the table. Then it updates the adapter and adds the records to the Access table.

Excuse the sloppy code. I never did this before and sort of hacked it out as I went along.

using System;
using System.Data;
using System.Data.OleDb;

namespace CreateDB
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
//
// TODO: Add code to start application here
//
Class1 myClass = new Class1();
myClass.CreateDB();
}

public void CreateDB()
{



OleDbConnection conn;
OleDbDataAdapter adapter;

string connString;


DataSet ds;

DataTable dtEmp = new DataTable("Employees");
dtEmp.MinimumCapacity = 100;
dtEmp.CaseSensitive = false;

// Create an ID
DataColumn dcEmpId = new DataColumn("EmpID");
dcEmpId.DataType = System.Type.GetType("System.Int32");
dcEmpId.AllowDBNull = false;
dtEmp.Columns.Add(dcEmpId);

// create columns
DataColumn dcFName = new DataColumn("FirstName");
dcFName.DataType = System.Type.GetType("System.String");
dtEmp.Columns.Add(dcFName);

DataColumn dcCity = new DataColumn("City");
dcCity.DataType = System.Type.GetType("System.String");
dtEmp.Columns.Add(dcCity);



// add the table to a dataset
ds = new DataSet();
ds.Tables.Add(dtEmp);


connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Documents and Settings\Glenn\My Documents\My Data Sources\RentalInfo.mdb;Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type=5;Jet OLEDBatabase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False";
//
conn = new OleDbConnection(connString);
conn.Open();

string createStr = "CREATE TABLE Employee(EmpID varchar (50) not null, City varchar (50), FirstName varchar (50)) " ;

try
{
OleDbCommand createCMD = new OleDbCommand(createStr, conn);
createCMD.ExecuteNonQuery();


adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand("SELECT * FROM Employee", conn);
OleDbCommandBuilder empCB = new OleDbCommandBuilder(adapter);


ds = new DataSet();
adapter.Fill(ds, "Employee");


// add data to dataset
DataRow myRow;
for(int i = 0; i < 10; i++)
{
myRow = dtEmp.NewRow();
myRow["EmpID"] = i + 1;
myRow["FirstName"] = "First"+ i;
myRow["City"] = "City"+i;

// Be sure to add the new row to the DataRowCollection.
dtEmp.Rows.Add(myRow);
Console.WriteLine(myRow.RowState);
}

adapter.Update(dtEmp);
}
catch (Exception e)
{

Console.WriteLine("Exception: {0}", e.Message);
Console.WriteLine(e.StackTrace);
}

}


}
}

[Pirate]

I'm testing it and error raised saying "An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll " on this line

string createStr = "CREATE TABLE Employee(EmpID varchar (50) not null, City varchar (50), FirstName varchar (50)) " ;

What the heck is that ?

Thank you ggprogram .

[Pirate]

This is one part of your connection string . It's expecting a database in this path with the name "RentalInfo.mdb" , Is this possible ? I mean I will create one . Would it be matter if I change this to :

Application.StartUp + .... ?

Data Source=C:\Documents and Settings\Glenn\My Documents\My Data Sources\RentalInfo.mdb

[ggprogram]

Originally posted by Pirate
This is one part of your connection string . It's expecting a database in this path with the name "RentalInfo.mdb" , Is this possible ? I mean I will create one . Would it be matter if I change this to :

Application.StartUp + .... ?

Data Source=C:\Documents and Settings\Glenn\My Documents\My Data Sources\RentalInfo.mdb

RentalInfo is the mdb file I was creating the table in. You should be able to change it to anything you want.

[ggprogram]

BTW, the code I gave you will generate an error if you run it a second time. Like I said, it was just quick and dirty. If you create the table in the MDB file and then run the program again,it will try to create a table with CREATE TABLE that already exists, which throws an error.

[Pirate]

Omg , it's my fault dude . I thought this thread for creating new database file . . I'll tweak the code and put the database there and try it again . Thanks for that ggprogram .

[Pirate]

Originally posted by ggprogram
BTW, the code I gave you will generate an error if you run it a second time. Like I said, it was just quick and dirty. If you create the table in the MDB file and then run the program again,it will try to create a table with CREATE TABLE that already exists, which throws an error.

No worries , if it runs once , I'll run twice . Got that .