Eureka!

Many sites have alot of different select fields in their forms. In order to streamline my coding efforts I have consolidated and am using a function to satisfy all my drop-down needs:

    function MakeSelectList($strSQL, $strParameters, $blnShowBlank)    {        $arrData = GetArrayQuery($strSQL);?>                    <select <?= $strParameters ?>>                        <option value=""></option>                        <option value="<?= $arrData[$i][0] ?>"><?= $arrData[$i][1] ?></option>                    </select>The GetArrayQuerry() function is another streamlined function I use to return data as a data array from the database. This way I only deal with arrays and am removing my code from having to deal directly with the database:
    function GetArrayQuery($strSQL)    {        $i = 0;        $objRS = $db->query($strSQL);        CheckError($objRS);        while ($objRS->fetchInto($arrRow)) {$arrData[$i++] = $arrRow;}

        return $arrData;    }
Let me know if you have more elegant ways of doing this! I’m always open to new ideas.  

A very powerful and extremely simple tool to convert your SQL results into an XML file is the following:

    Dim objDS As New DataSet

    Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click        Dim objConn As New SqlConnection        Dim objDA As SqlDataAdapter

        Try            objConn.ConnectionString = "server=(local);database=northwind;integrated security=SSPI"            objConn.Open()            objDA = New SqlDataAdapter("SELECT * FROM customers ORDER BY companyname", objConn)            objDA.Fill(objDS, "customers")            objConn.Close()            dgData.DataSource = objDS.Tables("customers")        Catch objError As Exception            MessageBox.Show(objError.Message)        End Try    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click        Try            objDS.WriteXml("c:projectscustomers.xml")        Catch objError As Exception            MessageBox.Show(objError.Message)        End Try

Using this method you can save any table in your dataset to an XML file. This can come in handy when transfering data to clients, or even to coworkers. Not to mention to make a backup of your data before making changes.

In order to use data while disconnected from a database, it needs to be loaded in one form or other from the database and kept in memory or stored into a different object. The following code illustrates how to do this by using a datagrid on a windows form. This takes the previous connection how-to a step further, because we are closing the connection after the info is loaded, then allowing for changes to the data in the dataset, and afterwards we can save the newly changed data after opening a new connection again. This example is using a windows form with a datagrid (dgData), a button to load the data (btnLoad) and a button to save the data (btnSave):

    Dim objDS As New DataSet

    Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click        Dim objConn As New SqlConnection        Dim objDA As SqlDataAdapter

        Try            objConn.ConnectionString = "server=(local);database=northwind;integrated security=SSPI"            objConn.Open()            objDA = New SqlDataAdapter("SELECT * FROM customers ORDER BY companyname", objConn)            objDA.Fill(objDS, "customers")            objConn.Close()            dgData.DataSource = objDS.Tables("customers")        Catch objError As Exception            MessageBox.Show(objError.Message)        End Try    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click        Dim objConn As New SqlConnection        Dim objDA As SqlDataAdapter        Dim objBuilder As SqlCommandBuilder

        Try            objConn.ConnectionString = "server=(local);database=northwind;integrated security=SSPI"            objConn.Open()            'the following is being used as a template to create the update, delete, and instert commands            objDA = New SqlDataAdapter("SELECT * FROM customers ORDER BY companyname", objConn)            objDA.Fill(objDS, "customers")            objBuilder = New SqlCommandBuilder(objDA)            objDA.DeleteCommand = objBuilder.GetDeleteCommand            objDA.InsertCommand = objBuilder.GetInsertCommand            objDA.UpdateCommand = objBuilder.GetUpdateCommand

            objDA.Update(objDS, "customers")            objConn.Close()        Catch objError As Exception            MessageBox.Show(objError.Message)        End Try    End Sub

This scenario comes in handy when developing applications that remote users may use. However, the inherent complexity comes into play when the user connects again and attempts to update records that have been changed since they were originally downloaded by this user.

The following example illustrates how to connect to a database using a connectionstring. We’re also using a datareader to access multiple rows, and a scalar to access a single value.

        Dim objConn As New SqlConnection        Dim objComm As New SqlCommand        Dim objReader As SqlDataReader        Dim strText As String

        Try            objConn.ConnectionString = "server=(local);database=northwind;integrated security=SSPI"            objConn.Open()            MessageBox.Show("Connection Success!")            objComm.Connection = objConn            objComm.CommandType = CommandType.Text            objComm.CommandText = "SELECT @@VERSION"            MessageBox.Show(objComm.ExecuteScalar())            objComm.CommandText = "SELECT companyname FROM customers ORDER BY companyname"            objReader = objComm.ExecuteReader            'read must be done first to prime the data reader.             'if trying to access item(0) prior to doing a read an exceptionwill occur!            While objReader.Read                strText += objReader.Item(0) + NewLine            End While            MessageBox.Show(strText)        Catch objError As Exception            MessageBox.Show(objError.Message)        End Try

As you can see this is a fairly simple example using the Northwind database. More info will be soon to come on creating your own connectionstrings, etc.

While working on my latest .NET project I ran into the problem of how to specify the program to use Sub main() as the startup procedure. I had originally created my project as a Windows Form application, so the sub main() settings were not set by defeault.

The first thing I did was to change the project preferences (Project > Properties > General) settings Startup Object to Sub Main. This was a given. Next I created a new class containing my Sub Main(). I could not get the program to start up in this manner.

After some googling I found that the Sub Main() needs to be placed inside a module in the following manner:

ModuleSub Main()        [...]End SubEnd Module

After that everything worked like a charm.