Solved

Problem with Microsoft VBScript runtime error '800a0005'

Posted on 2014-01-15
10
1,740 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 19

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 55

Assisted Solution

by:Julian Hansen
Julian Hansen earned 250 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 19

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 52

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 250 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 55

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 52

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …
This tutorial will walk an individual through the process of installing of Data Protection Manager on a server running Windows Server 2012 R2, including the prerequisites. Microsoft .Net 3.5 is required. To install this feature, go to Server Manager…

830 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