Basic DataGrid Example

HI

I have just discovered the DataGrid control and I could do with some help.

I need the complete code that does the following:

Retrieves the data from one simple table in an Access database and displays it in the datagrid. I then want to be able to edit an item in the grid or add a new row or two. I then want to click on a Save button and these changes to be saved back to the Access database.

500 points and an A rating for the person that can supply the complete code for this that I can get working.

Thanks in advance
jclothierAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
Dim table As New DataTable
Dim adapter As New OleDbDataAdapter("<command text goes here>", "<connection string goes here")
adapter.Fill(table)
Me.DataGrid1.DataSource = table

Bob
jclothierAuthor Commented:
That has certainly populated the grid but when I exit the changes are not saved
Bob LearnedCommented:
Save changes:

   adapter.Update(table)

Bob

OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

jclothierAuthor Commented:
nope that doesn't seem to work. I put adapter.Update(table) in the click event for my save button and nothing happened.
SanclerCommented:
1)   adapter will need to be declared with scope that covers both the .Fill and the .Update.
2)   Somewhere after .Fill but before .Update you will need

            Dim cb As New OleDbCommandBuilder(adapter)

3)   Make sure that, when you've edited one row in the datagrid, its changes get back to the datatable by clicking on a different row before trying the .Update.

Roger

SanclerCommented:
And I should have added that the commandbuilder will only work properly if the table has a PrimaryKey

Roger
cubixSoftwareCommented:
You can try the following....make sure that the DataSet is in overall scope.

================================================

Imports System.Data
Imports System.Data.OleDb


Public Class Form1
    Inherits System.Windows.Forms.Form
    ' create a connection string

    Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Northwind.mdb"
    Dim myConnection As OleDbConnection = New OleDbConnection
    ' create a new dataset
    Dim ds As DataSet = New DataSet


   ' The form load will populate the datagrid with data from Access table

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim da As New OleDbDataAdapter
        Dim selectCmd As New OleDbCommand

        myConnection.ConnectionString = connString

        ' define the select command
        With selectCmd
            .Connection = myConnection
            .CommandText = "Select * From Customers"
            .CommandType = CommandType.Text
        End With

        ' fill dataset
        With da
            .SelectCommand = selectCmd
            .Fill(ds, "Customers")
        End With

        ' display in the grid
        DataGrid1.DataSource = ds.Tables("Customers")

    End Sub

    ' the button1 click will take the grid information and post back to the database

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim da As New OleDbDataAdapter
        Dim updCmd As New OleDbCommand

        ' create update command - example here only updates the company name
        With updCmd
            .Connection = myConnection
            .CommandText = "Update Customers Set CompanyName = @CompanyName Where CustomerID = @CustomerID"
            .CommandType = CommandType.Text
            .Parameters.Add(New OleDbParameter("@CompanyName", OleDbType.Char, 40, "CompanyName"))
            .Parameters.Add(New OleDbParameter("@CustomerID", OleDbType.Char, 5, ParameterDirection.Input, False, CType(0, System.Byte), CType(0, System.Byte), "CustomerID", DataRowVersion.Original, Nothing))
        End With

        ' need to now do an insert command and a delete command if you are adding and deleting data

        ' update the database
        With da
            .UpdateCommand = updCmd  ' specifiy what update command to use
            .Update(ds, "Customers")
        End With

        Me.Close

    End Sub

===========================================

HTH :)
jclothierAuthor Commented:
Thanks cubixSoftware

That seems to work quite nicely. Can you provide me with the code for adding a new row (by the way my ACcess database uses Autonumber for its primary key field) and then I will avoid you the points.
jclothierAuthor Commented:
Sorry meant to say award not avoid ;-)
cubixSoftwareCommented:
I have amended the procedure to also do an insert. I have changed my version of the Customers table to include an Autonumber field but as you can see within the insert statement I make no reference to it, therefore the database will simply assign and return the next number.

Now that you have discovered the datagrid, you will want to start formatting the display etc. Here is a link to an excellent FAQ webpage that I use as a reference myself all the time:  

http://www.syncfusion.com/FAQ/WindowsForms/FAQ_c44c.aspx#Top

============================================
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim da As New OleDbDataAdapter
        Dim updCmd As New OleDbCommand
        Dim insCmd As New OleDbCommand


        ' create update command - example here only updates the company name
        With updCmd
            .Connection = myConnection
            .CommandText = "Update Customers Set CompanyName = @CompanyName Where CustomerID = @CustomerID"
            .CommandType = CommandType.Text
            .Parameters.Add(New OleDbParameter("@CompanyName", OleDbType.Char, 40, "CompanyName"))
            .Parameters.Add(New OleDbParameter("@CustomerID", OleDbType.Char, 5, ParameterDirection.Input, False, CType(0, System.Byte), CType(0, System.Byte), "CustomerID", DataRowVersion.Original, Nothing))
        End With

        ' create insert command
        With insCmd
            .Connection = myConnection
            .CommandText = "Insert Into Customers (CustomerID, CompanyName, ContactName, ContactTitle, Address, " & _
                                            "City, Region, PostalCode, Country, Phone, Fax) " & _
                                      "VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, " & _
                                                "@City, @Region, @PostalCode, @Country, @Phone, @Fax) "
            .CommandType = CommandType.Text
            .Parameters.Add(New OleDbParameter("@CustomerID", OleDbType.Char, 5, "CustomerID"))
            .Parameters.Add(New OleDbParameter("@CompanyName", OleDbType.Char, 40, "CompanyName"))
            .Parameters.Add(New OleDbParameter("@ContactName", OleDbType.Char, 30, "ContactName"))
            .Parameters.Add(New OleDbParameter("@ContactTitle", OleDbType.Char, 30, "ContactTitle"))
            .Parameters.Add(New OleDbParameter("@Address", OleDbType.Char, 60, "Address"))
            .Parameters.Add(New OleDbParameter("@City", OleDbType.Char, 15, "City"))
            .Parameters.Add(New OleDbParameter("@Region", OleDbType.Char, 15, "Region"))
            .Parameters.Add(New OleDbParameter("@PostalCode", OleDbType.Char, 10, "PostalCode"))
            .Parameters.Add(New OleDbParameter("@Country", OleDbType.Char, 15, "Country"))
            .Parameters.Add(New OleDbParameter("@Phone", OleDbType.Char, 24, "Phone"))
            .Parameters.Add(New OleDbParameter("@Fax", OleDbType.Char, 24, "Fax"))
        End With

        ' need to now do a delete command if you want to delete records

        ' update the database
        With da
            .UpdateCommand = updCmd  ' specify what update command to use
            .InsertCommand = insCmd     ' specify what insert command to use
            .Update(ds, "Customers")
        End With

        Me.Close()

    End Sub

========================================================

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jclothierAuthor Commented:
Absolutely Perfect! Thanks CubixSoftware
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.