VBScript, Record Sets, Writeline... Please help. :)

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 ForReading = 1
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 = CreateObject("ADODB.Connection")
'set conn.Provider = "sqloledb"
    conn.ConnectionString = "Driver={SQL Server};Server=ServerName;UID=test;PWD=test"
    conn.Open
    conn.DefaultDatabase = "DatabaseDB"
    
set rsQuery = CreateObject("ADODB.Recordset")
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"

Open in new window

mmitchell57Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Guy Hengel [angelIII / a3]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"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
mmitchell57Author 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
mmitchell57Author Commented:
Also, another question. Is there a free editor that will show you line numbers? I can't find it in Visual Studio..
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Guy Hengel [angelIII / a3]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
mmitchell57Author 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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I'm using VS2005.
but still filesystemobject and ado? ... hmmmm....
0
mmitchell57Author 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
mmitchell57Author 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
mmitchell57Author 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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, use ControlChars.Tab instead of char(8) in vb.net
0
mmitchell57Author 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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>keep getting errors there.
what errors, please?
0
mmitchell57Author Commented:
Sorry, the error is "invalid Procedure call or argument"
0
mmitchell57Author Commented:
If it helps any, here's the code I have now. I replaced the tabs with ",".
'------ Constants ------
Const ForReading = 1
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
set conn = CreateObject("ADODB.Connection")
    conn.ConnectionString = "Driver={SQL Server};Server=server.com;UID=test;PWD=test"
    conn.Open
    conn.DefaultDatabase = "databaseDB"
    
set rsQuery = CreateObject("ADODB.Recordset")
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"

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what data types are there in the table?

objFile1.Write objField.Value & ""

Open in new window

0
mmitchell57Author 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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the "binary" fields are eventually the problem... could you avoid those in the SELECT, to check out?
0
mmitchell57Author 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
mmitchell57Author 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
mmitchell57Author 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.