I'm trying to make a query that inserts multiple records in one query.

This is what I have:



function loadSFO()
{
var sfo=new ActiveXObject('scripting.FileSystemObject');
var floc=sfo.OpenTextFile(document.getElementById("artfile").value);
var str=floc.readAll();
var cut=str.split('\n');
var sql="INSERT INTO SFO (Manager, Supervisor, Employee, RGN, Cust_ID, MDN,Feat_CD, PKG_DESC, DateOf, User_ID, Line_Act_Date, Coached, Correct, Notes, Status) VALUES";
for(var j=0;j<cut.length-1;j++)
{
var cut3=cut[j].split(',');
for(d=0;d<cut3.length;d++)
{
if(cut3.length!=13)
{
alert("There are errors in your csv file. Please ensure you replaced all instances of commas with a | symbol before transfering to csv!");
return;
}
if(cut3[8]=='Y'||cut3[8]=='N')
{
alert("It appears you have selected the wrong report type!");
return;
}
}
}
for(var i=0;i<cut.length-1;i++)
{
var re=/"/g
var re2=/'/g
var re3=/'|'/g
var fix=cut[i].replace(re,'');
var fix=fix.replace(re2,'');
var cut2=fix.split(',');
for(x=0;x<cut2.length;x++)
{
cut2[x]=cut2[x].replace(/\|/g,',');
}
sql+="('"+cut2[1]+"','"+cut2[2]+"','"+cut2[3]+"','"+cut2[4]+"','"+cut2[5]+"','"+cut2[6]+"',+'"+cut2[7]+"','"+cut2[8]+"',#"+cut2[9]+"#,'"+cut2[11]+"','"+cut2[12]+"',false,false,'None',false),";
}
getDataBase();
sql=sql.substring('0',sql.length-1);
rs.Open(sql+";");
dbc.Close;
alert('Process Completed Successfully!');
}


I've tried this various ways based on different sites examples but none seem to work. Depending on how I have it I either get the message that there are uneeded characters at the end of the SQL statement or Missing semicolon at the end of SQL statement. Am I just going about this all wrong? It works just fine if I open a new recordset for each insert, but it is taking to long. It's inserting about 1500 records and takes upwords of 10 minutes to process. Any help is appreciated.

Basscyst

Just to add if I do it like this:




function getDataBase4(sql)
{
dbc = new ActiveXObject("ADODB.Connection");
dbc.Provider = "Microsoft.Jet.OLEDB.4.0";
dbc.ConnectionString = "Data Source=\\\\carcrdsan1\\carcrd-ops$\\Dept_Apps\\BMS\\ART2.mdb";

dbc.Open //open the database connection with the above settings
dbc.execute(sql)
dbc.close;
}




function loadSFO()
{
var sfo=new ActiveXObject('scripting.FileSystemObject');
var floc=sfo.OpenTextFile(document.getElementById("artfile").value);
var str=floc.readAll();
var cut=str.split('\n');
var sql="";
for(var j=0;j<cut.length-1;j++)
{
var cut3=cut[j].split(',');
for(d=0;d<cut3.length;d++)
{
if(cut3.length!=13)
{
alert("There are errors in your csv file. Please ensure you replaced all instances of commas with a | symbol before transfering to csv!");
return;
}
if(cut3[8]=='Y'||cut3[8]=='N')
{
alert("It appears you have selected the wrong report type!");
return;
}
}
}
for(var i=0;i<cut.length-1;i++)
{
var re=/"/g
var re2=/'/g
var re3=/'|'/g
var fix=cut[i].replace(re,'');
var fix=fix.replace(re2,'');
var cut2=fix.split(',');
for(x=0;x<cut2.length;x++)
{
cut2[x]=cut2[x].replace(/\|/g,',');
}
getDataBase4("INSERT INTO SFO (Manager,Supervisor,Employee,RGN,Cust_ID,MDN,Feat_CD,PKG_DESC,DateOf,User_ID,Line_Act_Date,Coached,C orrect,Notes,Status) VALUES('"+cut2[1]+"','"+cut2[2]+"','"+cut2[3]+"','"+cut2[4]+"','"+cut2[5]+"','"+cut2[6]+"',+'"+cut2[7]+"','"+cut2[8]+"',#"+cut2[9]+"#,'"+cut2[11]+"','"+cut2[12]+"',false,false,'None',false);");
}

alert('Process Completed Successfully!');
}


It works just fine. But takes forever. BTW: I'm using ACCESS 2000. I think the first example may only work in MySQL 3.22.5 or later are multiple inserts just not supported in ACCESS 2000? I just ran this and it took 30 minutes to insert 1843 records. There just has to be a way to speed it up. Please help. :( :confused: