Formatting VB Script

50 pts.
Microsoft Visual Studio
Hello programmer's. I have the following VB script in VS2005, ASP.NET that writes sql data to a text file. Can someone help me understanding whether I can format the paydate and checkamt fields? Please see the two sample format problems below. 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? Thank you. Two Sample Format Problems:
  1. Using the below VB script in 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 the “checkamt” is outputting to the checkfile.txt as 848.7000. I need to format it so it outputs as 848.70.
'************************START CREATING THE OUTPUT TEXT FILE***********************************
Dim flag As Boolean

Dim ds As New DataSet

Dim da As New SqlDataAdapter objComm = New SqlCommand("select * from tbl2txt", objConn)

objComm.CommandTimeout = 500

''The flag is to let me know which authentication response to issue

flag = False da.SelectCommand = objComm


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


If intFileNum > 0 Then

flag = True


'extract all fields out 1st, then format and write using the StreamWriter 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 Strdocdate 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("docdate")) Then Strinvno = (dr("docdate"))

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 + " " + " " + Strcheckamt + " " + Strname + " " + Stradd1 + " " + Stradd2 " " + Stradd3 + " " + Stradd4 + " " + Strpostcode + " " + Strdocdate +" " + Strinvoiceamt + " " + Strvendor  " " + Strinvno + " " + " " + Strcheckno + " " + Strsname + " " + StrGrp + " " + StrGroupName)


'Clean Up



'Clean Up



da.Dispose() End If

Software/Hardware used:
Visual Studio

Answer Wiki

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

You should do your formatting only during output just in case you wanted to do a computation on the variable there is nothing stopping from doing this prior to output.

But as per MSDN documentation: Custom Numeric Format Strings.


If the paydate is stored in the database as a date and the checkamt as numeric (and not as strings), and you are not going to perform calculations in your application, you could use the CONVERT function in your query to convert them into an formatted string..

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.

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.

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


Share this item with your network: