asked on

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mmitchell57

ASKER

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?

mmitchell57

ASKER

Also, another question. Is there a free editor that will show you line numbers? I can't find it in Visual Studio..

Guy Hengel [angelIII / a3]

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?

mmitchell57

ASKER

I'm using VS2005.

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

I appreciate the help thus far.

Guy Hengel [angelIII / a3]

>I'm using VS2005.
but still filesystemobject and ado? ... hmmmm....

mmitchell57

ASKER

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

mmitchell57

ASKER

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!

mmitchell57

ASKER

Fixed previous error by adding a space to the foreach statement. Now I get an error on the Char.. Say's type mis match.

Guy Hengel [angelIII / a3]

sorry, use ControlChars.Tab instead of char(8) in vb.net

mmitchell57

ASKER

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?

Guy Hengel [angelIII / a3]

>keep getting errors there.
what errors, please?

mmitchell57

ASKER

Sorry, the error is "invalid Procedure call or argument"

mmitchell57

ASKER

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

Guy Hengel [angelIII / a3]

what data types are there in the table?

objFile1.Write objField.Value & ""

Open in new window

mmitchell57

ASKER

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?

Guy Hengel [angelIII / a3]

the "binary" fields are eventually the problem... could you avoid those in the SELECT, to check out?

mmitchell57

ASKER

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.

mmitchell57

ASKER

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.

mmitchell57

ASKER

Angelll, you were right on. I remove the Binary data fields from the query and it all worked well. I appreciate you help. :)