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?
 
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

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

0
 
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
0
 
jclothierAuthor Commented:
That has certainly populated the grid but when I exit the changes are not saved
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Bob LearnedCommented:
Save changes:

   adapter.Update(table)

Bob

0
 
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.
0
 
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

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

Roger
0
 
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 :)
0
 
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.
0
 
jclothierAuthor Commented:
Sorry meant to say award not avoid ;-)
0
 
jclothierAuthor Commented:
Absolutely Perfect! Thanks CubixSoftware
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.