none
fill datagridview RRS feed

  • Question

  • hi

    i used this function to fill datagridview are this best way and fast way to fill 

    If there are large data rows

    I used the dispose dataadapter part because I used the fill a lot

     Public Sub filldatg( ByVal dg As DataGridView, ByVal ds As DataSet, ByVal tablename As String, ByVal qurey As String)
                    DataAdapter.Dispose()
            If con.State = ConnectionState.Closed Then
                con.Open()
            End If
            Using cmd As OleDbCommand = New OleDbCommand(qurey, con)
                DataAdapter = New OleDbDataAdapter(cmd)
                ds.Clear()
                DataAdapter.Fill(ds, tablename)
                dg.DataSource = ds
                dg.DataMember = tablename
                dg.ClearSelection()
                dg.CurrentCell = Nothing
            End Using
            con.Close()
           
        End Sub

    Tuesday, December 4, 2018 6:51 AM

Answers

  • Hi,

    What is your problem?You can use using.

     Public Sub filldatg( ByVal dg As DataGridView, ByVal ds As DataSet, ByVal tablename As String, ByVal qurey As String)
                    'DataAdapter.Dispose()
            If con.State = ConnectionState.Closed Then
                con.Open()
            End If
            Using cmd As OleDbCommand = New OleDbCommand(qurey, con)
               Using DataAdapter = New OleDbDataAdapter(cmd)
                ds.Clear()
                DataAdapter.Fill(ds, tablename)
                dg.DataSource = ds
                dg.DataMember = tablename
                dg.ClearSelection()
                dg.CurrentCell = Nothing
               End Using
            End Using
            con.Close()
           
        End Sub

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ahmeddc Tuesday, December 4, 2018 7:39 PM
    Tuesday, December 4, 2018 7:48 AM

