By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

save file in db with unique name or id with asp classic

P: 8
I m new to asp my code is working absolutely fine, but i am facing two problems although these problems are not affecting the application. I just want to make good functionality to the application logically.

1) when i upload excel file, and if another file is already present with the same name, then new file overwrites it..
and if i manually change file name then file is saved with new name..
i am trying to save this new file with unique name or new name,, i donít know how to do this pls help..

2) and second problem is that i want to save this file in db with the corresponding login user (for future reference).. i hope
my problems will be solved. many thanks

this is the url from which I got help..
shotdev.com/asp/asp-excel/asp-upload-excel-import-to-database/

code is pasted for review...


Expand|Select|Wrap|Line Numbers
  1. 'upload_excel.asp               
  2. <form action="upload_excel_process.asp" method="post" enctype="multipart/form-data" name="frmMain" onSubmit="return checkData();">
  3. <input name="file1" type="file"> <input type="submit" name="Submit" value="Submit">
  4. <%mem_id=session("mem_id")%>
  5. <input type="hidden" name="client_id"  value="<%=mem_id%>">
  6. </form>
  7.  
process file

Expand|Select|Wrap|Line Numbers
  1. 'upload_excel_process.asp
  2.  
  3. <%client_id=session("mem_id")%>
  4. <%
  5. Dim xlApp,xlBook,xlSheet1,xlSheet2,OpenFile,i  
  6. Dim Conn,strSQL,client_id,objExec  
  7. Dim mySmartUpload  
  8. Dim sFileName    
  9.  
  10. '*** Create Object ***'    
  11. Set mySmartUpload = Server.CreateObject("aspSmartUpload.SmartUpload")
  12.  
  13. '*** Upload Files ***'
  14. mySmartUpload.Upload
  15.  
  16.     '** Getfile Name ***'
  17. sFileName = mySmartUpload.Files("file1").FileName
  18.  
  19. If sFileName <> "" Then
  20.  
  21. mySmartUpload.Files("file1").SaveAs(Server.MapPath("excel/"&sFileName))
  22.  
  23. '   my ref..\excel\" & session("mem_id") & "\" & now()
  24.  
  25. OpenFile = "excel/"&sFileName
  26.  
  27. '*** Create Exce.Application ***'
  28. Set xlApp = Server.CreateObject("Excel.Application")
  29. Set xlBook = xlApp.Workbooks.Open(Server.MapPath(OpenFile))
  30. Set xlSheet1 = xlBook.Worksheets(1)        
  31.  
  32. Set Conn = Server.Createobject("ADODB.Connection")
  33. Conn.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("../db/database.mdb"),"" , ""
  34. For  i = 2 To 5  
  35. If Trim(xlSheet1.Cells.Item(i,1)) <> "" Then  
  36. strSQL = ""  
  37. strSQL = strSQL &"INSERT INTO add_contacts "  
  38. strSQL = strSQL &"(client_id,name_receiver,contact_person_receiver,street_receiver,city_receiver,tel_receiver,fax_receiver,country_receiver,zip_code_receiver) "  
  39.  
  40. ' i have added fields to the db for file (file_name and file_id) 
  41.  
  42. strSQL = strSQL &"VALUES "  
  43. strSQL = strSQL &"('"&client_id&"', '"&xlSheet1.Cells.Item(i,1)&"','"&xlSheet1.Cells.Item(i,2)&"','"&xlSheet1.Cells.Item(i,3)&"'"  
  44. strSQL = strSQL &",'"&xlSheet1.Cells.Item(i,4)&"','"&xlSheet1.Cells.Item(i,5)&"','"&xlSheet1.Cells.Item(i,6)&"','"&xlSheet1.Cells.Item(i,7)&"','"&xlSheet1.Cells.Item(i,8)&"')"
  45.  
  46. Set objExec = Conn.Execute(strSQL)  
  47. Set objExec = Nothing  
  48. End IF  
  49. Next  
  50.  
  51.  
  52.  
  53. xlApp.Application.Quit
  54.  
  55. '*** Quit and Clear Object ***'
  56. Conn.Close()
  57. Set Conn = Nothing
  58. Set xlSheet1 = Nothing
  59. Set xlBook = Nothing
  60. Set xlApp = Nothing
  61. End If
  62.  
  63. Set mySmartUpload = Nothing
  64. %>
  65.  
Jan 16 '12 #1
Share this Question
Share on Google+
3 Replies


100+
P: 144
I believe the original filename is stored in your var sFileName, and I believe its id is "File1", so you'd have to rename it.
See if you can do this in your SaveAs(Server.MapPath(...)). Instead of "excel1/" & sFilename, use "excel1/" & "newname" & "." & "[whatever you want the extension to be]".

I believe if you used some concatenation and incrementation with something like a trailing number for each time you upload, the filename would be changed (e.g.; from "MyFile1.xlsx" to "MyFile2.xlsx", etc., etc.).

Does that make sense? There's alot of things you could do like that.

I can't test it right now, but let me know if it'll work.

As far as saving it in the database, do you mean the content or just the filename as a reference?
Jan 17 '12 #2

P: 8
thanks for your reply
it is very helpfull for me.
i just want to save the filename as a reference for future reference.
Jan 18 '12 #3

100+
P: 144
While your saving the upload to a folder also stick in a few more lines of code beneath there to open your table of preference and stick the name you just used into a field somewhere in a database table. Then in the future, before you upload the file, you could reference the oldname out of the table, manipulate the name some way as I descibed before and save the file to folder and filename back to the table, probably in a new record if you want to maintain a list of these files for future reference. If you aren't familiar with doing something I've mentioned here, reply and I'll walk you through some basic process for getting this done. Just have a good idea ready for renaming the file, where in the database you want to store it (tablename, fieldname, etc.) and we can go from there. Good luck.
Jan 18 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.