none
how i add rows from first row to datagridview? RRS feed

  • Question

  • i have a datagridview which read a textfile, after the i add a column from database to the same datagridview

    when i succeed to do that i have a problem when i get data from db it shows after last row from textfile

    Thursday, April 18, 2019 2:58 PM

All replies

  •   dt.Columns.Add("Command")
            dt.Columns.Add("Article")
            dt.Columns.Add("Qte")
            dt.Columns.Add("PU")
    
            Dim ouvrir As New OpenFileDialog
            If ouvrir.ShowDialog = DialogResult.OK Then
                Dim sr As New StreamReader(ouvrir.FileName)
                While Not sr.EndOfStream
                    dt.Rows.Add(sr.ReadLine.Split(CChar(vbTab)))
                End While
    
    
                customersDataGridView.DataSource = dt
            End If
            cn.Open()
    
            Using cmd As New SqlCommand("select Designation from DetailReceptionFrs", cn)
                Dim da As SqlDataReader = cmd.ExecuteReader
    
                dt.Columns.Add("Designation")
    
                While da.Read
    
    
                    dt.Rows(0)("Designation") = da("Designation")
    
                End While
                customersDataGridView.Rows(0).Cells("Designation").Value = dt
    
            End Using

    Thursday, April 18, 2019 2:59 PM
  • Hi,
    I show you in another thread how to merge data. Where is the problem? You read data from the database and must detect in which row of the first DataTable you want write the new value. Please, describe this assignment.

    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    Thursday, April 18, 2019 3:26 PM
  • So, you cant find someone to provide a copy/paste solution over on vbforums, so you come here and try?

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Thursday, April 18, 2019 3:49 PM
  • Okay, if you are reading a file and then adding data from a database do it all in one shot. Here I read from the text file into a list of customer, get their phone number from SQL-Server then convert all into a DataTable.

    The connection is made from the NuGet package in my signature below.

    Imports System.ComponentModel
    Imports System.Data.SqlClient
    Imports System.IO
    Imports BaseConnectionLibrary.ConnectionClasses
    
    Public Class DataOperations
        Inherits SqlServerConnection
        Public Sub New()
            DatabaseServer = ".\SQLEXPRESS"
            DefaultCatalog = "NorthWindAzure3"
        End Sub
    
    
        Public Function GetData() As DataTable
            Dim fileData As List(Of Customer) = File.
                    ReadAllLines(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "TextFile1.txt")).
                    Select(
                        Function(lineData)
                            Dim company As New Customer
                            If Not String.IsNullOrWhiteSpace(lineData) Then
                                Dim lineArray = lineData.Split(","c)
                                company = New Customer With
                                    {
                                      .CustomerIdentifier = CInt(lineArray(0)),
                                      .CompanyName = lineArray(1),
                                      .ContactName = lineArray(2)
                                    }
                            End If
                            Return company
                        End Function).ToList()
    
    
            Dim selectStatement = "SELECT Phone FROM Customers WHERE (CustomerIdentifier = @Id)"
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn, .CommandText = selectStatement}
                    cmd.Parameters.Add("@id", SqlDbType.Int)
                    cn.Open()
    
                    For Each company As Customer In fileData
                        cmd.Parameters(0).Value = company.CustomerIdentifier
                        Dim reader = cmd.ExecuteScalar()
                        If reader IsNot Nothing Then
                            company.Phone = reader.ToString
                        End If
                        reader = Nothing
                    Next
                End Using
            End Using
    
            Return ConvertToDataTable(fileData)
    
        End Function
    
        Public Function ConvertToDataTable(Of T)(ByVal data As IList(Of T)) As DataTable
            Dim properties As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
            Dim table As New DataTable()
            For Each prop As PropertyDescriptor In properties
                table.Columns.Add(prop.Name,
                                  If(Nullable.GetUnderlyingType(prop.PropertyType), prop.PropertyType))
            Next prop
            For Each item As T In data
                Dim row As DataRow = table.NewRow()
                For Each prop As PropertyDescriptor In properties
                    row(prop.Name) = If(prop.GetValue(item), DBNull.Value)
                Next prop
                table.Rows.Add(row)
            Next item
            Return table
    
        End Function
    End Class
    

    Form code

    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim ops As New DataOperations
            Dim dt = ops.GetData()
            DataGridView1.DataSource = dt
        End Sub
    End Class

    File

    1,Alfreds Futterkiste's,Maria Anders 2,Ana Trujillo Emparedados y helados,Ana Trujillo 3,Antonio Moreno Taquería,Antonio Moreno 4,Around the Horn,Thomas Hardy 5,Berglunds snabbköp,Christina Berglund


    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

    Thursday, April 18, 2019 4:49 PM
    Moderator
  • Its bee difficult working with this lad, really the only thing he needs to to is iterate a DataReader inside stream while loop 

    Imports System.Data.SqlClient
    
    Public Class FormHous
        Private Sub FormHous_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'Stop adding rows to the datagridview!
            'Use a Datatable (for the 5th time!)
    
            Dim DT As New DataTable With {.TableName = "hous"}
            DT.Columns.Add("Col1", GetType(String)) 'txt column
            DT.Columns.Add("Col2", GetType(String)) 'txt column
            DT.Columns.Add("Col3", GetType(String)) 'txt column
            DT.Columns.Add("Col4", GetType(String)) 'txt column
            DT.Columns.Add("Col5", GetType(String)) 'txt column
    
            DT.Columns.Add("Col6", GetType(String)) 'sql column
    
            Dim sr As New IO.StreamReader(ouvrir.FileName)
    
            Using cn As New SqlConnection("Your COnn String")
                cn.Open()
    
                Using EXTReader As SqlDataReader = New SqlCommand("select numero from database", cn).ExecuteReader
                    While sr.Peek <> -1
                        EXTReader.Read()
                        Dim TxtVals() As String = sr.ReadLine.Split(CChar(vbTab))
                        Dim Nrow As DataRow = DT.NewRow
                        Nrow("Col1") = TxtVals(0)
                        Nrow("Col2") = TxtVals(1)
                        Nrow("Col3") = TxtVals(2)
                        Nrow("Col4") = TxtVals(3)
                        Nrow("Col5") = TxtVals(4)
    
                        Nrow("Col6") = EXTReader.GetString(EXTReader.GetOrdinal("ColumnName")) 'Column from SQL
    
                        DT.Rows.Add(Nrow)
                    End While
                End Using
                cn.Close()
            End Using
    
            DataGridView1.DataSource = DT
        End Sub
    End Class

    which I have finally provided here after pulling out a few hairs...


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi


    • Edited by Gtripodi Thursday, April 18, 2019 7:30 PM
    Thursday, April 18, 2019 7:29 PM
  • Okay, if you are reading a file and then adding data from a database do it all in one shot. Here I read from the text file into a list of customer, get their phone number from SQL-Server then convert all into a DataTable.

    The connection is made from the NuGet package in my signature below.

    Imports System.ComponentModel
    Imports System.Data.SqlClient
    Imports System.IO
    Imports BaseConnectionLibrary.ConnectionClasses
    
    Public Class DataOperations
        Inherits SqlServerConnection
        Public Sub New()
            DatabaseServer = ".\SQLEXPRESS"
            DefaultCatalog = "NorthWindAzure3"
        End Sub
    
    
        Public Function GetData() As DataTable
            Dim fileData As List(Of Customer) = File.
                    ReadAllLines(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "TextFile1.txt")).
                    Select(
                        Function(lineData)
                            Dim company As New Customer
                            If Not String.IsNullOrWhiteSpace(lineData) Then
                                Dim lineArray = lineData.Split(","c)
                                company = New Customer With
                                    {
                                      .CustomerIdentifier = CInt(lineArray(0)),
                                      .CompanyName = lineArray(1),
                                      .ContactName = lineArray(2)
                                    }
                            End If
                            Return company
                        End Function).ToList()
    
    
            Dim selectStatement = "SELECT Phone FROM Customers WHERE (CustomerIdentifier = @Id)"
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn, .CommandText = selectStatement}
                    cmd.Parameters.Add("@id", SqlDbType.Int)
                    cn.Open()
    
                    For Each company As Customer In fileData
                        cmd.Parameters(0).Value = company.CustomerIdentifier
                        Dim reader = cmd.ExecuteScalar()
                        If reader IsNot Nothing Then
                            company.Phone = reader.ToString
                        End If
                        reader = Nothing
                    Next
                End Using
            End Using
    
            Return ConvertToDataTable(fileData)
    
        End Function
    
        Public Function ConvertToDataTable(Of T)(ByVal data As IList(Of T)) As DataTable
            Dim properties As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
            Dim table As New DataTable()
            For Each prop As PropertyDescriptor In properties
                table.Columns.Add(prop.Name,
                                  If(Nullable.GetUnderlyingType(prop.PropertyType), prop.PropertyType))
            Next prop
            For Each item As T In data
                Dim row As DataRow = table.NewRow()
                For Each prop As PropertyDescriptor In properties
                    row(prop.Name) = If(prop.GetValue(item), DBNull.Value)
                Next prop
                table.Rows.Add(row)
            Next item
            Return table
    
        End Function
    End Class

    Form code

    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim ops As New DataOperations
            Dim dt = ops.GetData()
            DataGridView1.DataSource = dt
        End Sub
    End Class

    File

    1,Alfreds Futterkiste's,Maria Anders 2,Ana Trujillo Emparedados y helados,Ana Trujillo 3,Antonio Moreno Taquería,Antonio Moreno 4,Around the Horn,Thomas Hardy 5,Berglunds snabbköp,Christina Berglund


    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

    Thank you for help

    Dim fileData As List(Of Customer)

    i got error in Customer

    Friday, April 19, 2019 10:22 AM
  • Yes, you need the Customer class

    Public Class Customer
        Public Property CustomerIdentifier() As Integer
        Public Property CompanyName() As String
        Public Property ContactName() As String
        Public Property Phone() As String
        ''' <summary>
        ''' When viewing while debugging this shows up
        ''' </summary>
        ''' <returns></returns>
        Public Overrides Function ToString() As String
            Return CompanyName
        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

    Friday, April 19, 2019 10:46 AM
    Moderator