Error when writing to file

[DrewConn]

Trying to write a simple data file out from a database table but keep getting 91 object variable or with block variable not set error. Any idea's ?


Imports System.Data.Odbc

Module MainModule

Public Sub Main()
Dim mystr As String

Dim dbconn As New OdbcConnection
Dim rs As OdbcDataReader
Dim cmd As New OdbcCommand
Dim i As Integer
Dim myfile = Nothing
Dim myfilestr As String
Dim fso = Nothing

On Error GoTo Err_Hdlr

myfilestr = "\\My Server\Extracts\CreditRevue_Daily\Document.txt"

mystr = "SELECT AppNo, Document, SentDate, DocSet, KeyValue FROM app_Document"



With dbconn
.ConnectionString = "DSN=XXXXXX ;UID=XXXXXX;PWD=XXXXXX;"
.ConnectionTimeout = 0
.Open()
End With

With cmd
.CommandTimeout = 0
.CommandType = CommandType.Text
.CommandText = mystr
.Connection = dbconn
End With

rs = cmd.ExecuteReader

If rs.HasRows = False Then

Else

fso = CreateObject("Scripting.filesystemobject")

myfile = fspenTextFile(myfilestr, 2, True)


myfile.Writeline("AppNbr|Document|SentDate|DocSet|KeyValue")
While rs.Read
mystr = ""
For i = 0 To rs.FieldCount - 1
If IsDBNull(rs.Item(i)) Then
mystr = mystr & "|"
Else
If i = rs.FieldCount - 1 Then
mystr = mystr & rs.Item(i)
Else
mystr = mystr & rs.Item(i) & "|"
End If
End If
Next
myfile.Writeline(mystr)
End While
myfile.Close()

End If

rs = Nothing
dbconn.Close()
dbconn = Nothing
myfile = Nothing
fso = Nothing
Exit Sub

Err_Hdlr:
MsgBox(Err.Number & " " & Err.Description)
Resume Next

End Sub

End Module

 

[RoyVidar]

It might be just my impression, but I thought this forum was more Classic VB/Database - I think perhaps posting in forum796 might be more appropriate.

BTW you might want to look into the StreamWriter class (System.IO)

Roy-Vidar