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?
 
Guy Hengel [angelIII / a3]Connect With a Mentor 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
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
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.

All Courses

From novice to tech pro — start learning today.