datagridview add new row

[new1]

i have this code to fill my datagrid with data from access database table

Dim CON As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & Application.StartupPath & "\database.mdb")
Dim sql As String = "select bot.trigger,bot.verse1 from bot"
Dim data As New OleDbDataAdapter(sql, CON)
        Dim ds As New DataSet
        ds.Clear()
        data.Fill(ds, "bot")
        DataGridView1.DataSource = ds
        DataGridView1.DataMember = "bot"

now i want to add new row to the datagrid with value from textbox
i get error that datagridview is bound
how can i do this?

[.paul.]

add a new row to your datatable + it'll appear in your dgv

[new1]

add a new row to your datatable + it'll appear in your dgv

that is the way i'm useing now and it's kinda slow because the table have too many rows some thing like 31102 row..so i want to add the rows directly to the datagrid and save it later to the table with button

[jmcilhinney]

If you don;t want the table bound to the grid then don't bind it. If you do bind it then it's bound, so you have to live with all that that entails.

Is it really sensible to have that many rows in a grid at one time anyway? Perhaps you should display less data at a time so it is less unruly for the user to work with.

[new1]

well how can i display the table in the datagrid without binding it?

[MarMan]

I never bind data controls. It gives for more control and flexibility. It is more work, but I think it is worth it, since you can copy and paste after you've created the first one (or better yet, write a code generator).

You need two things:

1. A collection
2. An object representing a row

You can use a generic collection, but I recommend a strongly typed collection. Here's an example: (Change EmailRecipientCollection to the collection name of your coice and EmailRecipient to your table name)

    Public Class EmailRecipientCollection
        'Strongly typed collection of rows from tblEmailRecipient

        Inherits CollectionBase


#Region "¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤ ReadOnly Properties ¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤"

        Public Shadows ReadOnly Property Count() As Integer

            Get
                Return MyBase.List.Count
            End Get

        End Property

#End Region


#Region "¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤ Properties ¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤"

        Default Public Property Item(ByVal index As Integer) As EmailRecipient

            Get
                If index < MyBase.List.Count Then
                    Return CType(MyBase.List.Item(index), EmailRecipient)
                Else
                    Return Nothing
                End If
            End Get

            Set(ByVal value As EmailRecipient)
                MyBase.List.Item(index) = value
            End Set

        End Property

        Default Public Property Item(ByVal strEmailTypeID As String, ByVal strEmployeeID As String, Optional ByVal bIgnoreCase As Boolean = False) As EmailRecipient

            Get

                Dim bFound As Boolean
                Dim EmailRecipient As EmailRecipient


                If bIgnoreCase Then
                    strEmailTypeID = UCase(strEmailTypeID)
                    strEmployeeID = UCase(strEmployeeID)
                End If
                EmailRecipient = Nothing
                For Each EmailRecipient In MyBase.List
                    If bIgnoreCase Then
                        If UCase(EmailRecipient.EmailTypeID) = strEmailTypeID And UCase(EmailRecipient.EmployeeID) = strEmployeeID Then
                            bFound = True
                            Exit For
                        End If
                    Else
                        If EmailRecipient.EmailTypeID = strEmailTypeID And EmailRecipient.EmployeeID = strEmployeeID Then
                            bFound = True
                            Exit For
                        End If
                    End If
                Next
                If bFound Then
                    Return EmailRecipient
                Else
                    Return Nothing
                End If
            End Get

            Set(ByVal value As EmailRecipient)

                Dim bFound As Boolean
                Dim EmailRecipient As EmailRecipient


                If bIgnoreCase Then
                    strEmailTypeID = UCase(strEmailTypeID)
                    strEmployeeID = UCase(strEmployeeID)
                End If
                EmailRecipient = Nothing
                For Each EmailRecipient In MyBase.List
                    If bIgnoreCase Then
                        If UCase(EmailRecipient.EmailTypeID) = strEmailTypeID And UCase(EmailRecipient.EmployeeID) = strEmployeeID Then
                            bFound = True
                            Exit For
                        End If
                    Else
                        If EmailRecipient.EmailTypeID = strEmailTypeID And EmailRecipient.EmployeeID = strEmployeeID Then
                            bFound = True
                            Exit For
                        End If
                    End If
                Next
                If bFound Then
                    EmailRecipient.CopyOf(value)
                End If
            End Set

        End Property

