none
how to set a datatable ordinal ? RRS feed

  • Question

  • Hi members

    please help

    when i import from excel file i have 4 column filled a datatable after that

    i added a new column to the datatable i name it "designation"

    that column is filled from database

     DT.Load(cmd789.ExecuteReader())
    
                                DT.Columns("F1").ColumnName = "Command"
                            DT.Columns("F2").ColumnName = "Article"
    
    
                        DT.Columns("F3").ColumnName = "Qte"
                            DT.Columns("F4").ColumnName = "PU"
                        DT.Columns.Add("Désignation", GetType(String))
      For n = 0 To DT.Rows.Count - 1
    
                                If cn.State = ConnectionState.Open Then
                                        cn.Close()
                                    End If
                                    cn.Open()
                                Dim cmd23 As New SqlCommand("select AR_Design from F_ARTICLE where ar_ref='" & Trim(DT.Rows(n).Item(1).ToString) & "'", cn)
                                Dim lbn As SqlDataReader = (cmd23.ExecuteReader)
                                    lbn.Read()
                                    If lbn.HasRows Then
    
                                    DT.Rows(n).Item("Désignation") = lbn(0)
                                    Else
                                        MessageBox.Show("Article non trouvé :" & DT.Rows(n).Item(1).ToString & " BON COMMANDE : " & DT.Rows(n).Item(0).ToString)
                                        articleExiste = True
                                    End If
    
                                Next
    
    
    
                            DataGridView4.RowHeadersWidth = 53
                            DT.Columns("Désignation").SetOrdinal(2)
                            DataGridView4.DataSource = DT

    it works fine in 1st attempt but when i reimport the excel file and generate the new column "designation" the ordinal set not work it goes to 5th column

    and i want it to be the 2nd

    please how i resolve it

    Wednesday, February 12, 2020 10:10 AM

Answers

  • You can define the DataColumns then populate, also not you can alias column names in Excel e.g. F1 As FirstName.

    Public Class ExcelOperations
        Private mException As Boolean = False
        Public ReadOnly Property IsSuccessful() As Boolean
            Get
                Return mException = False
            End Get
        End Property
        Private Function CreateTable() As DataTable
    
            Dim dt As New DataTable
    
            dt.Columns.Add(New DataColumn() With {.ColumnName = "FirstName", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "MiddleName", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "Designation", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "LastName", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "Street", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "City", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "State", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "Postal", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "EmailAddress", .DataType = GetType(String)})
    
            Return dt
    
        End Function
        Public Function Read(fileName As String, sheetName As String) As DataTable
            Dim dt = CreateTable()
    
            Using cn As New OleDb.OleDbConnection With
                {
                .ConnectionString =
                    "Provider=Microsoft.ACE.OLEDB.12.0;" &
                    $"Data Source={fileName};" &
                    "Extended Properties=""Excel 12.0;IMEX=1;HDR=No;"""}
    
                Using cmd As New OleDb.OleDbCommand With
                    {
                    .CommandText =
                        "SELECT F1 As FirstName, F2 As MiddleName, F3 As LastName, " &
                        "F4 As Street, F5 As City, F6 As State, F7 As Postal, " &
                        $"F8 As EmailAddress FROM [{sheetName}]",
                    .Connection = cn}
    
                    Try
                        cn.Open()
                        dt.Load(cmd.ExecuteReader)
                        mException = False
                    Catch ex As Exception
                        mException = True
                    End Try
    
                End Using
            End Using
    
            Return dt
    
        End Function
    
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Houssem12 Wednesday, February 12, 2020 3:06 PM
    Wednesday, February 12, 2020 2:21 PM
    Moderator

All replies

  • You can define the DataColumns then populate, also not you can alias column names in Excel e.g. F1 As FirstName.

    Public Class ExcelOperations
        Private mException As Boolean = False
        Public ReadOnly Property IsSuccessful() As Boolean
            Get
                Return mException = False
            End Get
        End Property
        Private Function CreateTable() As DataTable
    
            Dim dt As New DataTable
    
            dt.Columns.Add(New DataColumn() With {.ColumnName = "FirstName", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "MiddleName", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "Designation", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "LastName", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "Street", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "City", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "State", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "Postal", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "EmailAddress", .DataType = GetType(String)})
    
            Return dt
    
        End Function
        Public Function Read(fileName As String, sheetName As String) As DataTable
            Dim dt = CreateTable()
    
            Using cn As New OleDb.OleDbConnection With
                {
                .ConnectionString =
                    "Provider=Microsoft.ACE.OLEDB.12.0;" &
                    $"Data Source={fileName};" &
                    "Extended Properties=""Excel 12.0;IMEX=1;HDR=No;"""}
    
                Using cmd As New OleDb.OleDbCommand With
                    {
                    .CommandText =
                        "SELECT F1 As FirstName, F2 As MiddleName, F3 As LastName, " &
                        "F4 As Street, F5 As City, F6 As State, F7 As Postal, " &
                        $"F8 As EmailAddress FROM [{sheetName}]",
                    .Connection = cn}
    
                    Try
                        cn.Open()
                        dt.Load(cmd.ExecuteReader)
                        mException = False
                    Catch ex As Exception
                        mException = True
                    End Try
    
                End Using
            End Using
    
            Return dt
    
        End Function
    
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Houssem12 Wednesday, February 12, 2020 3:06 PM
    Wednesday, February 12, 2020 2:21 PM
    Moderator
  • yap it work fine thank you a lot
    Wednesday, February 12, 2020 3:06 PM