locked
Create A Fixed Text File From SQL DB Table RRS feed

  • Question

  • User1779186697 posted

    Sources of Assistance:

    http://it.toolbox.com/ask-a-question/asp-dotnet-l

    http://forums.asp.net/default.aspx/7

     

    Create A Fixed Text File From SQL DB Table

     

    Hello, I am using VS2005 and would like to add script to my aspx page to Output a Fixed Text File From my SQL DB Table to a location on the network.  I did this in VB6 using a FREEFILE and PADSTRING off of an Access DB table.  However, I can't seem to get it right in the .NET aspx environment.  Can someone assist me with this?  I have added the VB6 script that is working now and the .NET aspx vb script that I'm trying to move it to.  Thank you.

     

    VB6 SCRIPT:

    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

    <st1:place w:st="on">Loop</st1:place>

     

    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

     

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    ASP.NET .ASPX SCRIPT (NEED CORRECTING):

     

    Dim objConn As Data.SqlClient.SqlConnection

    Dim objComm As Data.SqlClient.SqlCommand

    Dim objrec As Data.SqlClient.SqlDataReader

    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=codaprod; USER;Initial Catalog=CODAPROD;"

    objConn.Open()

    objComm = New SqlCommand("select * from tbl2txt", objConn)

    objComm.CommandTimeout = 500

    objrec = objComm.ExecuteReader()

     

    flag = False

     

    While (objrec.Read())

    If objrec.recordcount > 0 Then

          flag = True

     

          intFileNum = FreeFile()

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

    Open strFileName For Output As intFileNum

     

          '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)))

     

    Exit While

          End If

     

    End While

     

    Close(intFileNum)

    If flag = False Then

    lblMessage.Visible = True

    lblMessage.Text = "No Records Found. Please try again or call your Systems Programmer at 415-945-4202."

     

    Exit Sub

    End If

     

    Wednesday, October 28, 2009 6:21 PM

