# Writing an entire recordset to a .txt file - vbs file help

Hi

I am attempting to write a vbs file to ADO connect to a database, return the results of a query and write out the recordset to a text file.

I subsequently want to print the text file to generic (text only) printers with no formatting.

I have written some (probably messy) code to connect and populate a recordset. I have tested this and I can echo out fields from the first record.

How do I now get this data out of an array in memory into a text file?

do until eof / rs.movefirst / movenext do not work (I believe due to the oConn parameters).

I believe that oRec.Fields(0) oRec.Fields(1) may help but I cant work out the exact syntax.

Any ideal people??

Option Explicit

Dim sConn
Dim oConn, oRec

Dim sQuote, sSQL

sConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\SERVER\database.mdb;"

sQuote = Chr(39)
sSQL = "SELECT * FROM FIRELIST"

oConn.Open sConn
oRec.Open sSQL, oConn, 0, 1, 1

Wscript.echo oRec("EmploeeName")

oRec.Close
oConn.Close


###### Who is Participating?

Senior Network Systems SpecialistCommented:
Use the following lines e.g.

Set fs = CreateObject("Scripting.FileSystemObject")

Set obj=fs.OpenTextFile("c:\employees.txt")
obj.Writeline (oRec("EmploeeName")
obj.Close
0

Author Commented:
Good start!!!!

Set obj=fs.OpenTextFile("c:\employees.txt", 8)

Got it working with a minor tweak.

Now, I have the employee name of the first record in the recordset landing in my text file.

How do I get it to dump out the ENTIRE ARRAY (rs) into my text file??

Many thanks
Steven
0

Senior Network Systems SpecialistCommented:
~Something like this

while not orec.EOF
for field = 0 to orec.Fields.Count
obj.writeline(orec.Fields.Item(field))
next field
orec.NextRecord
wend
0

Author Commented:
right, got this far....

Option Explicit

Dim sConn
Dim oConn, oRec, fs, obj

Dim sQuote, sSQL

sConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\SERVER\Databases\Firelist.mdb;"

sQuote = Chr(39)
sSQL = "SELECT * FROM FIRELIST"

oConn.Open sConn
oRec.Open sSQL, oConn, 0, 1, 1

Set fs = CreateObject("Scripting.FileSystemObject")

Set obj=fs.OpenTextFile("C:\temp\Firelist\firelist.txt", 2)

while not orec.EOF

'for field = 0 to orec.Fields.Count
obj.write(orec.Fields.Item("Muster"))
obj.write("          ")
obj.write(orec.Fields.Item("ClockedOn"))
obj.write("          ")
obj.write(orec.Fields.Item("Present"))
obj.write("          ")
obj.write(orec.Fields.Item("Name"))
obj.write("          ")
obj.write(orec.Fields.Item("Arrived"))
obj.write("          ")
obj.write(orec.Fields.Item("Left"))
'next' field
orec.NextRecord

wend

obj.Close

oRec.Close
oConn.Close


If I comment out the WHILE statement it writes out the first (Current) line. oRec.nextrecord method doesn't work (nor does movefirst, movelast).

I believe the type of connection

oRec.Open sSQL, oConn, 0, 1, 1
establishes whether I can navigate through the recordset.

In the back of my mind I though there was a way of calling the record number from the array oRec(0), oRec(1) or similar.
0

Author Commented:
Forget that.
I was thinking of Wscript.Arguments(0) Wscript.Arguments(1) where you can specify which argument to use.

How do I get the script to loop / write out each record in the recordset??
0

Older than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.