I have an upload form that get .csv file and update to the access database. Let say I have 38 records inside the .csv file that are to insert or update inside the access database.
When I upload the file, the form did insert and update the database. All the records are displaying properly except 15th and 16. The form inserted 15th record for about 14 times and skip the 16th record then display the rest.
Following is the source for my ASP script that seems to having problem -
Many thanks in advance.
Code:
<%
function SaveFiles
Dim uploadsDirVar
uploadsDirVar = Server.MapPath("admin")
Dim Upload, fileName, fileSize, ks, i, fileKey
Set Upload = New FreeASPUpload
Upload.Save(uploadsDirVar)
' If something fails inside the script, but the exception is handled
If Err.Number<>0 then Exit function
SaveFiles = ""
ks = Upload.UploadedFiles.keys
if (UBound(ks) <> -1) then
for each fileKey in Upload.UploadedFiles.keys
SaveFiles = SaveFiles & Upload.UploadedFiles(fileKey).FileName
next
end if
end function
if Request.ServerVariables("REQUEST_METHOD") = "POST" then
uploadFile = SaveFiles()
dim fs,f
set fs=Server.CreateObject("Scripting.FileSystemObject")
set f=fs.OpenTextFile(Server.MapPath("admin/" & uploadFile))
f.ReadLine
Set datesDataClear = Server.CreateObject("ADODB.Recordset")
datesDataClear.ActiveConnection = MM_midas_STRING
datesDataClear.Source = "delete * from dates"
datesDataClear.CursorType = 0
datesDataClear.CursorLocation = 2
datesDataClear.LockType = 1
datesDataClear.Open()
set datesDataClear = Nothing
Do
strAryWords = Split(f.ReadLine, ",")
Set datesDataAdd = Server.CreateObject("ADODB.Recordset")
datesDataAdd.ActiveConnection = MM_midas_STRING
if Ubound(strAryWords) = 6 then
sqlString = "insert into dates (schedule, reference, departure, return, nights, status, spaces) values ('" & strAryWords(0) & "','" & strAryWords(1) & "','" & strAryWords(2) & "','" & strAryWords(3) & "','" & strAryWords(4) & "','" & strAryWords(5) & "','" & strAryWords(6) & "')"
else
On Error resume Next
sqlString = "insert into dates (schedule, reference, departure, return, nights, status, spaces, comments, othercomments, paxpaying, paxrate, depositdue, depositamt, depositpaid, balancedue, jetski, otheraccom, finalamountdue, finalpaymentdue, finalbalancepaid, accomflightdetails) values ('" & strAryWords(0) & "','" & strAryWords(1) & "','" & strAryWords(2) & "','" & strAryWords(3) & "','" & strAryWords(4) & "','" & strAryWords(5) & "','" & strAryWords(6) & "','" & strAryWords(7) & "','" & strAryWords(8) & "','" & strAryWords(9) & "','" & strAryWords(10) & "','" & strAryWords(11) & "','" & strAryWords(12) & "','" & strAryWords(13) & "','" & strAryWords(14) & "','" & strAryWords(15) & "','" & strAryWords(16) & "','" & strAryWords(17) & "','" & strAryWords(18) & "','" & strAryWords(19) & "','" & strAryWords(20) & "')"
end if
datesDataAdd.Source = sqlString
datesDataAdd.CursorType = 0
datesDataAdd.CursorLocation = 2
datesDataAdd.LockType = 1
datesDataAdd.Open()
set datesDataAdd = Nothing
Loop Until f.AtEndOfStream
f.Close
set f=Nothing
set fs=Nothing
response.Redirect("admin_dates.asp")
else
Dim datesDataDown
Set datesDataDown = Server.CreateObject("ADODB.Recordset")
datesDataDown.ActiveConnection = MM_midas_STRING
datesDataDown.Source = "SELECT schedule, reference, departure, return, nights, status, spaces, comments, othercomments, paxpaying, paxrate, depositdue, depositamt, depositpaid, balancedue, jetski, otheraccom, finalamountdue, finalpaymentdue, finalbalancepaid, accomflightdetails FROM dates order by id"
datesDataDown.CursorType = 0
datesDataDown.CursorLocation = 2
datesDataDown.LockType = 1
datesDataDown.Open()
filename = Server.MapPath("admin/" & "schedule.csv")
dim fsDown, fDown
set fsDown=Server.CreateObject("Scripting.FileSystemObject")
set fDown=fsDown.CreateTextFile(filename,true)
strReturn = "schedule,reference,departure,return,nights,status, spaces,comments,othercomments,paxpaying,paxrate,de positdue,deopsitamt,depositpaid,balancedue,jetski, otheraccom,finalamountdue,finalpaymentdue,finalbal ancepaid,accomflightdetails"
fDown.WriteLine(strReturn)
While NOT datesDataDown.EOF
strReturn = datesDataDown.Fields.Item("schedule").Value & "," & datesDataDown.Fields.Item("reference").Value & "," & datesDataDown.Fields.Item("departure").Value & "," & datesDataDown.Fields.Item("return").Value & "," & datesDataDown.Fields.Item("nights").Value & "," & datesDataDown.Fields.Item("status").Value & "," & datesDataDown.Fields.Item("spaces").Value & "," & datesDataDown.Fields.Item("comments").Value & "," & datesDataDown.Fields.Item("othercomments").Value & "," & datesDataDown.Fields.Item("paxpaying").Value & "," & datesDataDown.Fields.Item("paxrate").Value & "," & datesDataDown.Fields.Item("depositdue").Value & "," & datesDataDown.Fields.Item("depositamt").Value & "," & datesDataDown.Fields.Item("depositpaid").Value & "," & datesDataDown.Fields.Item("balancedue").Value & "," & datesDataDown.Fields.Item("jetski").Value & "," & datesDataDown.Fields.Item("otheraccom").Value & "," & datesDataDown.Fields.Item("finalamountdue").Value & "," & datesDataDown.Fields.Item("finalpaymentdue").Value & "," & datesDataDown.Fields.Item("finalbalancepaid").Value & "," & datesDataDown.Fields.Item("accomflightdetails").Value
datesDataDown.MoveNext()
fDown.WriteLine(strReturn)
Wend
datesDataDown.Close
set datesDataDown = Nothing
fDown.Close
set fDown=nothing
set fsDown=nothing
end if
%>