webbased text file driven database update using asp

[Guest_imported]

Hi i was wondering how you would go about updating a database over http using a commma delimeted text file and asp..
I tried a couple of things using the <input type=file
input, and the filesystemobject for asp.. But i am unsure, if this is possible, and if it is, how to go about coding it.. And how you get the file from the input tag, to the filesystem object..
EG is it just a simple file = request.form(&quot;file&quot or is there the need to somehow upload the file to the server then read it...??

I need this so that other staff who don't have the technical knowledge to ftp the server and update, can still update the database, as it needs to be done daily..

Any help would be great
Brad

 

[Guest_imported]

Here's some code, that adds records to the database, reading it from the comma-delimited text file, stored on a server:
1. Script
<%
dim fso,file,i,strPath,arFields,oRS,oConn,strSQL,strConn,strRecord
strPath = server.MapPath(&quot;.&quot & &quot;\test.txt&quot;
set fso = server.CreateObject(&quot;Scripting.FileSystemObject&quot
set file = fspenTextFile(strPath,1,0)
set oConn = server.CreateObject(&quot;ADODB.Connection&quot
strConn = &quot;driver={SQL Server};server=development;uid=sa;pwd=;database=pubs;&quot;
oConn.Open strConn
set oRS = server.CreateObject(&quot;ADODB.Recordset&quot
strSQL = &quot;Select * from table4&quot;
oRS.Open strSQL,oConn,adOpenKeyset,adLockOptimistic,adCmdText
'redim arFields(0)
do while not file.atendofstream = true
strRecord = file.readLine
arFields = split(strRecord,&quot;,&quot
oRS.AddNew
for i = 0 to oRS.Fields.Count - 1
oRS(i)= arFields(i)
Next
oRS.Update
arFields = &quot;&quot;
loop
%>

2. Text file test.txt
5,d:\Pioneer2\CntrItem.h,1855,11/12/97
6,d:\Pioneer3\CntrItem.hh,1899,11/12/97
7,d:\Pioneer4\CntrItem.hg,1855,11/12/98

There are some things you need to take into consideration:

1. Split() function returns a string and, of course, data in test.txt is of the string datatype also. So, you might be needing to do it differently if you have numeric fields in a table:

oRS(0)= cint(arFields(0))
oRS(1)= cint(arFields(1)) and so on converting as needed

2. Digits at the beginning of every line of the text file --
are primary keys or records' ids, but if you have an identity field, you don't need to include any value for this field in a text file, so it would be :

d:\Pioneer2\CntrItem.h,1855,11/12/97
d:\Pioneer3\CntrItem.hh,1899,11/12/97
d:\Pioneer4\CntrItem.hg,1855,11/12/98

And, of course layout of the text file must be the same as in the table, meaning fields order and number. Otherwise, if a particular field doesn't accept NULL or doesn't have a default value, error will be generated by SQL driver.

Hope this helps.