All replies

  • Hi,

    What is your problem?You can use using.

     Public Sub filldatg( ByVal dg As DataGridView, ByVal ds As DataSet, ByVal tablename As String, ByVal qurey As String)
                    'DataAdapter.Dispose()
            If con.State = ConnectionState.Closed Then
                con.Open()
            End If
            Using cmd As OleDbCommand = New OleDbCommand(qurey, con)
               Using DataAdapter = New OleDbDataAdapter(cmd)
                ds.Clear()
                DataAdapter.Fill(ds, tablename)
                dg.DataSource = ds
                dg.DataMember = tablename
                dg.ClearSelection()
                dg.CurrentCell = Nothing
               End Using
            End Using
            con.Close()
           
        End Sub

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ahmeddc Tuesday, December 4, 2018 7:39 PM
    Tuesday, December 4, 2018 7:48 AM
  • Hi,

    What is your problem?You can use using.

     Public Sub filldatg( ByVal dg As DataGridView, ByVal ds As DataSet, ByVal tablename As String, ByVal qurey As String)
                    'DataAdapter.Dispose()
            If con.State = ConnectionState.Closed Then
                con.Open()
            End If
            Using cmd As OleDbCommand = New OleDbCommand(qurey, con)
               Using DataAdapter = New OleDbDataAdapter(cmd)
                ds.Clear()
                DataAdapter.Fill(ds, tablename)
                dg.DataSource = ds
                dg.DataMember = tablename
                dg.ClearSelection()
                dg.CurrentCell = Nothing
               End Using
            End Using
            con.Close()
           
        End Sub

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    What is your problem?You can use using.

    nothing !

    dataAdapter.AcceptChangesDuringFill = false dataAdapter.AcceptChangesDuringUpdate = false


    Is there a benefit of putting code before fill dataadapter in database?


    Tuesday, December 4, 2018 8:12 AM
  • Why you need to dispose DataAdapter every you finish fill DataSet?

    If you want to use for other fill you just need to change the query on DataAdapter.SelectCommand.CommandText

    Best Regard

    Xan To


    • Edited by Xan To Tuesday, December 4, 2018 8:13 AM
    Tuesday, December 4, 2018 8:13 AM
  • Why you need to dispose DataAdapter every you finish fill DataSet?

    If you want to use for other fill you just change the query on DataAdapter.SelectCommand.CommandText

    Best Regard

    Xan To

    There is some apprehension I have about the data and reassert it every time
    Tuesday, December 4, 2018 8:16 AM
  • hi

    i used this function to fill datagridview are this best way and fast way to fill 

    If there are large data rows

    I used the dispose dataadapter part because I used the fill a lot

     Public Sub filldatg( ByVal dg As DataGridView, ByVal ds As DataSet, ByVal tablename As String, ByVal qurey As String)
                    DataAdapter.Dispose()
            If con.State = ConnectionState.Closed Then
                con.Open()
            End If
            Using cmd As OleDbCommand = New OleDbCommand(qurey, con)
                DataAdapter = New OleDbDataAdapter(cmd)
                ds.Clear()
                DataAdapter.Fill(ds, tablename)
                dg.DataSource = ds
                dg.DataMember = tablename
                dg.ClearSelection()
                dg.CurrentCell = Nothing
            End Using
            con.Close()
           
        End Sub

    Hi,

    I think that you fill your grid view in the form load so you took about best way and fast way 

    You need to manage your query with "WHERE" condition to control the data that is coming from the database and never use "FormLoad OR FormShown" to bring all data of the table in this events ..... Imagine your table after one month or 3 months the data in your table increases and increases .... .So you have to be reasonable to bring data from your table to the gridview ("BY Filter") with date or ID or whatever you want this is an example to show you how to deal with this scenario

    Using conn AS New OleDbConnection(cs)
    
            conn.Open()
            Using cmd AS New OleDbCommand("SELECT (ProductCode)as [كود الصنف],
                                    (ProductName) as [اسم الصنف],
                                    (StockNo)as[الكمية بالباكية],
                                    (Category) as [اسم الباكية],
                                    (Price) as [سعر التكلفة],
                                    (SellPrice)as[سعر البيع],
                                    (StockDate)as[تاريخ دخول الصنف],(StockID)as[كود المخزون] 
                                    from Stock where Category = '" & cmbCategory.Text & "' order by ProductCode DESC", conn)
    
            Using myDataSet As New DataSet, myDA As New OleDbDataAdapter(cmd)
    
            myDA.Fill(myDataSet, "Stock")
    
            DataGridView1.DataSource = myDataSet.Tables("Stock").DefaultView
          End Using
       End Using
    End Using
    If you need to bring the whole table immediately and your table have many records (i.e 1000 or more than this number) you must use date filter or my preferable way it is so useful ("PAGINATION") here is the link of how to use pagination in VB.net  
     Hope it helps ..... if i understood you well

    Regards From Amr_Aly

    Tuesday, December 4, 2018 8:49 AM
  • I don't have any issue with DataAdapter although I don't dispose. You just need to dispose before close the form. You only need to pay attention to what is in the DataTable
    • Marked as answer by ahmeddc Tuesday, December 4, 2018 7:40 PM
    • Unmarked as answer by ahmeddc Tuesday, December 4, 2018 7:40 PM
    Tuesday, December 4, 2018 8:52 AM
  • Hello,

    Your best option when there is a lot of data be it rows or rows and columns is what Amr_Aly recommended, pagination. 


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by ahmeddc Tuesday, December 4, 2018 7:39 PM
    • Unmarked as answer by ahmeddc Tuesday, December 4, 2018 7:39 PM
    Tuesday, December 4, 2018 2:35 PM
    Moderator
  • hi

    i used this function to fill datagridview are this best way and fast way to fill 

    If there are large data rows

    I used the dispose dataadapter part because I used the fill a lot

     Public Sub filldatg( ByVal dg As DataGridView, ByVal ds As DataSet, ByVal tablename As String, ByVal qurey As String)
                    DataAdapter.Dispose()
            If con.State = ConnectionState.Closed Then
                con.Open()
            End If
            Using cmd As OleDbCommand = New OleDbCommand(qurey, con)
                DataAdapter = New OleDbDataAdapter(cmd)
                ds.Clear()
                DataAdapter.Fill(ds, tablename)
                dg.DataSource = ds
                dg.DataMember = tablename
                dg.ClearSelection()
                dg.CurrentCell = Nothing
            End Using
            con.Close()
           
        End Sub

    Hi,

    I think that you fill your grid view in the form load so you took about best way and fast way 

    You need to manage your query with "WHERE" condition to control the data that is coming from the database and never use "FormLoad OR FormShown" to bring all data of the table in this events ..... Imagine your table after one month or 3 months the data in your table increases and increases .... .So you have to be reasonable to bring data from your table to the gridview ("BY Filter") with date or ID or whatever you want this is an example to show you how to deal with this scenario

    Using conn AS New OleDbConnection(cs)
    
            conn.Open()
            Using cmd AS New OleDbCommand("SELECT (ProductCode)as [كود الصنف],
                                    (ProductName) as [اسم الصنف],
                                    (StockNo)as[الكمية بالباكية],
                                    (Category) as [اسم الباكية],
                                    (Price) as [سعر التكلفة],
                                    (SellPrice)as[سعر البيع],
                                    (StockDate)as[تاريخ دخول الصنف],(StockID)as[كود المخزون] 
                                    from Stock where Category = '" & cmbCategory.Text & "' order by ProductCode DESC", conn)
    
            Using myDataSet As New DataSet, myDA As New OleDbDataAdapter(cmd)
    
            myDA.Fill(myDataSet, "Stock")
    
            DataGridView1.DataSource = myDataSet.Tables("Stock").DefaultView
          End Using
       End Using
    End Using
    If you need to bring the whole table immediately and your table have many records (i.e 1000 or more than this number) you must use date filter or my preferable way it is so useful ("PAGINATION") here is the link of how to use pagination in VB.net  
     Hope it helps ..... if i understood you well

    Regards From Amr_Aly

    What about the width of columns ؟
    Tuesday, December 4, 2018 7:41 PM

  • What about the width of columns ؟

    Sorry i didn't understand , what does you mean by width of columns  ????????

    Slowness is coming from that every beginners load the whole table in the form load and didn't use filters

    "Pagination" used to bring the whole table also by dividing it to pages ...If you have 1000 records in your table and adjust the page size to 100 then the table will be display on 10 pages and so on 

     


    Regards From Amr_Aly

    Tuesday, December 4, 2018 8:07 PM

  • What about the width of columns ؟
    From my experience you must be careful with DataGridView.AutoSizeColumnsMode And AutoSizeRowsMode, because it will slowdown datagridview displaying data
    Wednesday, December 5, 2018 3:43 AM

  • What about the width of columns ؟

    Sorry i didn't understand , what does you mean by width of columns  ????????

    Slowness is coming from that every beginners load the whole table in the form load and didn't use filters

    "Pagination" used to bring the whole table also by dividing it to pages ...If you have 1000 records in your table and adjust the page size to 100 then the table will be display on 10 pages and so on 

     


    Regards From Amr_Aly

    Perhaps I misunderstood the intention of displaying the columns where I put the properties of a specific width of the column where

     DataGridView1.Columns(1).Width = 120

    Wednesday, December 5, 2018 5:39 AM

  • What about the width of columns ؟

    From my experience you must be careful with DataGridView.AutoSizeColumnsMode And AutoSizeRowsMode, because it will slowdown datagridview displaying data
    Thanks for the note
    Wednesday, December 5, 2018 5:40 AM