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

[roshaan]

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...

'upload_excel.asp               
<form action="upload_excel_process.asp" method="post" enctype="multipart/form-data" name="frmMain" onSubmit="return checkData();">
<input name="file1" type="file"> <input type="submit" name="Submit" value="Submit">
<%mem_id=session("mem_id")%>
<input type="hidden" name="client_id"  value="<%=mem_id%>">
</form>

process file

'upload_excel_process.asp
		 
<%client_id=session("mem_id")%>
<%
Dim xlApp,xlBook,xlSheet1,xlSheet2,OpenFile,i  
Dim Conn,strSQL,client_id,objExec  
Dim mySmartUpload  
Dim sFileName	

'*** Create Object ***'	
Set mySmartUpload = Server.CreateObject("aspSmartUpload.SmartUpload")

'*** Upload Files ***'
mySmartUpload.Upload

	'** Getfile Name ***'
sFileName = mySmartUpload.Files("file1").FileName

If sFileName <> "" Then

mySmartUpload.Files("file1").SaveAs(Server.MapPath("excel/"&sFileName))

'   my ref..\excel\" & session("mem_id") & "\" & now()

OpenFile = "excel/"&sFileName

'*** Create Exce.Application ***'
Set xlApp = Server.CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(Server.MapPath(OpenFile))
Set xlSheet1 = xlBook.Worksheets(1)		

Set Conn = Server.Createobject("ADODB.Connection")
Conn.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("../db/database.mdb"),"" , ""
For  i = 2 To 5  
If Trim(xlSheet1.Cells.Item(i,1)) <> "" Then  
strSQL = ""  
strSQL = strSQL &"INSERT INTO add_contacts "  
strSQL = strSQL &"(client_id,name_receiver,contact_person_receiver,street_receiver,city_receiver,tel_receiver,fax_receiver,country_receiver,zip_code_receiver) "  

' i have added fields to the db for file (file_name and file_id) 

strSQL = strSQL &"VALUES "  
strSQL = strSQL &"('"&client_id&"', '"&xlSheet1.Cells.Item(i,1)&"','"&xlSheet1.Cells.Item(i,2)&"','"&xlSheet1.Cells.Item(i,3)&"'"  
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)&"')"

Set objExec = Conn.Execute(strSQL)  
Set objExec = Nothing  
End IF  
Next  



xlApp.Application.Quit

'*** Quit and Clear Object ***'
Conn.Close()
Set Conn = Nothing
Set xlSheet1 = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End If

Set mySmartUpload = Nothing
%>

[C CSR]

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.M apPath(...)). 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.xl sx" to "MyFile2.xl sx", 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?

[roshaan]

thanks for your reply
it is very helpfull for me.
i just want to save the filename as a reference for future reference.

[C CSR]

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.