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

Hybrid 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

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Do you know how to run an SQL query against a database using VB.NET and get a recordset in return?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Oct 2009
    Posts
    6
    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:
    Code:
    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
    Last edited by Hack; 11-05-2009 at 12:18 PM. 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