I am querying an SQL database to gather some information. I dump the results of that query into a recordset. I then attempt to write the record set to a file. The result of the below code only writes one value to the file. It appears the array hold all the data for this one record rather then a 2d array that has this record + fields, then the next + fields and so on.

Last time I ran this I got a field count of 86 and a record count of -1.
When I run this on the server via SQL Query Analyser I get 400+ records with 86 fields per record.

I want the output to look like

record1, field1, field2, field3, ....
record2, field1, field2, field3,.....
..
..
..
and so on.

Keep in mind, i'm a newb with a lot of this. :)
'------ Constants ------
Const ForAppending = 8
Const ForWriting = 2

'------ Variables -----
Dim objFso      'file system object
Dim StrDSN      'server connection specifications
Dim strFile1    'object file location
Dim objFile1    'objec file
Dim conn        'connection string
dim cnt         '
dim strSelect   'select statement
dim strInfo     '
dim rs          'Contains quered info
dim rsQuery
dim dtrInfo     '

'------ Initializing Variables ------ 19

strFile1 = "c:\temp\text.txt"

'!!!!!!------ Coding -------!!!!!!

'------ Connection Object ------
'set conn.Provider = "sqloledb"
conn.ConnectionString = "Driver={SQL Server};Server=ServerName;UID=test;PWD=test"
conn.Open
conn.DefaultDatabase = "DatabaseDB"

set rsQuery = conn.Execute("Select * From Table")

'------ File Object ------ 32
Set objFso = CreateObject("Scripting.FileSystemObject")
set objFile1 = objFSO.CreateTextFile(strFile1, ForWriting, false)

cnt = rsQuery.Fields.Count
cntr = rsquery.RecordCount
msgbox "The count is " & cnt
msgbox "the record count it " & cntr
for i = 0 to cnt - 1
objFile1.writeline rsQuery(i)
'rsQuery.NextRecordset
next
rsquery.Close

objFile1.close()

conn.Close

msgbox "done"

###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Billing EngineerCommented:
you are close:
'------ File Object ------ 32
Set objFso = CreateObject("Scripting.FileSystemObject")
set objFile1 = objFSO.CreateTextFile(strFile1, ForWriting, false)

Dim lngRecord
lngRecord = 1
while not rsQuery.eof
objFile.Write "Record#" & lngrecord & char(8)
foreach objfield in rsQuery.Fields
if not isnull(objField.Value) then
objFile.Write objField.Value
end if
objFile.Write char(8)
next
objFile.WriteLine
rsQuery.movenext
wend
rsquery.Close

objFile1.close()

conn.Close

msgbox "done"

0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
The only part that doesn't make sense to me is the following.

" objFile.Write "Record#" & lngrecord & char(8)  "

What is a char(8)? is that a return or tab?
0
Author Commented:
Also, another question. Is there a free editor that will show you line numbers? I can't find it in Visual Studio..
0
Billing EngineerCommented:
char(8) should be the tab... it might be char(9) though ....

>Also, another question. Is there a free editor that will show you line numbers? I can't find it in Visual Studio..
there are quite some... which version of VS are you speaking about?
0
Author Commented:
I'm using VS2005.

I"ll give the code a run through tomorrow to verify funcationality, hopefully.

I appreciate the help thus far.

0
Billing EngineerCommented:
>I'm using VS2005.
but still filesystemobject and ado? ... hmmmm....
0
Author Commented:
I'm doing everything via vbscripting.  It makes it easier on the user and less requirements on the user end. I'm still new to all this, so I could be on a very misguided path. I'm willing to take suggestions.  I'm working with what tools I know and have been learning slowly. :)
0
Author Commented:
I did a direct copy and paste into the code, commented out the old code in that section.

I get an error that states "Expecte end of statement" on the line that says "foreach objfield in rsQuery.Fields"

I appreciate the help so far!
0
Author Commented:
Fixed previous error by adding a space to the foreach statement. Now I get an error on the Char.. Say's type mis match.
0
Billing EngineerCommented:
sorry, use ControlChars.Tab instead of char(8) in vb.net
0
Author Commented:
Angel,

Everything seems to work great except for the "objFile1.Write objField.Value" portion. I keep getting errors there. If I comment it out, I get a file w/ the 86 tabs and record numbers. That's what I'm wanting.  So, is .value a true property of objField?  What would I search for to see the valid fields that objFeild could have?
0
Billing EngineerCommented:
>keep getting errors there.
0
Author Commented:
Sorry, the error is "invalid Procedure call or argument"
0
Author Commented:
If it helps any, here's the code I have now. I replaced the tabs with ",".
'------ Constants ------
Const ForAppending = 8
Const ForWriting = 2

'------ Variables -----
Dim objFso      'file system object
Dim StrDSN      'server connection specifications
Dim strFile1    'object file location
Dim objFile1    'objec file
Dim conn        'connection string
dim cnt         '
dim strSelect   'select statement
dim strInfo     '
dim rs          'Contains quered info
dim rsQuery
dim dtrInfo     '

'------ Initializing Variables ------ 19
strSelect = "Select * From Personnel WHERE uiName LIKE '%mi%' ORDER BY uiName"
strFile1 = "c:\temp\text.txt"

'!!!!!!------ Coding -------!!!!!!

'------ Connection Object ------ 25
conn.ConnectionString = "Driver={SQL Server};Server=server.com;UID=test;PWD=test"
conn.Open
conn.DefaultDatabase = "databaseDB"

set rsQuery = conn.Execute("Select * From table")

'------ File Object ------ 35
Set objFso = CreateObject("Scripting.FileSystemObject")
set objFile1 = objFSO.CreateTextFile(strFile1, ForWriting, false)

Dim lngRecord
lngRecord = 1
while not rsQuery.eof
objFile1.Write "Record#" & lngrecord & ","
for each objField in rsQuery.Fields
if not isnull(objField.Value) then
objFile1.Write objField.Value
end if
objFile1.Write ","
next
objFile1.WriteLine
rsQuery.movenext
lngRecord = lngRecord + 1
wend
rsquery.Close

objFile1.close()

conn.Close

msgbox "done"

0
Billing EngineerCommented:
what data types are there in the table?

objFile1.Write objField.Value & ""

0
Author Commented:
It looks like a mix of integers, VarChar, Smallint, datetime, bit, binary, and reals.

Is this methond only friendly to strings? Is there a method that can consume and write anything?
0
Billing EngineerCommented:
the "binary" fields are eventually the problem... could you avoid those in the SELECT, to check out?
0
Author Commented:
I'm sure I could, I'm running this on a test database.  I'll take a look at the live database (different data) and see if it has any require binary data they need. From what I remember of the request, it shouldn't.
0
Author Commented:
After so very minor modification this addition to my scripting helped get the results I was looking for. I appreciate the consistent follow up to questions and politeness of Angelll. I appreciate all the help.
0
Author Commented:
Angelll, you were right on. I remove the Binary data fields from the query and it all worked well. I appreciate you help. :)
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.