Trouble with DataTable primary key

[emaduddeen]

Hi Everyone,

Can you look at this code?

No errors are reported but the DataTable primary key never gets set and I can't figure out what additional code is missing.

This query has the data and the primary key column is FormulaName.

    Private Sub PopulateTheDataGridWithData()

        ' SQL for the DataGrid.
        '----------------------
        strSqlStatement = _
            "SELECT FormulaName AS [Formula Name], " & _
                   "Format(Actual, ""Percent"") AS [Actual], " & _
                   "Format(Goal, ""Percent"") AS [Goal], " & _
                   "Format(GuaranteedAnalysis, ""Percent"") AS [Guaranteed Analysis], " & _
                   "Format(TheoreticalAnalysis, ""Percent"") AS [Theoretical Analysis], " & _
                   "ChickenIngredient AS [Chicken Ingredient], " & _
                   "FishIngredient AS [Fish Ingredient], " & _
                   "PotatoIngredient AS [Potato Ingredient] " & _
              "FROM Formulas "

        ' Load the data into the grid with this query.
        '---------------------------------------------
        objFormulasDataTable = objClassDatabaseObjects.GetDataTable(strSqlStatement, _
                                                                       "Formulas Data", _
                                                                       "FormulaName", Nothing)
        LightGridFormulas.DataSource = objFormulasDataTable
    End Sub

This is the function objClassDatabaseObjects.GetDataTable that is called:

    Public Function GetDataTable(ByVal pSqlQuery As String, _
                                 ByVal pTableName As String, _
                                 ByVal pPrimaryKeyColumn1 As String, _
                                 ByVal pPrimaryKeyColumn2 As String) As DataTable

        ' Create a DataTable object to hold data from the SQL query.
        '-----------------------------------------------------------
        objDataTable = New DataTable(pTableName)

        ' Set for a composite primary key.
        '---------------------------------------
        Dim objKeyValueDataColumns(2) As DataColumn
        Try
            ' Create the connection object to use an SQL query and open it.
            '--------------------------------------------------------------
            objConnection = GetNutritionDataConnection()
            objConnection.Open()

            ' Create a DataAdapter object for the DataTable.
            '-----------------------------------------------
            objDataAdapter = New OleDbDataAdapter(pSqlQuery, objConnection)

            ' Load the DataAdapter with the data into the DataTable.
            '-------------------------------------------------------
            objDataAdapter.Fill(objDataTable)

            ' Set up the primary key for the data table.
            '-------------------------------------------
            objKeyValueDataColumns(0) = objDataTable.Columns(pPrimaryKeyColumn1)

            If pPrimaryKeyColumn2 <> Nothing Then
                objKeyValueDataColumns(1) = objDataTable.Columns(pPrimaryKeyColumn2)
            End If

            objDataTable.PrimaryKey = objKeyValueDataColumns

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        Finally
            ' Close the connection if it's currently open.
            '---------------------------------------------
            If objConnection IsNot Nothing Then
                objConnection.Close()
            End If
        End Try

        Return objDataTable
    End Function

Included are screen shots that shows the name of the column and nothing in the array objKeyValueDataColumns(0) that should hold the primary key column.

Thanks.

Truly,
Emad

 

[emaduddeen]

I found out what was messing it up.

If you use a column alias you have to use that alias in the call to the function like this:

        ' Load the data into the grid with this query.
        '---------------------------------------------
        objFormulasDataTable = objClassDatabaseObjects.GetDataTable(strSqlStatement, _
                                                                       "Formulas Data", _
                                                                       "Formula Name", Nothing)

Now it works.

Truly,
Emad