asked on
Problem with Microsoft VBScript runtime error '800a0005'
Hi,
I have moved an ASP based website from one webhosting server to a hosted test environment. Everything in the website seems to work fine, except for one function that uploads a csv file to the server and adds the values to a mysql database. The code of that page is:
I get this error in my browser when I try to upload a file in the new server:
Microsoft VBScript runtime error '800a0005'
Invalid procedure call or argument: 'left'
/admin/upload.asp, line 49
On line 49 there's this code:
The file does get uploaded on the server, though the values do not get updated in the database. This however works on the other server.
Any ideas of what the problem can be?
Thanks.
I have moved an ASP based website from one webhosting server to a hosted test environment. Everything in the website seems to work fine, except for one function that uploads a csv file to the server and adds the values to a mysql database. The code of that page is:
<%
if session("adm") <> "OK" then
response.redirect "login.asp"
response.end
end if
filename = "/admin/uploads/import.csv"
set myUpload = Server.CreateObject("aspSmartUpload.SmartUpload")
myUpload.Upload
' i = myUpload.SaveAs("/admin/uploads/import.csv")
For each file In myUpload.Files
If not file.IsMissing Then
file.SaveAs("/admin/uploads/import.csv")
end if
next
' abuisness end
' local server start
' set Upload = Server.CreateObject("Persits.Upload")
' count = Upload.Save
' for each File in Upload.Files
' File.SaveAs server.mappath(filename)
' next
' set upload = nothing
' local server end
set fso = CreateObject("Scripting.FileSystemObject")
set myFile = fso.OpenTextFile(server.mappath(filename), 1, True)
i=0
do while myFile.AtEndOfStream <> True
curLine = replace(MyFile.ReadLine,"'","''")
if i > 0 then
sql = sql & "('" & replace(curLine,";","','") & "'),"
end if
i=i+1
loop
myFile.close
set myFile = nothing
set fso = nothing
db_open db
sql = "insert into import_forhandlere (ForhandlerID, Byen, Forhandler, PostNr, Email) values " & left(sql, len(sql)-1)
db.execute(sql)
' delete dealers not in file
sql = "delete from forhandlere where forhandlerid not in (select forhandlerid from import_forhandlere)"
db.execute(sql)
' update existing dealers
sql = "update forhandlere as f left join import_forhandlere as i on f.forhandlerid = i.forhandlerid set f.byen = i.byen, f.forhandler = i.forhandler, f.postnr = i.postnr, f.email = i.email"
db.execute(sql)
' insert new dealers
sql = "insert into forhandlere (forhandlerid, byen, forhandler, postnr, email) (SELECT forhandlerid, byen, forhandler, postnr, email FROM import_forhandlere WHERE forhandlerid NOT IN (SELECT forhandlerid FROM forhandlere))"
db.execute(sql)
' delete all entries in import_table (prepare for next import)
sql = "delete from import_forhandlere"
db.execute(sql)
db_close db
response.redirect "index.asp?upload=1"
%>
I get this error in my browser when I try to upload a file in the new server:
Microsoft VBScript runtime error '800a0005'
Invalid procedure call or argument: 'left'
/admin/upload.asp, line 49
On line 49 there's this code:
sql = "insert into import_forhandlere (ForhandlerID, Byen, Forhandler, PostNr, Email) values " & left(sql, len(sql)-1)
The file does get uploaded on the server, though the values do not get updated in the database. This however works on the other server.
Any ideas of what the problem can be?
Thanks.
ASPWindows Server 2012MySQL Server
Last Comment
webdesigner_dk
You might be denied. Can you check MYSQL's server logs in order to understand if your code is trying to run unauthorized commands such as INSERT.
ASKER
Thanks for the quick answer. I do not receive any Insert Command Denied errors, so that shouldn't be the problem, though... any thoughts?
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Hey!
Thanks for the answer. I have tried changing the code like this:
:
I do get the same error though...
Am I missing something?
Thanks for the answer. I have tried changing the code like this:
:
<%
if session("adm") <> "OK" then
response.redirect "login.asp"
response.end
end if
filename = "/admin/uploads/import.csv"
set myUpload = Server.CreateObject("aspSmartUpload.SmartUpload")
myUpload.Upload
' i = myUpload.SaveAs("/admin/uploads/import.csv")
For each file In myUpload.Files
If not file.IsMissing Then
file.SaveAs("/admin/uploads/import.csv")
end if
next
' local server start
' set Upload = Server.CreateObject("Persits.Upload")
' count = Upload.Save
' for each File in Upload.Files
' File.SaveAs server.mappath(filename)
' next
' set upload = nothing
' local server end
set fso = CreateObject("Scripting.FileSystemObject")
set myFile = fso.OpenTextFile(server.mappath(filename), 1, True)
i=0
do while myFile.AtEndOfStream <> True
curLine = replace(MyFile.ReadLine,"'","''")
if i > 0 then
sql = sql & "('" & replace(curLine,";","','") & "'),"
end if
i=i+1
loop
myFile.close
set myFile = nothing
set fso = nothing
db_open db
sql = "insert into import_forhandlere (ForhandlerID, Byen, Forhandler, PostNr, Email) values " & left(sql, len(sql)-1)
Response.write "SQL: [" & sql & "]"
Response.End
' delete dealers not in file
sql = "delete from forhandlere where forhandlerid not in (select forhandlerid from import_forhandlere)"
Response.write "SQL: [" & sql & "]"
Response.End
' update existing dealers
sql = "update forhandlere as f left join import_forhandlere as i on f.forhandlerid = i.forhandlerid set f.byen = i.byen, f.forhandler = i.forhandler, f.postnr = i.postnr, f.email = i.email"
Response.write "SQL: [" & sql & "]"
Response.End
' insert new dealers
sql = "insert into forhandlere (forhandlerid, byen, forhandler, postnr, email) (SELECT forhandlerid, byen, forhandler, postnr, email FROM import_forhandlere WHERE forhandlerid NOT IN (SELECT forhandlerid FROM forhandlere))"
Response.write "SQL: [" & sql & "]"
Response.End
' delete all entries in import_table (prepare for next import)
sql = "delete from import_forhandlere"
Response.write "SQL: [" & sql & "]"
Response.End
db_close db
response.redirect "index.asp?upload=1"
%>
I do get the same error though...
Am I missing something?
Your script might not return any specific "Insert Command Denied errors" but just a generic error because it isn't aware of the reason.
You should check server's log. They could tell you much more about the reason.
You should check server's log. They could tell you much more about the reason.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
If you are getting the same error then this has nothing to do with the db.
I suspect your sql string is empty and that is why the left() function is failing - which is what the error message is telling you.
left() will fail with this error if the string passed to it is empty or null.
I suspect your sql string is empty and that is why the left() function is failing - which is what the error message is telling you.
left() will fail with this error if the string passed to it is empty or null.
Yes, I agree, that is what I was getting at and I would like to see what the variable, 'sql' looks like which is why I posted that little snippet.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
My comment presents the final step needed to solve the problem.