Create A Fixed Text File From SQL DB Table

[ilfoprogrammer]

Hello programmers. I am trying to retreive data from an SQL db table using VB in a .NET aspx page (visual studio 2005), confirm that there are records (i.e. record count, etc.) then print the data into a fixed text file somewhere on my network. I did this in Visual Basic 6 using selects, padstrings, freefile, recordcount properties, etc. hitting an Access db table. However I am not able to repeat this in .aspx page. Below is the script I used to output the text file from the MSAccess table. Thanks for the support.

Public Sub tbl2txt()

Dim wrk As dao.Workspace
Dim db As dao.Database
Dim cnn As dao.Connection
Dim rs As dao.Recordset
Dim strTable As String
Dim strFileName As String
Dim intFileNum As Integer

On Error GoTo err_Tbl2Txt

strTable = "QBtmLine"
' Create Microsoft Jet Workspace object.
Set wrk = CreateWorkspace("", "admin", "", dbUseJet)

Set db = wrk.OpenDatabase("U:\DEVHELP_DBs\Ilfornaio\Access2000_a\botline_2k.mdb")

Set rs = db.OpenRecordset("Select * From " & strTable)

With rs
'check to see that records were returned
If rs.RecordCount < 1 Then
MsgBox "No records found for Bottom Line Check File"
Me.Refresh
End If
intFileNum = FreeFile

strFileName = "H:\Acct\BT_2kTesting\blinetxtapp_2k\Blintes1.txt"

Open strFileName For Output As intFileNum

.MoveFirst
Do While Not .EOF

'File is fixed
Print #intFileNum, Format(!paydate, "mm/dd/yy") _
& PadString(Format(!checkamt, "###,#00.00"), " ", 13, False) _
& PadString(!Name, " ", 80, True) & PadString(!add1, " ", 35, True) _
& PadString(!add2, " ", 35, True) & PadString(!add3, " ", 35, True) _
& PadString(!add4, " ", 2, True) & PadString(!postcode, " ", 10, True) _
& (Format(!docdate, "mm/dd/yy") _
& PadString(Format(!invoiceamt, "###,#00.00"), " ", 13, False) _
& PadString(!vendor, " ", 72, False) & PadString(!invno, " ", 34, False) _
& PadString(!checkno, " ", 24, False) & PadString(!sname, " ", 25, True) _
& PadString(!Group, " ", 15, True) & PadString(!groupname, " ", 35, True))

.MoveNext
Loop

End With

Close intFileNum
Set rs = Nothing
db.Close
Set wrk = Nothing
Exit Sub

err_Tbl2Txt:
MsgBox "Error" & Err.Description & "occurred in Tbl2Txt, Correct then Rerun."

End Sub

[Hack]

Do you know how to run an SQL query against a database using VB.NET and get a recordset in return?

[ilfoprogrammer]

Below is my new working vb.net script which creates a text file if there is data in the table. However it is only putting out manual text. I need to loop through the table assign the fields (I guess) to variables and then somehow get the variable values into something similar to the writeline in a fixed format. I am getting the count of the records, but I'm kind of lost from there. Any help is appreciated.

CURRENT SCRIPT:

Partial Class Testing_Page
    Inherits System.Web.UI.Page
    Const FILE_NAME As String = "H:\Acct\BT_2kTesting\blinetxtapp_2k\Blintes1.txt"
--------------------------------------------------------------
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim ds As New DataSet
        Dim da As New SqlDataAdapter
        'Dim dr As DataRow
        Dim objConn As Data.SqlClient.SqlConnection
        Dim objComm As Data.SqlClient.SqlCommand
        'Dim strFileName As String
        Dim intFileNum As Integer
        Dim flag As Boolean
        objConn = New SqlClient.SqlConnection
        objConn.ConnectionString = "Data Source=CODA2K;Persist Security Info=True;password=proco; USER;Initial Catalog=proco;"
        objConn.Open()
 
        '************************KEN START CREATING THE OUTPUT TEXT FILE***********************************
        objComm = New SqlCommand("select * from tbl2txt", objConn)
        objComm.CommandTimeout = 500
        flag = False
        da.SelectCommand = objComm
        da.Fill(ds)
        'intFileNum = ds.Tables.Item
        intFileNum = ds.Tables(0).Rows.Count
         objConn.Close()
         If intFileNum > 0 Then
            flag = True
 ‘**************HERE I NEED TO LOOP THROUGH THE TABLE, GET EACH FIELD VALUE PER RECORD AND WRITE IT TO THE FILE_NAME IN A THE BELOW FIXED FORMAT (SEE FORMAT BELOW).*****************
             Using sw As StreamWriter = File.CreateText(FILE_NAME)
                sw.WriteLine("Testing 1.")
                sw.WriteLine("I can write {0} or floats {1}, and so on.", 1, 4.2)
                sw.Close()
            End Using
             'Clean Up   
            objConn.Dispose()
            ds.Dispose()
            da.Dispose()
        End If
         If flag = False Then
            lblMessage.Visible = True
            lblMessage.Text = "No Records Found. Please try again or call your Systems Programmer at 444-444-4444."
 
            Exit Sub
        End If
    End Sub
End Class