Answers

  • User-952121411 posted

    Hello and welcome to .NET!  I have a few suggestions on how to go about solving your need in general, but I must start by stating that you did not give any specefics on what the exact issue you were having with your code.  Therefore, I can give some general reccomendations on how to solve your problem.

    1st off, I was once where you are; going from a VB6 to a .NET developer and I can tell by the way you wrote your .NET code that you are trying to do things in .NET the VB6 way (#Print, calling variables 'objrec' for Recordsets, etc).  My suggestion is as you develop to try and find the .NET equivilent of how to do the same end result that you did back in VB6.  For example classic ADO with recordsets was replaced with ADO.NET and DataSets, DataTables, DataReaders, etc.  They have some of the same principals as being disconnected data objects (except the DataReader which is connected), but are much more powerful.  Learn them well as they can and will be the backbone of your data access methods going forward (until Microsoft comes out with a new data access method that replaces ADO.NET Wink; they have never done that before, right?  DAO,RDO,ADO,ADO.NET,EF, etc...).

    I reccomend looking at some of the links within the link below to continue familiarizing yourself with ADO.NET:

    ADO.NET:

    http://msdn.microsoft.com/en-us/library/e80y5yhx(VS.80).aspx

    As far as writiing to or manipulating files, your new fried will be the System.IO or System.Text namespaces in .NET.  Those framework libraries offer a vast amount of wrapped up functionality to write/read/ and do all sorts of manipulation that you need.

    To get you started, here is a terrific example from the MSDN on how to use a 'StreamWriter' object (from the System.IO namespace) to write to a text file:

    How to: Write Text to a File:

    http://msdn.microsoft.com/en-us/library/6ka1wd3w.aspx

    Here is another good link-

    Basic File I/O:

    http://msdn.microsoft.com/en-us/library/336wast5.aspx

    If I was doing what you are trying to do by reading all records returned from a database into a formatted text file, I would modify the code to look more like the following:

    'Import at the top of the class
    Imports System.IO
    Imports System.Data.SqlClient


    The code that goes within the class:

            Dim ds As New DataSet
            Dim da As New SqlDataAdapter
            Dim conn As New SqlConnection
            Dim cmd As New SqlCommand
    
            Try
                'Open a connection to the database
                conn.ConnectionString = "Data Source=CODA2K;Persist Security Info=True;password=codaprod; USER;Initial Catalog=CODAPROD;"
                conn.Open()
    
                'Build command object using string SQL and DB 
                cmd.CommandText = "select * from tbl2txt"
                cmd.Connection = conn
    
                da.SelectCommand = cmd
                da.Fill(ds)
    
                'Close the db connection
                conn.Close()
    
                'Create a StreamWriter to write to the text file   
                Dim sw As New System.IO.StreamWriter("C:\MyLog.txt", True)
                'Loop through each DataRow in the DataTable within the DataSet's 1st table (index 0)
                For Each dr As DataRow In ds.Tables(0).Rows
                    'Writing to file (add TimeStamp to beginning)   
                    sw.WriteLine(DateTime.Now.ToShortDateString() & ": This is line 1!")
                    sw.WriteLine("This is line 2!")
                    sw.WriteLine("This is line 3!")
                Next
    
                'Clean up   
                sw.Flush()
                sw.Close()
    
            Catch ex As SqlException
                'Do any SQL exception handling here if needed/wanted
            Catch ex As Exception
                'Do any exception handling here if needed/wanted
            Finally
                'Clean Up
                conn.Dispose()
                ds.Dispose()
                da.Dispose()
            End Try


    Now the exception handling, clean up, etc are very basic here but I am just trying to provide a good example of how to use ADO.NET and a StreamWriter to accomplish what you need to do.  I know the advice and content may have been more than what you needed, but I remember some of the road bumps moving to .NET as well, so I know what it is like.

    If you still have issues or want to post back with another question, please do so.

    Hope this helps! Smile

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 29, 2009 9:57 AM

All replies

  • User-952121411 posted

    Hello and welcome to .NET!  I have a few suggestions on how to go about solving your need in general, but I must start by stating that you did not give any specefics on what the exact issue you were having with your code.  Therefore, I can give some general reccomendations on how to solve your problem.

    1st off, I was once where you are; going from a VB6 to a .NET developer and I can tell by the way you wrote your .NET code that you are trying to do things in .NET the VB6 way (#Print, calling variables 'objrec' for Recordsets, etc).  My suggestion is as you develop to try and find the .NET equivilent of how to do the same end result that you did back in VB6.  For example classic ADO with recordsets was replaced with ADO.NET and DataSets, DataTables, DataReaders, etc.  They have some of the same principals as being disconnected data objects (except the DataReader which is connected), but are much more powerful.  Learn them well as they can and will be the backbone of your data access methods going forward (until Microsoft comes out with a new data access method that replaces ADO.NET Wink; they have never done that before, right?  DAO,RDO,ADO,ADO.NET,EF, etc...).

    I reccomend looking at some of the links within the link below to continue familiarizing yourself with ADO.NET:

    ADO.NET:

    http://msdn.microsoft.com/en-us/library/e80y5yhx(VS.80).aspx

    As far as writiing to or manipulating files, your new fried will be the System.IO or System.Text namespaces in .NET.  Those framework libraries offer a vast amount of wrapped up functionality to write/read/ and do all sorts of manipulation that you need.

    To get you started, here is a terrific example from the MSDN on how to use a 'StreamWriter' object (from the System.IO namespace) to write to a text file:

    How to: Write Text to a File:

    http://msdn.microsoft.com/en-us/library/6ka1wd3w.aspx

    Here is another good link-

    Basic File I/O:

    http://msdn.microsoft.com/en-us/library/336wast5.aspx

    If I was doing what you are trying to do by reading all records returned from a database into a formatted text file, I would modify the code to look more like the following:

    'Import at the top of the class
    Imports System.IO
    Imports System.Data.SqlClient


    The code that goes within the class:

            Dim ds As New DataSet
            Dim da As New SqlDataAdapter
            Dim conn As New SqlConnection
            Dim cmd As New SqlCommand
    
            Try
                'Open a connection to the database
                conn.ConnectionString = "Data Source=CODA2K;Persist Security Info=True;password=codaprod; USER;Initial Catalog=CODAPROD;"
                conn.Open()
    
                'Build command object using string SQL and DB 
                cmd.CommandText = "select * from tbl2txt"
                cmd.Connection = conn
    
                da.SelectCommand = cmd
                da.Fill(ds)
    
                'Close the db connection
                conn.Close()
    
                'Create a StreamWriter to write to the text file   
                Dim sw As New System.IO.StreamWriter("C:\MyLog.txt", True)
                'Loop through each DataRow in the DataTable within the DataSet's 1st table (index 0)
                For Each dr As DataRow In ds.Tables(0).Rows
                    'Writing to file (add TimeStamp to beginning)   
                    sw.WriteLine(DateTime.Now.ToShortDateString() & ": This is line 1!")
                    sw.WriteLine("This is line 2!")
                    sw.WriteLine("This is line 3!")
                Next
    
                'Clean up   
                sw.Flush()
                sw.Close()
    
            Catch ex As SqlException
                'Do any SQL exception handling here if needed/wanted
            Catch ex As Exception
                'Do any exception handling here if needed/wanted
            Finally
                'Clean Up
                conn.Dispose()
                ds.Dispose()
                da.Dispose()
            End Try


    Now the exception handling, clean up, etc are very basic here but I am just trying to provide a good example of how to use ADO.NET and a StreamWriter to accomplish what you need to do.  I know the advice and content may have been more than what you needed, but I remember some of the road bumps moving to .NET as well, so I know what it is like.

    If you still have issues or want to post back with another question, please do so.

    Hope this helps! Smile

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 29, 2009 9:57 AM
  • User1779186697 posted

    Thanks for the information.  I have a couple of problems that hopefully you can help me with.  One, I get an error message "The device is not ready"  at the dim line below.  Do you know what could be causing this?  Two, How can I get the table data into the text file instead of raw text?  Thank you.

    Dim sw As New System.IO.StreamWriter("D:\CheckFile.txt", True)

     

    Thursday, November 19, 2009 7:49 PM
  • User1779186697 posted

    The following script is working to output a text file with a manual string text for each record in the dataset.  However, can you help me with outputing the actual data from the dataset in a fixed format?  Thanks again.

    ilfoprogrammer

    CODE:

    'Use the Imports statement on the System and System.Data namespaces so that you are not required to qualify declarations

    'in those namespaces later in your code.

    Imports System

    Imports System.Data.OleDb

    Imports System.Data.SqlClient

    Imports System.Data

    Imports System.IO

     

    Dim objConn As Data.SqlClient.SqlConnection

    Dim objComm As Data.SqlClient.SqlCommand

    Dim flag As Boolean

    Dim ds As New DataSet

    Dim intFileNum As Integer

    Dim da As New SqlDataAdapter

     

    objConn = New SqlClient.SqlConnection

            objConn.ConnectionString = "Data Source=codK;Persist Security Info=True;password=xxx; USER;Initial Catalog=COD;"

            objConn.Open()

     

    objComm = New SqlCommand("select * from tbl2txt", objConn)

            objComm.CommandTimeout = 500

            flag = False

     

            da.SelectCommand = objComm

            da.Fill(ds)

            intFileNum = ds.Tables(0).Rows.Count

     

            objConn.Close()

     

            If intFileNum > 0 Then

                flag = True

     

    'START CREATE THE OUTPUT FILE HERE

     

                Dim sw As New System.IO.StreamWriter("c:\CheckFile.txt")

                For Each dr As DataRow In ds.Tables(0).Rows

                    sw.WriteLine("test 1")

                Next

     

                'Clean Up

                sw.Flush()

                sw.Close()

     

                'Clean Up  

                objConn.Dispose()

                ds.Dispose()

                da.Dispose()

     

     

            End If

    Thursday, November 19, 2009 9:09 PM
  • User-952121411 posted

    One, I get an error message "The device is not ready"  at the dim line below.  Do you know what could be causing this?
     

    Yes this is caused when your code is trying to run, but you currently have that file open (Checkfile.txt).  Try closing it and re-running.  If you still get the error and the .txt file is closed it may be a permissions issue for ASP.NET to that file or folder.

    Two, How can I get the table data into the text file instead of raw text?

    If I understand correctly you want to loop through the datatable with your StreamWriter and write the data to the text file, correct?  Take a look below:

            'Iterate through each row in the DataSet and write it to the text file
            For Each dr As DataRow In ds.Tables(0).Rows
    
                'Make sure the value coming back from the DataBase is Not Null.  If it has value
                'then write it using the StreamWriter to the text file.
                'WriteLine makes a new line for each entry
                If Not IsDBNull(dr("MyFieldName")) Then sw.WriteLine(dr("MyFieldName"))
    
            Next


     

    Friday, November 20, 2009 9:13 AM
  • User1779186697 posted

    Beautiful.  We're definitely getting to where I want to be.  Your script put "MyFieldName" into the text file the number of times of records. Next challenge, I tried adding an addtional field (i.e. "MyFieldName2", etc.) like in the below altered script but got the error: Operator 'Or' is not defined for type 'Date' and string "MyFieldName2".  Can you help me with that.  I also will be trying to format the fields (i.e. Format("MyFieldName", "mm/dd/yy"), Format(MyFieldName2, "###,#00.00"), etc.) Thanks again.

    For

    Each dr As DataRow In ds.Tables(0).Rows

    If Not IsDBNull(dr("MyFieldName") Or ("MyFieldName2")) Then sw.WriteLine(dr("MyFieldName"), ("MyFieldName2"))

    Next

     

    Friday, November 20, 2009 3:48 PM
  • User-952121411 posted

    By the way "MyFieldName" is the name of the field within the DataRow which will correspond to the column returned from your SQL was sent to the database.  I am assuming this is what you intended.

    The 'OR' is not going to work as you tried.  You will need to extract both fields out 1st, then format and write using the StreamWriter lkike follows:

    For Each dr As DataRow In ds.Tables(0).Rows
    
       Dim MyValue1 As String = String.Empty
       Dim MyValue2 As String = String.Empty
    
       If Not IsDBNull(dr("MyFieldName")) Then MyValue1 = (dr("MyFieldName"))
       If Not IsDBNull(dr("MyFieldName2")) Then MyValue2 = (dr("MyFieldName2"))
    
       'Now write to the streamwriter, adding a comma between the values
       sw.WriteLine(MyValue1 + "," + MyValue2) 
    
    Next
    


     

    Friday, November 20, 2009 4:04 PM
  • User1779186697 posted

    Thanks again for the excellent information.  It is spot on for the most part.  As we move forward I really like to know where I could find this type of information.  For now however, I have all the records in the text file (GREAT!!!).  I think the last thing I would like to understand is how to get the formatting set appropriately.  I tried the following but it's not working;

    sw.WriteLine(Format(MyValue1 , "mm/dd/yy") + "" + (Format(MyValue2, "###,#00.00"), " ", 13, False))    

    As indicated in my original posting the VB6 formatting was like this;

    '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))

    Friday, November 20, 2009 9:09 PM
  • User-952121411 posted

    As we move forward I really like to know where I could find this type of information.
     

    The (2) best places are the internet, and the MSDN (also available on the internet).  If you have the MSDN installed locally, there is a great tool within the Visual Sudio.NET IDE called "Dynamic Help".  If it is not already turned on you can activate it from the "Help" menu.  What this does is typically open a docked window below the "Solution Explorer" window that displays help topics specefic to the line of code your cursor is highligting.  For Example, if you had your cursor on "StreamWriter" then several links in the MSDN would appear for the StreamWriter class.  If you do not have the MSDN intalled locally because you have not purchased it, don't worry; all the same information is avaliable on the internet.

    As far as formatting, I will provide some VB.NET formatting links that you can read so you can get the strings the way you want them:

    Here is the best page that has links to all different types of formatting (date, numbers, etc.)

    http://msdn.microsoft.com/en-us/library/fbxft59x.aspx

    Custom Date and Time Format Strings:

    http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx

    http://www.vb-helper.com/howto_net_date_tostring_custom.html

     

    Monday, November 23, 2009 8:42 AM
  • User1779186697 posted

    After doing much research i still am unable to resolve how or where to custom format each field in a datarow.  Can someone help me with this?  Do I do the formatting in the Dim section or in the if not – then section or in the streamwriter section?  Thank you.

    Sample Format Problems:

    1. Using the below VB script in asp.net the paydate is outputting to the checkfile.txt as 1/1/2010.  I need to format it so it outputs as 1/1/10.

    2. Using the below VB script in asp.net the checkamt is outputting to the checkfile.txt as 848.7000.  I need to format it so it outputs as 848.70.

     

    Dim sw As New System.IO.StreamWriter("c:\CheckFile.txt")

    For 

    Each dr As DataRow In ds.Tables(0).Rows

    Dim Strpaydate As String = String.Empty

    Dim Strcheckamt As String = String.Empty

    Dim Strname As String = String.Empty

    Dim Stradd1 As String = String.Empty

    Dim Stradd2 As String = String.Empty

    Dim Stradd3 As String = String.Empty

    Dim Stradd4 As String = String.Empty

    Dim Strpostcode As String = String.Empty

    Dim Strinvno As String = String.Empty

    Dim Strinvoiceamt As String = String.Empty

    Dim Strvendor As String = String.Empty

    Dim Strcheckno As String = String.Empty

    Dim Strsname As String = String.Empty

    Dim StrGrp As String = String.Empty

    Dim StrGroupName As String = String.Empty

    'Make sure the value coming back from the DataBase is Not Null. If it has value

     'then write it using the StreamWriter to the text file.

     'WriteLine makes a new line for each entry  

    If Not IsDBNull(dr("paydate")) Then Strpaydate = (dr("paydate"))

    If Not IsDBNull(dr("checkamt")) Then Strcheckamt = Format(dr("checkamt"))

    If Not IsDBNull(dr("name")) Then Strname = (dr("name"))

    If Not IsDBNull(dr("add1")) Then Stradd1 = (dr("add1"))

    If Not IsDBNull(dr("add2")) Then Stradd2 = (dr("add2"))

    If Not IsDBNull(dr("add3")) Then Stradd3 = (dr("add3"))

    If Not IsDBNull(dr("add4")) Then Stradd4 = (dr("add4"))

    If Not IsDBNull(dr("postcode")) Then Strpostcode = (dr("postcode"))

    If Not IsDBNull(dr("invno")) Then Strinvno = (dr("invno"))

    If Not IsDBNull(dr("invoiceamt")) Then Strinvoiceamt = (dr("invoiceamt"))

    If Not IsDBNull(dr("vendor")) Then Strvendor = (dr("vendor"))

    If Not IsDBNull(dr("checkno")) Then Strcheckno = (dr("checkno"))

    If Not IsDBNull(dr("sname")) Then Strsname = (dr("sname"))

    If Not IsDBNull(dr("Grp")) Then StrGrp = (dr("Grp"))

    If Not IsDBNull(dr("GroupName")) Then StrGroupName = (dr("GroupName"))

    'Now write to the streamwriter, adding a space between the values

    sw.WriteLine(Strpaydate + " " + Stradd4 + " " + " " + Strname + " " + Strcheckamt + " " + Stradd1 + " " + Stradd2 + " " + Stradd3 + " " _" " + Strpostcode + " " + Strinvno + " " + Strinvoiceamt + " " + Strvendor + " " + Strcheckno _" " + Strsname + " " + StrGrp + " " + StrGroupName)

    Next

    Monday, January 11, 2010 4:45 PM