Add Column to DataTable before exporting to Excel file

Question

I have a DataTable that is built like this:

Dim dt As New DataTable()
dt = SqlHelper.ExecuteDataset(connString, "storedProcedure", Session("Member"), DBNull.Value, DBNull.Value).Tables(0)

Then it is converted to a DataView and exported to Excel like this:

Dim dv As New DataView()
dv = dt.DefaultView
Export.CreateExcelFile(dv, strFileName)

I want to add another column and fill it with data before I export to Excel. Here's what I'm doing now to add the column:

Dim dc As New DataColumn("New Column", Type.GetType("System.String"))
dc.DefaultValue = "N"
dt.Columns.Add(dc)

Then to populate it:

For Each row As DataRow In dt.Rows
    Dim uID As Integer = Convert.ToInt32(dt.Columns(0).ColumnName)
    Dim pID As String = dt.Columns(0).ColumnName
    Dim qry As String = "SELECT * FROM [MyTable] WHERE [UserID] = " & uID & " AND [PassID] = '" & pID & "'"
    Dim myCommand As SqlCommand
    Dim myConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("connString"))

    myConn.Open()
    myCommand = New SqlCommand(qry, myConn)
    Dim reader As SqlDataReader = myCommand.ExecuteReader()

    If reader.Read() Then
        row.Item("New Column") = "Y"
    Else
        row.Item("New Column") = "N"
    End If
Next row

But I get a "System.FormatException: Input string was not in a correct format." error when I run the app. It doesn't seem to like these lines:

Dim uID As Integer = Convert.ToInt32(dt.Columns(0).ColumnName)
Dim pID As String = dt.Columns(0).ColumnName

I think I have more than one issue here because even if I comment out the loop that fills the data in, the column I created doesn't show up in the Excel file. Any help would be much appreciated. Thanks!

---

EDIT:

Okay, I was grabbing the column name instead of the actual data in the column... because I'm an idiot. The new column still doesn't show up in the exported Excel file. Here's the updated code:

    Dim dt As New DataTable()
    dt = SqlHelper.ExecuteDataset(connString, "storedProcedure", Session("Member"), DBNull.Value, DBNull.Value).Tables(0)

    Dim dc As New DataColumn("New Column", Type.GetType("System.String"))
    dc.DefaultValue = "N"
    dt.Columns.Add(dc)

    'set the values of the new column based on info pulled from db
    Dim myCommand As SqlCommand
    Dim myConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("connString"))
    Dim reader As SqlDataReader

    For Each row As DataRow In dt.Rows
        Dim uID As Integer = Convert.ToInt32(row.Item(0))
        Dim pID As String = row.Item(2).ToString
        Dim qry As String = "SELECT * FROM [MyTable] WHERE [UserID] = " & uID & " AND [PassID] = '" & pID & "'"

        myConn.Open()
        myCommand = New SqlCommand(qry, myConn)
        reader = myCommand.ExecuteReader()

        If reader.Read() Then
            row.Item("New Column") = "Y"
        Else
            row.Item("New Column") = "N"
        End If

        myConn.Close()
    Next row

    dt.AcceptChanges()

    Dim dv As New DataView()
    dv = dt.DefaultView
    Export.CreateExcelFile(dv, strFileName)

Answer 1

The issue was in my CreateExcelFile() method. I inherited the app I'm modifying and assumed the method dynamically read the data in the DataTable and built the spreadsheet. In reality, it was searching the DataTable for specific values and ignored everything else.

2 lines of code later, I'm done.