Hi, I have a problem.

I can't add integer or long columns to ms access table using ado.net.

Could you just take a look at it. I think sql syntax is correct.

The weird thing is that in another program it is working and it is the
same code.

If you comment out these two lines of code:

"IDParent INTEGER, "+
"Position INTEGER, "+

then everything is ok, the table is created.

The problem is I can't add LONG or INTEGER columns.

I don't know what's going on, maybe it's some kind of a bug?

Sample usage:

Test.exe c:\\test.mdb




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

namespace Test
{
public class Test
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
Test t = new Test();

if (args.Length ==1)
{
t.Execute(args[0]);
}
}

private string connectionString = "";
private OleDbConnection connection;

private bool CreateTableWorkTopics()
{
string sqlText = "CREATE TABLE WorkTopics "+
"(IDWorkTopic COUNTER, "+
"IDParent INTEGER, "+
"Position INTEGER, "+
"Title CHAR(255), "+
"CONSTRAINT WorkTopics_PK PRIMARY KEY (IDWorkTopic))";

ExecuteNonQuery(sqlText);
return true;
}

private void ExecuteNonQuery(string sqlText)
{
OleDbCommand cmd = new OleDbCommand(sqlText, connection);
cmd.CommandType = CommandType.Text;

cmd.ExecuteNonQuery();
}

public void Execute(string path)
{
try
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"+
"Data Source=\""+path+"\";"+
"User ID=Admin;"+
"Password=";

// create mdb file
if (!System.IO.File.Exists(path))
{
ADOX.CatalogClass catalog = new ADOX.CatalogClass();
catalog.Create(connectionString);
catalog = null;
}

connection = new OleDbConnection(connectionString);
connection.Open();

CreateTableWorkTopics();

connection.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
Console.ReadLine();
}
}
}
}

It's the field name 'Position' - change that, and your code executes without
error. Must be a reserved word.

--
Brendan Reynolds

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi, I have a problem.
>
> I can't add integer or long columns to ms access table using ado.net.
>
> Could you just take a look at it. I think sql syntax is correct.
>
> The weird thing is that in another program it is working and it is the
> same code.
>
> If you comment out these two lines of code:
>
> "IDParent INTEGER, "+
> "Position INTEGER, "+
>
> then everything is ok, the table is created.
>
> The problem is I can't add LONG or INTEGER columns.
>
> I don't know what's going on, maybe it's some kind of a bug?
>
> Sample usage:
>
> Test.exe c:\\test.mdb
>
>
>
>
> using System;
> using System.Data;
> using System.Data.OleDb;
>
> namespace Test
> {
> public class Test
> {
> /// <summary>
> /// The main entry point for the application.
> /// </summary>
> [STAThread]
> static void Main(string[] args)
> {
> Test t = new Test();
>
> if (args.Length ==1)
> {
> t.Execute(args[0]);
> }
> }
>
> private string connectionString = "";
> private OleDbConnection connection;
>
> private bool CreateTableWorkTopics()
> {
> string sqlText = "CREATE TABLE WorkTopics "+
> "(IDWorkTopic COUNTER, "+
> "IDParent INTEGER, "+
> "Position INTEGER, "+
> "Title CHAR(255), "+
> "CONSTRAINT WorkTopics_PK PRIMARY KEY (IDWorkTopic))";
>
> ExecuteNonQuery(sqlText);
> return true;
> }
>
> private void ExecuteNonQuery(string sqlText)
> {
> OleDbCommand cmd = new OleDbCommand(sqlText, connection);
> cmd.CommandType = CommandType.Text;
>
> cmd.ExecuteNonQuery();
> }
>
> public void Execute(string path)
> {
> try
> {
> connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"+
> "Data Source=\""+path+"\";"+
> "User ID=Admin;"+
> "Password=";
>
> // create mdb file
> if (!System.IO.File.Exists(path))
> {
> ADOX.CatalogClass catalog = new ADOX.CatalogClass();
> catalog.Create(connectionString);
> catalog = null;
> }
>
> connection = new OleDbConnection(connectionString);
> connection.Open();
>
> CreateTableWorkTopics();
>
> connection.Close();
> }
> catch (Exception ex)
> {
> Console.WriteLine(ex.ToString());
> Console.ReadLine();
> }
> }
> }
> }
>

BTW: If you really need to use the field name 'Position', the following also
works ...

"[Position] INTEGER, "+

Note the square brackets.

--
Brendan Reynolds

"Brendan Reynolds" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> It's the field name 'Position' - change that, and your code executes
> without error. Must be a reserved word.
>
> --
> Brendan Reynolds
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hi, I have a problem.
>>
>> I can't add integer or long columns to ms access table using ado.net.
>>
>> Could you just take a look at it. I think sql syntax is correct.
>>
>> The weird thing is that in another program it is working and it is the
>> same code.
>>
>> If you comment out these two lines of code:
>>
>> "IDParent INTEGER, "+
>> "Position INTEGER, "+
>>
>> then everything is ok, the table is created.
>>
>> The problem is I can't add LONG or INTEGER columns.
>>
>> I don't know what's going on, maybe it's some kind of a bug?
>>
>> Sample usage:
>>
>> Test.exe c:\\test.mdb
>>
>>
>>
>>
>> using System;
>> using System.Data;
>> using System.Data.OleDb;
>>
>> namespace Test
>> {
>> public class Test
>> {
>> /// <summary>
>> /// The main entry point for the application.
>> /// </summary>
>> [STAThread]
>> static void Main(string[] args)
>> {
>> Test t = new Test();
>>
>> if (args.Length ==1)
>> {
>> t.Execute(args[0]);
>> }
>> }
>>
>> private string connectionString = "";
>> private OleDbConnection connection;
>>
>> private bool CreateTableWorkTopics()
>> {
>> string sqlText = "CREATE TABLE WorkTopics "+
>> "(IDWorkTopic COUNTER, "+
>> "IDParent INTEGER, "+
>> "Position INTEGER, "+
>> "Title CHAR(255), "+
>> "CONSTRAINT WorkTopics_PK PRIMARY KEY (IDWorkTopic))";
>>
>> ExecuteNonQuery(sqlText);
>> return true;
>> }
>>
>> private void ExecuteNonQuery(string sqlText)
>> {
>> OleDbCommand cmd = new OleDbCommand(sqlText, connection);
>> cmd.CommandType = CommandType.Text;
>>
>> cmd.ExecuteNonQuery();
>> }
>>
>> public void Execute(string path)
>> {
>> try
>> {
>> connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"+
>> "Data Source=\""+path+"\";"+
>> "User ID=Admin;"+
>> "Password=";
>>
>> // create mdb file
>> if (!System.IO.File.Exists(path))
>> {
>> ADOX.CatalogClass catalog = new ADOX.CatalogClass();
>> catalog.Create(connectionString);
>> catalog = null;
>> }
>>
>> connection = new OleDbConnection(connectionString);
>> connection.Open();
>>
>> CreateTableWorkTopics();
>>
>> connection.Close();
>> }
>> catch (Exception ex)
>> {
>> Console.WriteLine(ex.ToString());
>> Console.ReadLine();
>> }
>> }
>> }
>> }
>>
>
>

Thank you very much. You really helped me.

BTW: I have found list of microsoft access reserverd words. If someone
is curious, here it is:

http://support.microsoft.com/?id=321266