Following the article on how to create dynamic pages in Joomla! by inserting PHP code in the content, there is another case that I have encountered countless times; the content of the article does not need to change very often and it is systematic in the sense that it could be handled by a computer program. But how can you access the contents of a Joomla article and modify it?

Well, the content of our Joomla! site lies somewhere in the database. We will try to access and modify it using VB.NET. Although creating a new Joomla! article, or playing with the menus, or doing other stuff with VB.NET on your Joomla! installation is theoretically possible, I wouldn't dare doing it as an unspecified number of bad things may happen with the structure of the database. Instead, we will pick an existing article by its ID (which is unique and does not change) and manipulate its content, using our program to write/modify HTML code in it.

Let's open VB.NET (I currently use version 2013), and create a new project. I assume that we are using a MySQL database, so we need to add a reference to the MySQL connector. Then we need to add this line before the declaration of the class.

Imports MySql.Data.MySqlClient

Now, I assume that we are working locally (for example, using Xampp) and not in the live site. Then the server name can be "localhost" or "127.0.0.1". The connection string is given by the following function: 

Public Function GetConnectionString() As String
 
  GetConnectionString = "server=localhost;" & _
    "uid=myusername;" &  _
    "pwd=mypassword;" &  _
    "database=josdb;" &  _
    "charset=utf8;"
 
End Function

where, you need to change "myusername", "mypassword" and "josdb" with your username, password and database name, respectively. In most cases, the encoding will be UTF8. Next, we open the Joomla! article with the following sub: 

Public Sub UpdateJoomlaArticle(gJoomlaID As Long)
 
 Dim Conn As New MySqlConnection
 Dim Cmd As New MySqlCommand
 Dim Reader As MySqlDataReader
 Dim Ordinal As Integer
 Dim sData As String
 
 'get the connection string
 Conn.ConnectionString = GetConnectionString()
 
 Try
 
  Conn.Open()
  Cmd.Connection = Conn
 
  'ATTENTION! you need to replace "jos_" with the actual prefix
  Cmd.CommandText = "SELECT * FROM `jos_content` WHERE `id`='" & _
    gJoomlaID.ToString & "'"
 
  Reader = Cmd.ExecuteReader
 
  'we need the column number corresponding to column "introtext"
  Ordinal = Reader.GetOrdinal("introtext")
 
  If Reader.Read() Then
 
   'Load sData with the current HTML code of the article
   sData = Reader.GetString(Ordinal)
 
   Reader.Close()
 
   'process the HTML code here! Modify it, or create new content.
   'sData is passed by reference
   JoomlaFixArticle(sData)
 
   'now we put back the content in the article
   'ATTENTION! you need to replace "jos_" with the actual prefix
   Cmd.CommandText = "UPDATE `jos_content` SET `introtext`= (@text) " & _
     "WHERE `id`='" & gJoomlaID.ToString & "'"
 
   'always use AddWithValue for security reasons
   Cmd.Prepare()
   Cmd.Parameters.AddWithValue("@text", sData)
   Cmd.ExecuteNonQuery()
 
  End If
 
 Catch ex As MySqlException
 
  MessageBox.Show("Error " & ex.Number & " has occurred: " & ex.Message, _
     "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
 
 End Try
 
 Conn.Close()
 
End Sub

where, you need to replace "jos_" with the actual prefix that you have used. For reasons of clarity, I have created a separate function to write/modify the HTML content. The HTML code (sData) is passed by reference:

Private Sub JoomlaFixArticle(ByRef sData As String)

 sData = sData & "<p>The article was appended on " & Format(Now) & "</p>" & vbCrLf

End Sub

That's it! All you have to do is find the ID of the article you wish to manipulate (there is a corresponding column in the article manager of your Joomla! back-end), and pass it as an argument to UpdateJoomlaArticle( ). You will observe the modification of the article each time you run UpdateJoomlaArticle( ).