DNS-less connection with erroneous/extra data causing error

[gdkz]

I am using ASP to connect to a MS Access 97 mdb using a DNS-less connection. I then query the mdb and concatenate the fields and add delimenters then write to a text file. When I read this from the record set and write to a text file, I am getting erroneous/extra data on the ends of certain fields that does not belong. This causes errors when I read the text file to write to another database. This is my code:

<%on error resume next
Set MyConn = Server.CreateObject(&quot;ADODB.Connection&quot
MdbFilePath = &quot;d:\web\applications_dev\test\test.mdb&quot;
MyConn.Open &quot;Driver={Microsoft Access Driver (*.mdb)}; DBQ=&quot; & MdbFilePath & &quot;;&quot;


SQL = &quot;SELECT TITLE.Name AS ProgramTitle, TITLE.ProgramNo AS ProgramNumber, TITLE.ProgDesc AS ProgramDescription, &quot; & _
&quot; TITLE.Duration AS Duration, TITLE.GradeLevel AS GradeLevel, SERIES.Name AS SeriesName, &quot; & _
&quot; SERIES.Description AS SeriesDescription, SERIES.Number AS Subject &quot; & _
&quot;FROM (SERIES INNER JOIN SERIES_ITEMS ON SERIES.ID = SERIES_ITEMS.SeriesID) &quot; & _
&quot; INNER JOIN TITLE ON SERIES_ITEMS.TitleID = TITLE.ID &quot;&_
&quot;ORDER BY SERIES.Name, TITLE.ProgramNo&quot;

Set RS = MyConn.Execute(SQL)
Const ForWriting = 2 ' Input OutPut mode
Const Create = True
Dim MyFile
Dim FSO ' FileSystemObject
Dim TSO ' TextStreamObject
dim strLine
' Use MapPath function to get the Physical Path of file

MyFile = &quot;d:\web\applications_dev\text_files\textfile.txt&quot;

Set FSO = Server.CreateObject(&quot;Scripting.FileSystemObject&quot
Set TSO = FSO.OpenTextFile(MyFile, ForWriting, Create)

WHILE NOT RS.EOF

strLine = &quot;&quot;
strLine = RS(&quot;ProgramTitle&quot & &quot;#&quot; & &quot;~&quot; & &quot;#&quot; & RS(&quot;ProgramNumber&quot & &quot;#&quot; & &quot;~&quot; & &quot;#&quot; & RS(&quot;ProgramDescription&quot & &quot;#&quot; & &quot;~&quot; & &quot;#&quot; & RS(&quot;Duration&quot & &quot;#&quot; & &quot;~&quot; & &quot;#&quot; & RS(&quot;GradeLevel&quot & &quot;#&quot; & &quot;~&quot; & &quot;#&quot; & RS(&quot;SeriesName&quot & &quot;#&quot; & &quot;~&quot; & &quot;#&quot; & RS(&quot;SeriesDescription&quot & &quot;#&quot; & &quot;~&quot; & &quot;#&quot; & RS(&quot;Subject&quot
TSO.Writeline strLine
if err.number >0 then
Response.Write err.Description
end if
RS.MoveNext
WEND
if err.number = 0 then
Response.Write &quot;<font color=navy>&quot; & &quot;Text File Written Successfully!&quot; & &quot;</font>&quot;
else
Response.Write &quot;<font color=maroon>&quot; & err.Description & &quot;Error in Writing Text File, Please try again!&quot; & &quot;</font>&quot;
end if
' close TextStreamObject and
' destroy local variables to relase memory
TSO.close
Set TSO = Nothing
Set FSO = Nothing
%>

Does anyone see a problem with this code. Any help you can give me with this problem is greatly appreciated. Thanks

Thanks in Advance!
Greg

 

[Mr3Putt]

one thing, which will not answer your actual question... just advice:
[ol]
change & &quot;#&quot; & &quot;~&quot; & &quot;#&quot; &

to &&quot;#~#&quot;&
[/ol]
Every ampersand slows down your code.

Other than that, the CODE looks reasonable... what sort of extraneous/erroneous data are you seeing? Is it generally appended to Text fields, Numeric fields, any particular field? Could they be Line-Feeds, Carriage-Returns, or Vertical-Tabs found in your database?

 

[gdkz]

One example on the SeriesDescription field:
This is an example of the record written to the text file.

Birds,Bees,Bugs (2)#~#43702#~#Shows natural forms that have served as a source of ideas for artists and designers shapes, color, surface decorations, texts, and movements.#~#1167#~#4-9#~#IMAGES AND THINGS#~#This series presents the enjoyment of art as a natural out-growth of the childs sense of wonder at his own surroundings. Using a documentary format,the programs are clustered about a variety of themes imagin words. game management, ecology, and wildlif#~#Art

The &quot; words. game management, ecology, and wildlif&quot; is from a record else where in the table, many records before. The fields are not fixed length. The field should stop at &quot;imagin&quot;, but the extra is added on. This does not happen to all records and fields, but the majority of the time to this field and to this exact record. The record prior to this one does not include &quot; words. game management, ecology, and wildlif&quot; and is correct.

When I run this query from the mdb, it is correct.

Thanks for the help.

Thanks in Advance!
Greg

 

[Mr3Putt]

Well, that's pretty darn confusing...

For starters, I'd try replacing the

TSO.writeline strLine

with a simple:

response.write(strLine & &quot;<BR><BR>&quot

...and compare your results. That should show whether you're getting bad data in your recordset or writing bad data to your output file.

note: you should probably comment out all the FSO stuff while testing this.

 

[gdkz]

I know it is a little convoluted. I have already done exactly what you said and commented out the fso and wrote it to the screen.
Writing to the screen shows the field up until the &quot;imagin&quot; and then starts a new record immediately after that running them together, thus a problem. It does this as if the other fields do not exist, but they do.
This example shows several run together with the same problem:

Kinds Of Houses (1)#~#43701#~#Shows dwellings old and new,adequate and inadequate; shares solutions to problems of providing functional shelter.#~#1170#~#4-9#~#IMAGES AND THINGS#~#This series presents the enjoyment of art as a natural out-growth of the childs sense of wonder at his own surroundings. Using a documentary format,the programs are clustered about a variety of themes imaginBirds,Bees,Bugs (2)#~#43702#~#Shows natural forms that have served as a source of ideas for artists and designers shapes, color, surface decorations, texts, and movements.#~#1167#~#4-9#~#IMAGES AND THINGS#~#This series presents the enjoyment of art as a natural out-growth of the childs sense of wonder at his own surroundings. Using a documentary format,the programs are clustered about a variety of themes imaginBuildings for Work and Play (3)#~#43703#~#Shows exciting and original architectural styles and solutions in office buildings,air terminals, schools, stadiums, etc.#~#1170#~#4-9#~#IMAGES AND THINGS#~#This series presents the enjoyment of art as

Confusing huh.

Thanks in Advance!
Greg

 

[onpnt]

did you try different delimiters from #~#. possibly just ##. It's possible the ~ is being treated this way

____________________________________________________
_{get the best answer to your questions by asking the best questions &quot;General FAQ&quot; faq333-2924}

http://www.onpntwebdesigns.com

 

[gdkz]

Thanks all, but still does not work. Tried several different delimiters and still nothing works.

Thanks in Advance!
Greg

 

[Mr3Putt]

Okay, new tack: Rather than appending all your data into a single line then writing that, try:
[ol]
while not rs.eof
for each fld in rs.Fields
response.write (fld & &quot;#~#&quot
next
resonse.write(&quot;<BR><BR>&quot
rs.movenext
wend
[/ol]
...that code is probably mal-formed, but you know what I'm talking about... try assembling it a piece at a time, and INCLUDE double line-break to see if your is corrupt, or merely ENDING early.

I know you've probably tried all this before, but you've got a weird problem here... Good luck.

 

[gdkz]

One thing that seems to work, is to use LEFT(fieldname,150) when I query form ASP. It seems to be cutting off at about 200 characters. Does anyone no if there is a length limit to a field in an ASP record set?

Thanks in Advance!
Greg

 

[Genimuse]

Assuming the database doesn't have a 150-character field (and therefore the data isn't really there), no, there's no problem. I pull multi-k memo fields all the time with no problem.

 

[gdkz]

Thanks all, everyone really helped out! The process is limiting me to 200 characters per field in the record set. When it goes over the limit, it gives me the bad data. But at least it will work for me. Thanks again!!!

Thanks in Advance!
Greg