Solved

Bind Gridview to dataset using VB.NET

Posted on 2009-05-06
2
4,306 Views
Last Modified: 2013-11-08
I have a datagridview that I need to bind to a dataset that contains data from a Access database table.   The datagridview needs to allow the user to edit existing data as well as add new data.   When the user is done I need to have whatever changes are made to existing data as well as the new data saved back to the database.   What is the best approach to take to acomplish this?

I am able to populate the dataset and bind it to the grid.  My problem is save the changes back to the database.
0
Comment
Question by:Mike_Stevens
2 Comments
 
LVL 11

Expert Comment

by:srikanthreddyn143
ID: 24318322
0
 
LVL 6

Accepted Solution

by:
BALMUKUND KESHAV earned 500 total points
ID: 24334157
Here is an ASPX page retriving/editing/updating/deleting  data from mysql table using VB.net
Pl.make necessary correcitons at your end a/c to your environment and use :

<%@ Page Language=VB Debug=true %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="MySql.Data" %>
<%@ Import Namespace="MySql.Data.MysqlClient" %>

<script runat=server>
    Private strConnection As String = "server=192.168.1.44; user id=bmk; password=bmkpass; database=test; pooling=false;"
   
    Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)
        If Not IsPostBack Then
            BuildDataList()
        End If
    End Sub
   
    Sub Edit_Grid(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
        dgEmps.EditItemIndex = e.Item.ItemIndex
        BuildDataList()
    End Sub

    Sub CancelEdit(ByVal Sender As Object, ByVal E As DataGridCommandEventArgs)
        dgEmps.EditItemIndex = -1
        BuildDataList()
    End Sub
   
Sub Update_Grid(ByVal Sender as Object, ByVal E as DataGridCommandEventArgs)
    Dim TheID as String
    Dim LastName as String
    Dim FirstName as String
    TheID = E.Item.Cells(2).Text
    LastName = CType(e.Item.Cells(0).Controls(0), TextBox).Text
    FirstName = CType(e.Item.Cells(1).Controls(0), TextBox).Text
        Dim DBConn As MySqlConnection
        Dim DBUpdate As New MySqlCommand
        DBConn = New MySqlConnection(strConnection)
    DBUpdate.CommandText = "Update Employee " _
        & "Set LastName = '" & LastName &"', " _
        & "FirstName = '" & FirstName & "' " _
        & "Where ID = " & TheID
       DBUpdate.Connection = DBConn
        DBUpdate.Connection.Open()
        DBUpdate.ExecuteNonQuery()
    DBConn.Close
    dgEmps.EditItemIndex = -1
    BuildDataList
End Sub
    Sub Delete_Grid(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
        dgEmps.EditItemIndex = e.Item.ItemIndex
        Dim TheID As String
        TheID = e.Item.Cells(2).Text
        Dim DBConn As MySqlConnection
        Dim DBdelete As New MySqlCommand
        DBConn = New MySqlConnection(strConnection)
        DBdelete.CommandText = "delete from Employee " _
            & "Where ID = " & TheID
        DBdelete.Connection = DBConn
        DBdelete.Connection.Open()
        DBdelete.ExecuteNonQuery()
        DBConn.Close()
        dgEmps.EditItemIndex = -1
        BuildDataList()
    End Sub

    Sub CommandAdd_Click(ByVal Sender As Object, ByVal E As CommandEventArgs)
        Dim DBConn As MySqlConnection
        Dim DBAdd As New MySqlCommand
        DBConn = New MySqlConnection(strConnection)
        DBAdd.CommandText = "Insert Into Employee (LastName) " _
        & "values ('')"
        DBAdd.Connection = DBConn
        DBAdd.Connection.Open()
        DBAdd.ExecuteNonQuery()
        DBConn.Close()
        dgEmps.EditItemIndex = 0
        BuildDataList()
    End Sub
Sub BuildDataList ()
        Dim DBConn As MySqlConnection
        Dim DBCommand As MySqlDataAdapter
        Dim DSPageData as New DataSet
        DBConn = New MySqlConnection(strConnection)
        DBCommand = New MySqlDataAdapter _
            ("Select * From Employee " _
            & "Order By LastName, FirstName", DBConn)
        DBCommand.Fill(DSPageData, _
            "Employee")
        dgEmps.DataSource = _
            DSPageData.Tables("Employee").DefaultView
        dgEmps.DataBind()
End Sub
</SCRIPT>
<HTML>
<HEAD>
<TITLE>Adding Rows to a DataGrid Control</TITLE>
</HEAD>
<Body LEFTMARGIN="40">
<form runat="server">
<BR><BR>
<asp:Label
    id="lblMessage"
    runat="server"
/>
<BR><BR>
<asp:datagrid
    id="dgEmps"
    runat="server"
    autogeneratecolumns="false"
    oneditcommand="Edit_Grid"
    oncancelcommand="Canceledit"
    onupdatecommand="Update_Grid"
    ondeletecommand="Delete_Grid"
    >
    <columns>
        <asp:boundcolumn
            HeaderText="Last Name"
            DataField="LastName"
        />
        <asp:boundcolumn
            HeaderText="First Name"
            DataField="FirstName"
        />
        <asp:boundcolumn
            datafield="ID"
            visible=False
            readonly=true
        />
        <asp:editcommandcolumn
             edittext="Edit"
             updatetext="Update"
             canceltext="Cancel"
             itemstyle-wrap="false"
             headertext="Edit"
             headerStyle-wrap="false"
        />
        <asp:ButtonColumn Text="Delete" CommandName="Delete"/>
    </columns>
</asp:datagrid>
<BR><BR>
  <asp:LinkButton
    id="butAdd"
    text="Add"
    commandname="Add"
    oncommand="CommandAdd_Click"
    runat="server"
  />
</form>
</BODY>
</HTML>


Bm Keshav

0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
error in For & Next statements 4 25
C# class library debugging - Breakpoint will not hit. 8 22
Why use this lambda? 12 41
Why having to press tab key twice? 4 19
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

821 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