#End Region


#Region "¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤ Methods ¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤"

        Public Sub New()

            MyBase.new()

        End Sub

        Public Overrides Function ToString() As String

            Return "Collection of EmailRecipient objects, count = " & MyBase.List.Count.ToString

        End Function

        Public Function Add() As EmailRecipient

            Dim EmailRecipient As EmailRecipient


            EmailRecipient = New EmailRecipient
            MyBase.List.Add(EmailRecipient)
            Return EmailRecipient

        End Function

        Public Sub Add(ByVal EmailRecipient As EmailRecipient)

            MyBase.List.Add(EmailRecipient)

        End Sub

        Public Overloads Sub Clear()

            List.Clear()

        End Sub

        Public Function Contains(ByVal EmailRecipient As EmailRecipient) As Boolean

            Return List.Contains(EmailRecipient)

        End Function

        Public Sub CopyTo(ByVal Array() As EmailRecipient, ByVal intIndex As Integer)

            List.CopyTo(Array, intIndex)

        End Sub

        Public Sub Insert(ByVal intIndex As Integer, ByVal EmailRecipient As EmailRecipient)

            List.Insert(intIndex, EmailRecipient)

        End Sub

        Public Function IndexOf(ByVal EmailRecipient As EmailRecipient) As Integer

            Return List.IndexOf(EmailRecipient)

        End Function

        Public Sub Remove(ByVal EmailRecipient As EmailRecipient)

            MyBase.List.Remove(EmailRecipient)

        End Sub

        Public Sub Sort()

            MyBase.InnerList.Sort()

        End Sub

        Public Sub Sort(ByVal Comparer As IComparer)

            MyBase.InnerList.Sort(Comparer)

        End Sub


        Public Sub SaveList()

            Dim EmailRecipient As EmailRecipient


            For Each EmailRecipient In MyBase.List
                If EmailRecipient.Dirty Then
                    SaveEmailRecipient(EmailRecipient)
                End If
            Next

        End Sub

        Public Sub DeleteList()

            Dim EmailRecipient As EmailRecipient


            For Each EmailRecipient In MyBase.List
                DeleteEmailRecipient(EmailRecipient)
            Next

        End Sub

#End Region


    End Class 'EmailRecipientCollection

And here's an example of a row object:

        Public Class EmailRecipient
            'Represents a row from table tblEmailRecipient


#Region "¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤ Variables ¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤"

            'State variables
            Private mbDirty As Boolean
            Private mbExists As Boolean
            Private mbSetFromCode As Boolean
            'Field variables
'TODO Make local variables to represent columns in your table
'Example: Private mstrEmailTypeID As String
            'Holds the last data access error
            Private mstrLastError As String
            'If Readonly is true, then no Saves or Deletes can be done
            Private mbReadonly As Boolean

#End Region


#Region "¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤ ReadOnly Properties ¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤"

            Public ReadOnly Property Dirty() As Boolean Implements MainDataAccess.IDataAccess.Dirty

                Get
                    Return mbDirty
                End Get

            End Property

            Public ReadOnly Property ExistsInDB() As Boolean Implements MainDataAccess.IDataAccess.ExistsInDB

                Get
                    Return mbExists
                End Get

            End Property

            Public ReadOnly Property LastDBError() As String Implements MainDataAccess.IDataAccess.LastDBError

                Get
                    Return mstrLastError
                End Get

            End Property

            Public ReadOnly Property [Readonly]() As Boolean Implements MainDataAccess.IDataAccess.[Readonly]

                Get
                    Return mbReadonly
                End Get

            End Property

#End Region


#Region "¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤ Properties ¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤"

            Public Property SetFromCode() As Boolean
                Get
                    Return mbSetFromCode
                End Get
                Set(ByVal value As Boolean)
                    mbSetFromCode = value
                End Set

            End Property


