with code and append it to the database file ?
Printable View
with code and append it to the database file ?
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
Can you give me the complete command statement ?
Thanks for your help !
:)
*bump*
What DB are you using?
MS Access (.mdb) . :)
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();
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 . :D :D
Hmm , if anyone got .NET solution for MS Access , plz inform me . I really need that .
Thank you . :)
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 OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database 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 OLEDB:Don'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);
}
}
}
}
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 .
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 + .... ?
:rolleyes:Code: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.Quote:
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 + .... ?
:rolleyes:Code:Data Source=C:\Documents and Settings\Glenn\My Documents\My Data Sources\RentalInfo.mdb
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.
Omg , it's my fault dude . I thought this thread for creating new database file . :eek: :D . I'll tweak the code and put the database there and try it again . Thanks for that ggprogram . :)
No worries , if it runs once , I'll run twice . Got that . ;)Quote:
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.