Create A Fixed Text File From SQL DB Table

50 pts.
Tags:
ASP.NET
SQL Database
SQL tables
Hello programmers. After doing much research I am unable to resolve how or where to custom format each field of my SQL table output into my text file. Can someone help me with enhancing the below vb script? I need to reformat certain values. Do I do the formatting in the Dim section or in the if not – then section or in the streamwriter section? Two 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

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

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


Software/Hardware used:
vb2005, windows xp

Answer Wiki

Thanks. We'll let you know when a new response is added.

The formatting should be done when writing the text to the file, but I don’t think the WriteLine method has that functionality.

One way to do it (in case it could not be done by the front end) would be getting the data already formatted from the database.

If you are using SQL Server, the Convert function could help.

—————

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following