'TODO Make properties representing all columns in your table, example:
            Public Property EmailTypeID() As String
                Get
                    Return mstrEmailTypeID
                End Get
                Set(ByVal value As String)
                    If Not mbReadonly Then
                        mstrEmailTypeID = value
                        If Not SetFromCode Then
                            mbDirty = True
                        End If
                    End If
                End Set

            End Property



#End Region


#Region "¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤ Methods ¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤×¤"

            Public Sub New()

                MyBase.new()

            End Sub

            Public Sub New(ByVal bExists As Boolean, ByVal bReadonly As Boolean)

                MyBase.new()
                mbExists = bExists
                mbReadonly = bReadonly

            End Sub

            Public Function Copy() As EmailRecipient

                Return CType(Me.MemberwiseClone(), EmailRecipient)

            End Function


#End Region

        End Class 'EmailRecipient

If you wish to implement it you just need a method to fill it and a method to fill the grid. It makes an excellent layer of an n-tier system.

[jmcilhinney]

I never bind data controls. It gives for more control and flexibility.

If all you need is a room, why build a whole house?

You can use a generic collection, but I recommend a strongly typed collection.

The CollectionBase class is outdated. From .NET 2.0 you should be using the Collection(Of T) class as a base for a strongly-typed collection. Unlike CollectionBase, because Collection(Of T) is generic, you don't have to implement members like Add and Item because it's already done for you. If you need anything out of the ordinary then this is all the code you need:

Public Class ThingCollection
    Inherits System.Collections.ObjectModel.Collection(Of Thing)
End Class

That's it, that's all.

[new1]

jmcilhinney i'm kinda beginner so tell me agin in simple way how can i adjust this code

Dim CON As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & Application.StartupPath & "\database.mdb")
Dim sql As String = "select bot.trigger,bot.verse1 from bot"
Dim data As New OleDbDataAdapter(sql, CON)
        Dim ds As New DataSet
        ds.Clear()
        data.Fill(ds, "bot")
        DataGridView1.DataSource = ds
        DataGridView1.DataMember = "bot"

to not give me datagridview is bound

[MarMan]

If all you need is a room, why build a whole house?

Cause then you can rent out rooms and live for free!

That is a better choice for new1 though. I didn't even know that it existed, thanks for pointing it out jmc (there are more exciting things that get my attention than collections).

I checked the Item method and saw that it takes an integer. Is there a method where you can use a Key (string) for a lookup field? My collection classes are automatically generated, so I can build many houses just by picking up a hammer, but I may rewrite the collection class to use the new object. I was wondering if I could use an already existing method or need to include my own.

[jmcilhinney]

jmcilhinney i'm kinda beginner so tell me agin in simple way how can i adjust this code

Dim CON As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & Application.StartupPath & "\database.mdb")
Dim sql As String = "select bot.trigger,bot.verse1 from bot"
Dim data As New OleDbDataAdapter(sql, CON)
        Dim ds As New DataSet
        ds.Clear()
        data.Fill(ds, "bot")
        DataGridView1.DataSource = ds
        DataGridView1.DataMember = "bot"

to not give me datagridview is bound

If you don't want the grid bound then don't bind it, i.e. don;t set the DataSource.

[new1]

if i didnt set the datasource will the table be displayed in the datagridview?

[jmcilhinney]

Cause then you can rent out rooms and live for free!

That is a better choice for new1 though. I didn't even know that it existed, thanks for pointing it out jmc (there are more exciting things that get my attention than collections).

I checked the Item method and saw that it takes an integer. Is there a method where you can use a Key (string) for a lookup field? My collection classes are automatically generated, so I can build many houses just by picking up a hammer, but I may rewrite the collection class to use the new object. I was wondering if I could use an already existing method or need to include my own.

If you want to be able to access the items in the collection by key then you need some type of keyed collection. You can either inherit Dictionary(Of TKey, TValue) or, if the keys are actually part of the items then you should inherit the KeyedCollection(Of TKey, TItem) class.

