[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Basic DataGrid Example

Posted on 2006-03-20
11
Medium Priority
?
255 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
0
Comment
Question by:jclothier
  • 5
  • 2
  • 2
  • +1
11 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 16237303
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
 

Author Comment

by:jclothier
ID: 16239963
That has certainly populated the grid but when I exit the changes are not saved
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 16240297
Save changes:

   adapter.Update(table)

Bob

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:jclothier
ID: 16240545
nope that doesn't seem to work. I put adapter.Update(table) in the click event for my save button and nothing happened.
0
 
LVL 34

Expert Comment

by:Sancler
ID: 16246491
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
 
LVL 34

Expert Comment

by:Sancler
ID: 16246501
And I should have added that the commandbuilder will only work properly if the table has a PrimaryKey

Roger
0
 
LVL 6

Expert Comment

by:cubixSoftware
ID: 16246566
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
 

Author Comment

by:jclothier
ID: 16248567
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
 

Author Comment

by:jclothier
ID: 16248582
Sorry meant to say award not avoid ;-)
0
 
LVL 6

Accepted Solution

by:
cubixSoftware earned 2000 total points
ID: 16256015
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
 

Author Comment

by:jclothier
ID: 16256048
Absolutely Perfect! Thanks CubixSoftware
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question