replace in html?

[Francismori7]

I have another question.

I have taken an HTML file called "template.html" and got its content. Then I change some variables and save it to a new file in the same directory. Afterwards, there is something else I need to do before saving but I don't know how.

In the template.html file, I have a table which should represent a table from a SQL database which means I would need to loop it. But I don't know how to loop that.

This is my function:

    Public Shared Function GenerateHTMLFile(ByVal ProjectID As Integer)
        Dim Content As String = ""
        Try
            ' Create an instance of StreamReader to read from a file.
            Dim sr As StreamReader = New StreamReader(Directory.GetCurrentDirectory() & "\HTML\template.html")
            Dim line As String
            ' Read and display the lines from the file until the end
            ' of the file is reached.
            Do
                line = sr.ReadLine()
                Content = Content & vbCrLf & line
                'Console.WriteLine(line)
            Loop Until line Is Nothing
            sr.Close()

            Dim conn As New SqlConnection(LUMART.My.Settings.ProjectsConnectionString.ToString())
            Dim rdrQuery As String = "SELECT * FROM Projects WHERE [ID] = " & ProjectID
            Try
                conn.Open()
                Dim cmd As New SqlClient.SqlCommand(rdrQuery, conn)
                Dim rdr As SqlClient.SqlDataReader = cmd.ExecuteReader()

                rdr.Read()

                Content = Replace(Content, "PROJNAME", rdr.Item("Name"))
                Content = Replace(Content, "PROJSTARTDATE", rdr.Item("CreatedAt"))
                Content = Replace(Content, "PROJENDDATE", rdr.Item("DoneAt"))
                Content = Replace(Content, "PROJTOTALTIMESPENT", SecondsToText(rdr.Item("TotalTimeSpent")))
                Content = Replace(Content, "CLIENTNAME", rdr.Item("Client"))
                Content = Replace(Content, "COORDS", rdr.Item("Coords"))
                Content = Replace(Content, "PROJTYPE", rdr.Item("Type"))
                Dim descr As String = rdr.Item("Description")
                descr = Replace(descr, vbCrLf, "<br />")
                Content = Replace(Content, "PROJDESCR", descr)
                Content = Replace(Content, "PROJ_STARTDATE", rdr.Item("CreatedAt"))
                Content = Replace(Content, "PROJ_ENDDATE", rdr.Item("DoneAt"))
                Content = Replace(Content, "PROJ_TOTALTIMESPENT", SecondsToText(rdr.Item("TotalTimeSpent")))
                rdr.Close()
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
                Return False
            Finally
                conn.Close()
            End Try
            Return True
            SaveTextToFile(Content, Directory.GetCurrentDirectory() & "\HTML\" & ProjectID & ".html")
        Catch E As Exception
            ' Let the user know what went wrong.
            Console.WriteLine("The file could not be read:")
            Console.WriteLine(E.Message)
        End Try
        Return True
    End Function

    Public Shared Function SaveTextToFile(ByVal strData As String, _
     ByVal FullPath As String, _
       Optional ByVal ErrInfo As String = "") As Boolean

        Dim bAns As Boolean = False
        Dim objReader As StreamWriter
        Try
            objReader = New StreamWriter(FullPath)
            objReader.Write(strData)
            objReader.Close()
            bAns = True
        Catch Ex As Exception
            ErrInfo = Ex.Message

        End Try
        Return bAns
    End Function

As I said, I need to loop this:

      <tr>
        <td> </td>
        <td> </td>
        <td> </td>
      </tr>

And fill the first one with "StartTime", the second one with "EndTime" and the last column with "Duration". All that gotten from the SQL statement:

"SELECT * FROM WorkedHours WHERE ProjectID = " & ProjectID

Is that possible? If so, then how?

 

[demausdauth]

If I understood your post correctly this might be what you are looking for. Be advised I didn't get a chance to test it.

Call the function where every you need to insert the html code for the table. And if it doesn't work maybe it will give you some ideas

Private Function MakeProjectWorkHours(ByVal ProjectID As Integer) As String

        Dim sbWorkHoursTable As New System.Text.StringBuilder("")

        Using sqlCon As New SqlClient.SqlConnection(LUMART.My.Settings.ProjectsConnectionString.ToString())
            If Not sqlCon.State = ConnectionState.Open Then sqlCon.Open()
            Using sqlCmd As New SqlClient.SqlCommand("SELECT * FROM WorkedHours WHERE ProjectID = @ProjectID", sqlCon)

                sqlCmd.Parameters.Add("@ProjectID", SqlDbType.Int, 4)
                sqlCmd.Parameters("@ProjectID").Value = ProjectID

                Dim sqlReader As SqlClient.SqlDataReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection)

                
                If sqlReader.HasRows Then

                    'open Html table
                    sbWorkHoursTable.Append("<table>")
                    While sqlReader.Read()
                        sbWorkHoursTable.Append("<tr>")
                        sbWorkHoursTable.Append("<td>")
                        sbWorkHoursTable.Append(sqlReader("StartTime").ToString())
                        sbWorkHoursTable.Append("</td>")
                        sbWorkHoursTable.Append("<td>")
                        sbWorkHoursTable.Append(sqlReader("EndTime").ToString())
                        sbWorkHoursTable.Append("</td>")
                        sbWorkHoursTable.Append("<td>")
                        sbWorkHoursTable.Append(sqlReader("Duration").ToString())
                        sbWorkHoursTable.Append("</td>")
                        sbWorkHoursTable.Append("</tr>")
                    End While
                    'Close Html table
                    sbWorkHoursTable.Append("</table>")

                End If
            End Using 'End sqlCmd
        End Using 'End sqlCon

        Return sbWorkHoursTable.ToString()

    End Function

 

[Francismori7]

Instead, I have splitted my template.html in two files: header.html and footer.html. In the middle of those is where the loop should fill the <tr>s and others. So that fixed it. Thanks.