Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1908
  • Last Modified:

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:

<%

  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
webdesigner_dk
Asked:
webdesigner_dk
  • 4
  • 2
  • 2
  • +1
3 Solutions
 
strivoliCommented:
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
 
webdesigner_dkAuthor Commented:
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
 
Julian HansenCommented:
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
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
webdesigner_dkAuthor Commented:
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
 
strivoliCommented:
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
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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
 
Julian HansenCommented:
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
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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
 
webdesigner_dkAuthor Commented:
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
 
webdesigner_dkAuthor Commented:
My comment presents the final step needed to solve the problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows PowershellĀ® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now