Solved

Posted on 2008-11-04
291 Views
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"

0
Question by:mmitchell57

LVL 142

Accepted Solution

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

Author Comment

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

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

LVL 142

Expert Comment

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

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 142

Expert Comment

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

Author Comment

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

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

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 142

Expert Comment

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

Author Comment

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 142

Expert Comment

>keep getting errors there.
0

Author Comment

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

Author Comment

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

LVL 142

Expert Comment

what data types are there in the table?

objFile1.Write objField.Value & ""

0

Author Comment

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 142

Expert Comment

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

Author Comment

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

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

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

### Suggested Solutions

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

#### Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!