[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-04
20
Medium Priority
?
296 Views
Last Modified: 2012-06-22
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

0
Comment
Question by:mmitchell57
  • 13
  • 7
20 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 22880110
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
 

Author Comment

by:mmitchell57
ID: 22880667
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 Comment

by:mmitchell57
ID: 22880689
Also, another question. Is there a free editor that will show you line numbers? I can't find it in Visual Studio..
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22880897
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 Comment

by:mmitchell57
ID: 22881892
I'm using VS2005.

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

I appreciate the help thus far.

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22881942
>I'm using VS2005.
but still filesystemobject and ado? ... hmmmm....
0
 

Author Comment

by:mmitchell57
ID: 22882233
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 Comment

by:mmitchell57
ID: 22885598
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 Comment

by:mmitchell57
ID: 22885798
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22885832
sorry, use ControlChars.Tab instead of char(8) in vb.net
0
 

Author Comment

by:mmitchell57
ID: 22886081
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22886169
>keep getting errors there.
what errors, please?
0
 

Author Comment

by:mmitchell57
ID: 22887175
Sorry, the error is "invalid Procedure call or argument"
0
 

Author Comment

by:mmitchell57
ID: 22887566
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22889214
what data types are there in the table?

objFile1.Write objField.Value & ""

Open in new window

0
 

Author Comment

by:mmitchell57
ID: 22889531
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22889696
the "binary" fields are eventually the problem... could you avoid those in the SELECT, to check out?
0
 

Author Comment

by:mmitchell57
ID: 22889781
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 Closing Comment

by:mmitchell57
ID: 31513260
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 Comment

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month17 days, 18 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question