Too long Record in Insert by OleDb (Ms Access)

  • Thread starter Thread starter Supel
  • Start date Start date
S

Supel

I have problem with insert and update record by OleDb in Access. I have
table test with 151 columns and I can insert or update record.
I have 2 exception error
1. "Record is too long" when I try to insert
2. "Too many name columns" in update query
Somebody can help me and explain where is error?
Has OleDb max number parameters and max size record in query updet or
insert??

Thanks.
Robert
There are example code:

void Init()//Main function
{
OleDbConnection myConnect = Connect();
try
{

//HERE ARE ERRORS!!! when a can insert or update
InsertTable(myConnect);
//or
UpdateTable(myConnect);


}
catch(System.Exception e2)
{

e2=e2;
}
finally
{ if(myConnect!=null)
myConnect.Close();
}
}

const int MAX_COLUMN_TO_UPDATE = 15;
const int MAX_COLUMN_TO_INSERT = 150;
OleDbConnection Connect()
{ OleDbConnection myConn = null;
myConn = new OleDbConnection(ConnStr);
myConn.Open();
return myConn;
}

string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=D:\\test.mdb;Mode=ReadWrite|Share Deny None;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: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;";

void CreateTable( OleDbConnection myConnect)
{ StringBuilder StrQuery = new StringBuilder(1000);
StrQuery.Append("create table test1 (unq int");
for(int i=0;i<MAX_COLUMN_TO_INSERT;i++)
StrQuery.AppendFormat(",kol{0} text(100)",i+1);
StrQuery.Append(")");

OleDbCommand myCommand = new OleDbCommand(StrQuery.ToString(),myConnect);
myCommand.ExecuteNonQuery();
}



void InsertTable( OleDbConnection myConnect)
{ OleDbCommand myCommand = new OleDbCommand("",myConnect);
StringBuilder StrQuery = new StringBuilder(1000);
StrQuery.Append("INSERT INTO test1 (unq");
for(int i=0;i<MAX_COLUMN_TO_INSERT;i++)
StrQuery.AppendFormat(",kol{0}",i+1);
StrQuery.Append(") VALUES(?");

OleDbParameter Par = new OleDbParameter("unq", OleDbType.Integer);
Par.Value = 0;
myCommand.Parameters.Add(Par);

for(int i=0;i<MAX_COLUMN_TO_INSERT;i++)
{ StrQuery.Append(",?");
// OleDbParameter Par = new OleDbParameter(string.Format("(kol{1}",i+1),
OleDbType.VarChar,)
OleDbParameter Par1 = new
OleDbParameter(string.Format("kol{0}",i+1),"123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_");
myCommand.Parameters.Add(Par1);
}
StrQuery.Append(")");
myCommand.CommandText = StrQuery.ToString();
myCommand.ExecuteNonQuery();
}
void UpdateTable( OleDbConnection myConnect)
{ OleDbCommand myCommand = new OleDbCommand("",myConnect);
StringBuilder StrQuery = new StringBuilder(1000);
StrQuery.Append("UPDATE test1 SET ");
for(int i=0;i<MAX_COLUMN_TO_UPDATE;i++)
{ if(i!=0)
StrQuery.Append(",");
StrQuery.AppendFormat("kol{0}=?",i+1);
OleDbParameter Par1 = new OleDbParameter(string.Format("kol{0}",i+1),"1");
myCommand.Parameters.Add(Par1);
}

StrQuery.Append("WHERE unq=?");
OleDbParameter Par = new OleDbParameter("unq", OleDbType.Integer);
Par.Value = 1;
myCommand.Parameters.Add(Par);

myCommand.CommandText = StrQuery.ToString();
myCommand.ExecuteNonQuery();
}
 
Hi Supel,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that your update query throwns exception and
says the record was too big. If there is any misunderstanding, please feel
free to let me know.

Here in the Access help file, I found this limitation document. You can
check to see if your query has exceed the limits.

Access database

Attribute
Maximum

Microsoft Access database (.mdb) (Microsoft Access database: A collection
of data and objects (such as tables, queries, or forms) that is related to
a particular topic or purpose. The Microsoft Jet database engine manages
the data.) file size
2 gigabytes minus the space needed for system objects (system object:
Database objects that are defined by the system, such as the table
MSysIndexes, or by the user. You can create a system object by naming the
object with USys as the first four characters in the object name.).

Number of objects in a database
32,768

Modules (including forms and reports with the HasModule property set to
True)
1,000

Number of characters in an object name
64

Number of characters in a password
14

Number of characters in a user name or group name
20

Number of concurrent users
255


Table

Attribute
Maximum

Number of characters in a table name
64

Number of characters in a field name
64

Number of fields in a table
255

Number of open tables
2048; the actual number may be less because of tables opened internally by
Microsoft Access

Table size
2 gigabyte minus the space needed for the system objects

Number of characters in a Text field
255

Number of characters in a Memo field
65,535 when entering data through the user interface;
1 gigabyte of character storage when entering data programmatically

Size of an OLE Object field
1 gigabyte

Number of indexes in a table
32

Number of fields in an index
10

Number of characters in a validation message
255

Number of characters in a validation rule
2,048

Number of characters in a table or field description
255

Number of characters in a record (excluding Memo and OLE Object fields)
when the UnicodeCompression property of the fields is set to Yes
4,000

Number of characters in a field property setting
255


Query

Attribute
Maximum

Number of enforced relationships
32 per table minus the number of indexes that are on the table for fields
or combinations of fields that are not involved in relationships

Number of tables in a query
32

Number of fields in a recordset
255

Recordset size
1 gigabyte

Sort limit
255 characters in one or more fields

Number of levels of nested queries
50

Number of characters in a cell in the query design grid
1,024

Number of characters for a parameter in a parameter query
255

Number of ANDs in a WHERE or HAVING clause
99

Number of characters in an SQL statement
approximately 64,000

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
You're welcome, Supel.

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top