We help IT Professionals succeed at work.

Basic DataGrid Example

jclothier
jclothier asked
on
Medium Priority
299 Views
Last Modified: 2010-04-23
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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
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

Author

Commented:
That has certainly populated the grid but when I exit the changes are not saved
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
Save changes:

   adapter.Update(table)

Bob

Author

Commented:
nope that doesn't seem to work. I put adapter.Update(table) in the click event for my save button and nothing happened.

Commented:
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

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

Roger
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 :)

Author

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.

Author

Commented:
Sorry meant to say award not avoid ;-)
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

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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Absolutely Perfect! Thanks CubixSoftware
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.