[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Problem with Microsoft VBScript runtime error '800a0005'

Posted on 2014-01-15
10
Medium Priority
?
1,833 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 59

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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 59

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The following article is comprised of the pearls we have garnered deploying virtualization solutions since Virtual Server 2005 and subsequent 2008 RTM+ Hyper-V in standalone and clustered environments.
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 they can get their files copied out from their unbootable system without need to use recovery services. As an example non-bootable Windows 2012R2 installation is used which has boot problems.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

650 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