Please help debug this ASP.Net [VB] code. Trying to write to text file from SQL Server DB.

Question

I am a PHP programmer. I have no .Net coding experience (last seen it 4 years ago). Not interested in code-behind model since this is a quick temporary hack.

What I am trying to do is generate an output.txt file whenever the user submits new data. So an output.txt file if exists should be replaced with the new one.

I want to write data in this format :

123|Java Programmer|2010-01-01|2010-02-03
124|VB Programmer|2010-01-01|2010-02-03
125|.Net Programmer|2010-01-01|2010-02-03

I don't know VB, so not sure about string manipulations. Hope a kind soul can help me with this. I will be grateful to you. Thank you :)

<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script language="vb" runat="server">
  sub Page_Load(sender as Object, e as EventArgs)

  Dim sqlConn As New SqlConnection("Data Source=winsqlus04.1and1.com;Initial Catalog=db28765269;User Id=dbo2765469;Password=ByhgstfH;") 
  Dim myCommand As SqlCommand
  Dim dr As SqlDataReader

  Dim FILENAME as String = Server.MapPath("Output4.txt")

    Dim objStreamWriter as StreamWriter
   ' If Len(Dir$(FILENAME)) > 0 Then Kill(FILENAME)
    objStreamWriter = File.AppendText(FILENAME)

   Try
   sqlConn.Open()
   'opening the connection
   myCommand = New SqlCommand("SELECT  id, title, CONVERT(varchar(10), expirydate, 120) AS [expirydate],CONVERT(varchar(10), creationdate, 120) AS [createdate] from tblContact where flag = 0 AND ACTIVE = 1", sqlConn)
  'executing the command and assigning it to connection 
  dr = myCommand.ExecuteReader()

  While dr.Read()

    objStreamWriter.WriteLine("JobID: " & dr(0).ToString())
    objStreamWriter.WriteLine("JobID: " & dr(2).ToString())
    objStreamWriter.WriteLine("JobID: " & dr(3).ToString())

  End While
  dr.Close()
  sqlConn.Close()
  Catch x As Exception
  End Try

    objStreamWriter.Close()

    Dim objStreamReader as StreamReader
    objStreamReader = File.OpenText(FILENAME)

    Dim contents as String = objStreamReader.ReadToEnd()

    lblNicerOutput.Text = contents.Replace(vbCrLf, "<br>")

    objStreamReader.Close()
  end sub
</script>

<asp:label runat="server" id="lblNicerOutput" Font-Name="Verdana" />

Answer 1

You can use .Net's Composite Formatting feature:

objStreamWriter.WriteLine("{0}|{1}|{2:yyyy-MM-dd}|{3:yyyy-MM-dd}", _
                          dr(0),  dr(1), dr(2), dr(3))

---

Some notes on your current code:

• (EDIT) You have an empty Catch block (Catch x As Exception End Try) which is silently swallowing any errors. You should never do that.
• Hungarian notation is frowned upon. Your objStreamWriter would be better named writer or fileWriter.
• You don't need to convert the dates to VARCHARs on the server; you can output them in that format by using the datetime format string yyyy-MM-dd (As in my sample, or as dateTime.ToString("yyyy-MM-dd")
• File.AppendText will append to an existing file. Are you sure you don't want to call File.CreateText, which will automatically erase the file if it already exists?
• You should close your objects using the Using statement, like this:

Using objStreamWriter As StreamWriter = File.AppendText(FILENAME)
    ...
End Using