Cannot add integer or long column to microsoft access table using ado.net

  • Thread starter Thread starter lado1976
  • Start date Start date
L

lado1976

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.
 
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 said:
It's the field name 'Position' - change that, and your code executes
without error. Must be a reserved word.

--
Brendan Reynolds

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();
}
}
}
}
Click to expand...
Click to expand...
 
Back
Top