READING TEXT FILE HELP

[SeanB]

Hello all I need to take a simple text file which is line by line and place each value into a variable in ASP so I can run a update statement in SQL to update something. I know how to open the text file with file system object such as:
set fso = createobject("scripting.filesystemobject&quot
If fso.FileExists(filename) then
set fileobj = fspenTextFile(filename,8,True)
Else
Response.End
End if

Now each line would have up to a 30 character order in each line such as:
01MA34443454
0933423432MA

I need to place in my loop the order in a variable so I can run a update statement against it. Any idea? Thanks

 

[vasah20]

store the text file into a string, then split it up at the vbCrLf

eg:

dim mystrarr
mystrarr = split(txtStream,vbCrLf)

for i = 0 to ubound(mystrarr)
conn.execute "update mytbl where id = " & mystrarr(i)
next

something along these lines, at least

hth
leo

 

[SeanB]

Thanks for some reason the code I have looks like it does what I want because if I do a response.write it write out the update lines correct. But the update does not occur for some reason. My connection string looks right as well with no error:
''' Opening closedpo file for reading
set fso = createobject("scripting.filesystemobject&quot
If fso.FileExists(filename) then
set fileobj = fspenTextFile(filename,1)
Else
Response.End
End if


Do Until fileobj.AtEndOfStream
GetFileContent = Trim(fileobj.ReadLine) + vbCrLf
GetFileContent = Trim(GetFileContent)
cn.execute "UPDATE poheader SET archive = '1',archivedate = getdate() where ponum like '" & Trim(GetFileContent) & "'"
Loop

 

[vasah20]

Since you are using .ReadLine, it should cut off at each vbCrLf, so if you do something like

getfilecontent = Trim(fileobj.readline)

that should be fine. I think that your concatenation of vbCrLf is why the update isn't getting executed. If I remember right, Trim trims the whitespace, but will not remove the line breaks.

so modify it like this:
Do Until fileobj.AtEndOfStream
GetFileContent = Trim(fileobj.ReadLine)
cn.execute "UPDATE poheader SET archive = '1',archivedate = getdate() where ponum like '" & GetFileContent& "'"
Loop


and see if that works.
if it doesn't, try issuing a Response.write "UPDATE poheader SET archive = '1',archivedate = getdate() where ponum like '" & GetFileContent & "'"

within your loop to make sure that you are getting the correct values and what not.

hth
leo