Create A Fixed Text File From SQL DB Table


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Create A Fixed Text File From SQL DB Table

Threaded View

  1. #1
    Join Date
    Oct 2009
    Posts
    6

    Create A Fixed Text File From SQL DB Table

    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.
    Code:
    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
    Last edited by Hack; 10-30-2009 at 07:35 AM. Reason: Added Code Tags

Similar Threads

  1. Replies: 0
    Last Post: 07-25-2002, 04:29 PM
  2. Getting a GUI to function
    By Eric in forum Java
    Replies: 1
    Last Post: 11-27-2001, 06:53 AM
  3. Creating a fixed Length text file
    By Rob Pesce in forum VB Classic
    Replies: 2
    Last Post: 11-10-2000, 11:15 PM
  4. SQL server administrative knowledge
    By Becky in forum Database
    Replies: 1
    Last Post: 07-11-2000, 03:27 PM
  5. Re: ODBC error
    By Devaraj in forum Enterprise
    Replies: 0
    Last Post: 05-11-2000, 12:48 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center