How to insert a record into Access table?

V

VM

What's the fastest way to insert data into an Access table? I'm currently
using ExecuteNonQuery but it seems to take too long (should filling an mdb
table be slower than filling a datatable?). So this is my code to insert
several rows:

string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
Application.StartupPath + \\ZMData\\audit.mdb;
OleDbCommand myCommInsert = new OleDbCommand();
myCommInsert.Connection = new OleDbConnection(strDSN);
myCommInsert.Connection.Open();
sAuditRecord = sr.ReadLine(); /* First record in AZM Audit file to display
in datagrid */
while (sAuditRecord != null)
{
sQueryInsert = "";
sQueryInsert = "insert into audit ([col_row], [col_key], [col_Ref],
[col_input], [col_output], [col_notes], [col_action], [col_comments]," +
"[col_comments1], [col_comments2], [col_mark], [col_accept], [col_edit],
[col_reject], [col_default], [col_users])" +
"values ('" + sLine + "','" + sKey + "','" + sRefAddress + "','" +
sOldAddress + "','" + sOutAddress + "','" + sOutNotes + "','" +
sActionText + "','" + ZMNoteTxt + "','" + ZMNote1 + "','" + ZMNote2 +
"','" + sMark + "','" + sAccept + "','" + sEdit + "','" +
sReject + "','" + sDefault + "','" + sUsers + "')";
StreamWriter sw = new StreamWriter ("c:\\sql.txt");
sw.WriteLine(sQueryInsert);
sw.Close();
myCommInsert.CommandText = sQueryInsert;
myCommInsert.ExecuteNonQuery();
}

Thanks for your help
 
R

Roger Helliwell

What's the fastest way to insert data into an Access table? I'm currently
using ExecuteNonQuery but it seems to take too long (should filling an mdb
table be slower than filling a datatable?). So this is my code to insert
several rows:

string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
Application.StartupPath + \\ZMData\\audit.mdb;
OleDbCommand myCommInsert = new OleDbCommand();
myCommInsert.Connection = new OleDbConnection(strDSN);
myCommInsert.Connection.Open();
sAuditRecord = sr.ReadLine(); /* First record in AZM Audit file to display
in datagrid */
while (sAuditRecord != null)
{
sQueryInsert = "";
sQueryInsert = "insert into audit ([col_row], [col_key], [col_Ref],
[col_input], [col_output], [col_notes], [col_action], [col_comments]," +
"[col_comments1], [col_comments2], [col_mark], [col_accept], [col_edit],
[col_reject], [col_default], [col_users])" +
"values ('" + sLine + "','" + sKey + "','" + sRefAddress + "','" +
sOldAddress + "','" + sOutAddress + "','" + sOutNotes + "','" +
sActionText + "','" + ZMNoteTxt + "','" + ZMNote1 + "','" + ZMNote2 +
"','" + sMark + "','" + sAccept + "','" + sEdit + "','" +
sReject + "','" + sDefault + "','" + sUsers + "')";
StreamWriter sw = new StreamWriter ("c:\\sql.txt");
sw.WriteLine(sQueryInsert);
sw.Close();
myCommInsert.CommandText = sQueryInsert;
myCommInsert.ExecuteNonQuery();
}

Thanks for your help
Click to expand...

This is probably just a typo, but do you have an extra

sAuditRecord = sr.ReadLine();

inside your while loop? You've got an infinite loop if you don't.
Roger
 
R

Ron Allen

VM,
This insert statement needs to be parsed each time it is called which is
going to add some time. Try
adding "Values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" for the
values list. Create the Command before the loop with this and then add 16
parameters to the Command. I.E.
myCommand.Parameters.Add("@colrow", SqlDbType.Int, 0, "col_row");
just guessing the type of the parameter for now. Make sure to add the
parameters in order as OleDb client is position dependent.
Then inside the loop do
myCommand.Parameters["@colrow"].Value = Convert.ToInt32(sLine);
.... for the rest of the parmeters
That insert should run faster.

For more specific help on these types of topics see
microsoft.public.dotnet.framework.adonet.

Ron Allen
 
Top