Error writing data 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

 

[johnwm]

You have already been redirected to the correct forum (forum796).

Please RedFlag this thread for removal when you have read this post

You might also check out faq222-2244 for forum usage guidelines

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints