Error 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

 

[PsychoCoder]

DrewConn,

First, get rid of the OnError GoTo Err_Hdlr, that is such a VB6 way of doing things, in.Net use a Try..Catch..Finally block. Second, on which line are you getting this error?

Senior Qik III, ASP.Net, VB.Net ,SQL Programmer

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

"In the new millennium there will be two kinds of business; those on the net and those out of business"

~ Bill Gates ~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[DrewConn]

I am getting the error on the following line

myfile.Writeline(mystr)


Also I only threw that on err statement for debugging.

 

[TipGiver]

Also, fso = CreateObject("Scripting.filesystemobject") should be dead too, but you use it. Have a look at the streamreader class

 

[DrewConn]

OK, I thought this forum was to get constructive help , not simply pick on your code. So far two responses neither of which even try to address the issue. I have no problem changing code if it speaks to the issue at hand but this code, old or not does work in .NET

 

[RoyVidar]

Please - you were hinted in the right direction. Looking up the help topic for the StreamWriter class, you should find samples to modify your code. Air code (System.IO)

[tt] Dim swStreamReader As New StreamReader(myfilestr)
swStreamReader.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
swStreamReader.WriteLine(mystr)
End While
swStreamReader.Close()[/tt]

Roy-Vidar

 

[RoyVidar]

I said StreamWriter, but types StreamReader all the way through

[tt] Dim swStreamWriter As New StreamWriter(myfilestr)
swStreamWriter.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
swStreamWriter.WriteLine(mystr)
End While
swStreamWriter.Close()[/tt]

Roy-Vidar