[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Problem with Microsoft VBScript runtime error '800a0005'

Posted on 2014-01-15
10
Medium Priority
?
1,859 Views
Last Modified: 2014-01-20
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:

<%

  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"
  
%>

Open in new window


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)

Open in new window


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.
0
Comment
Question by:webdesigner_dk
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 20

Expert Comment

by:strivoli
ID: 39781847
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.
0
 

Author Comment

by:webdesigner_dk
ID: 39781903
Thanks for the quick answer. I do not receive any Insert Command Denied errors, so that shouldn't be the problem, though... any thoughts?
0
 
LVL 60

Assisted Solution

by:Julian Hansen
Julian Hansen earned 750 total points
ID: 39781951
This is probably because the sql parameter is empty

Try replacing the db.execute(sql) line with
Response.write "SQL: [" & sql & "]"
Response.End

Open in new window

0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

Author Comment

by:webdesigner_dk
ID: 39782091
Hey!

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"
  
%>

Open in new window


I do get the same error though...

Am I missing something?
0
 
LVL 20

Expert Comment

by:strivoli
ID: 39782105
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.
0
 
LVL 54

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 750 total points
ID: 39782341
Are you sure there is data?

db_open db
'***** insert test code *******
response.write "The SQL = "&sql
response.end
  sql = "insert into import_forhandlere (ForhandlerID, Byen, Forhandler, PostNr, Email) values " & left(sql, len(sql)-1)
  Response.write "SQL: [" & sql & "]"
  Response.End

Open in new window

0
 
LVL 60

Expert Comment

by:Julian Hansen
ID: 39783378
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.
0
 
LVL 54

Expert Comment

by:Scott Fell, EE MVE
ID: 39783623
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.
0
 

Accepted Solution

by:
webdesigner_dk earned 0 total points
ID: 39784830
Hi!

Turns out that the problem was on the csv file I tried to import. The problem was that line separator was "0D" instead of "0D0A" sequence.

Thanks for all the answers.
0
 

Author Closing Comment

by:webdesigner_dk
ID: 39793783
My comment presents the final step needed to solve the problem.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this Micro Tutorial viewers will learn how to restore their server from Bare Metal Backup image created with Windows Server Backup feature. As an example Windows 2012R2 is used.
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question