[MarMan]

To use the grid unbound requires these steps (won't be too easy for a beginner, but will make you a better coder)

1. Make two new classes
2. Use the code jmc gave you for one that is your collection (rename THING to what you wish)
3. Use the second block of code I gave you for the other (it will require editing)
4. Create local variables for your columns
5. Create properties for your columns

After you have done that all you need is a SQL statement and a loop. But see let's if you wish to have a go at it before we continue.

[jmcilhinney]

To use the grid unbound requires these steps (won't be too easy for a beginner, but will make you a better coder)

1. Make two new classes
2. Use the code jmc gave you for one that is your collection (rename THING to what you wish)
3. Use the second block of code I gave you for the other (it will require editing)
4. Create local variables for your columns
5. Create properties for your columns

After you have done that all you need is a SQL statement and a loop. But see let's if you wish to have a go at it before we continue.

There's no more need for a custom collection here than with a bound array. If the grid is going to be unbound then the data has to be transferred to and from the rows and cells manually, which can be done using a DataTable as easily as it can a custom collection. I'm not against custom collections but whether or not you're data-binding isn't really a factor in whether you use one or not.

[MarMan]

The custom collection provides many benefits that will be realized when it gets used. I am looking at the bigger picture here trying to help out new1. A custom object matching a row from a database table is extremely intuitive to use and self documents the code (easier to read) reducing maintenance costs and increasing developer productivity.

6 of one or half a dozen of another, there are many good ways to do something, I am merely providing one way. Too many choices can be confusing to a beginner.

[new1]

If you don't want the grid bound then don't bind it, i.e. don;t set the DataSource.

if i didnt set the datasource will the table be displayed in the datagridview?

[MarMan]

No. Setting the datasource tells the grid where to get its data from. Maybe if you explained EXACTLY, without leaving anything out, what you wanted to do we can come up with a better solution.

[new1]

ok marman her's is what i'm working on

i have program abot that send sentences to chat room
i have table with those sentences which appear in datagrid view
now when i want to add new respond or sentence i have to add it to the table first and reload the datagridview so that the new sentence appear in the datagridview and that take some time and very slow because the table have like 31102 rows

so i been thinking about after loading the datagridview from the table for the first time ..i can add the new sentence to the datagridview directly and save it from the datagridview to the table so that i wont have to reload the datagridview each time i add new sentence...

so when i tryie to make button that show me textbox where i can add the new sentence that i want to add it to the datagridview i get error datagridview is bound

i guess you get what i mean now..so tell me how can i do it

[MarMan]

Binding a grid means you want the grid to show whats in the table, it is BOUND to the table so you can't have a bound grid and add stuff that's not in the table as far as I now. I do not use bound controls because of things like that.

Well I see 4 options:

1. Let the grid reload each time and deal with the slowness. No good.
2. Create an unbound grid. We told you how to do it and you didn't like that one.
3. Don't get all the rows in the first place place, this will speed it up.
4. Use a second grid for new rows, i.e. have a bound grid for what exists and an unbound grid for newly added rows.

Unless anyone has any other ideas that's all I have.

[dolot]

ok marman her's is what i'm working on

now when i want to add new respond or sentence i have to add it to the table first and reload the datagridview so that the new sentence appear in the datagridview and that take some time and very slow because the table have like 31102 rows

so i been thinking about after loading the datagridview from the table for the first time ..i can add the new sentence to the datagridview directly and save it from the datagridview to the table so that i wont have to reload the datagridview each time i add new sentence...

so when i tryie to make button that show me textbox where i can add the new sentence that i want to add it to the datagridview i get error datagridview is bound

i guess you get what i mean now..so tell me how can i do it

You can make your datagridview editable so that you can add new records directly to the grid - and therefore to the bound table. There's no need to have a seperate textbox.

However, if you want to use a seperate textbox, then simply add the record to the underlying datatable in the dataset. Then the refresh performance shouldn't be too bad. Then you can commit the changes in the dataset to the underlying database table whenever you want.