Hi I am a newbie to ASP. I am trying to create a form to upload a file (e.g xls or CSV). this file will contain data to update an existing database on the server. I have no problem creating the upload page. But I am having difficulty in writing the ASP page to read the records from the file and update the DB records. Can anyone provide some code sample? Thanks!
I think you have hit on a difficult problem: there really needs to be more than one, possibly three different parts to this script. So you said you can upload just fine. Are you saving this file to the server? Do you have a choice of what file format you are going to upload (for example can you decide to always use .xls files?)? If this is the case it would simplify the code dramatically , probably the best would be to open two different recordsets (let's call "rsXls" the recordset from the uploaded file and "rsAccess" the recordset from the permanent database). Then the code would be something like this:
- rsAccess.open "SELECT * FROM table1", connAccess, adOpenDynamic, adLockOptimistic
-
'rsAccess needs special cursor, lock to update
-
rsXls.open "SELECT * FROM [$sheet1]", conXls 'default parameters OK
-
-
on error resume next
-
do until rsXls.eof
-
rsAccess.addnew
-
for each x in rsAccess.fields
-
if rsXls(x) <> "" then
-
rsAccess(x) = rsXls(x)
-
end if
-
next
-
rsAccess.update
-
rsXls.moveNext
-
loop
I think you'll need the 'on error' line, I can't remember how to determine if the field is an autonumber (since I generally don't try to update it), and that would definitely cause an error when you try to update.
The only question, then, is how are you going to set the "conn" objects. Those declarations will depend on which file formats you are going to use for the two databases. If the file that is going to be uploaded might have several different formats which you can not specify then the connXls declaration will need to be in some sort of select case statement.
Let me know if this helps.
Jared
1
