One day in your life,
You’ll remember the love you found here.
You’ll remember me somehow,
Though you don’t need me now.
I will stay in your heart,
And when things fall apart,
You’ll remember one day…

-Michael Jackson

There are many ways to export data from SQL Server to Excel but I think the one way to do it by real simple coding is to export the data as an XML file and then opening it in Excel.

First we fill the data in a DataSet and then export that data to an XML file. Although the file is an XML one, I give it an extension of ‘.xls’ so that Excel opens it by default and then it can be saved it as the Excel format in some other sheet.

Dim con as SqlConnection
con = New SqlConnection(”Data Source=SERVER; Initial Catalog=dbTest;uid=sa;pwd=123″)
Dim cmd As SqlCommand = New SqlCommand
con.Open()
cmd.CommandText = “spExportData”
cmd.CommandType = CommandType.StoredProcedure

cmd.Connection = con
Dim ds As New DataSet
Dim da As New SqlDataAdapter
da.SelectCommand = cmd
da.Fill(ds)
Dim savefileName As String = Application.StartupPath & “\ExportedFile.xls”
ds.WriteXml(savefileName)
con.Close()
cmd.Dispose()
da.Dispose()

If in a table, some columns occasionally contain null values, then while trying to retrieve the values, the following error is shown:

Conversion from type ‘DBNull’ to type ‘String’ is not valid.

To solve this problem, we can check if the Datareader has a null value for the column, and if yes, then just getting the TextBox to show an empty string.

If DBNull.Value.Equals(dataReader1(“Column_Name”)) Then

TextBox.Text = “”

Else

TextBox.Text = dataReader1(“Column_Name”)

End If

Chris Connell writes about what separates software engineering form computer science. Quoting:

Formal software engineering processes, such as cleanroom engineering, are gradually finding rigorous, provable methods for software development. They are raising the bright line to subsume previously squishy software engineering topics.

Microsoft’s developing of a gaming system which takes motion sensing to next level.

Google wave: Hands On

Top 10 everything of 2008, by Time Magazine.