Datagridview and ms access problems

[Ruchi224]

Hello..

I am using MS Access 2003 and vb.net to develop a windows application of inventory management.

I have used datagridview in the form and using FillBytoolstrip option, can filter data using type.

But i have no idea as to how can i update the database to reflect the latest changes in the gridview.

Tried searching but could not find proper and organised answer.

Could you please help me out with this ??

following is the code I have used.

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

Me.PartnoTableAdapter.Fill(Me.HemDatabase1DataSet3.partno)

End Sub

Private Sub FillByToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillByToolStripButton.Click
        Try
            Me.PartnoTableAdapter.FillBy(Me.HemDatabase1DataSet3.partno, TypeToolStripTextBox.Text)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try

[Phasma]

Ruchi224,

You will need to use a DataSet, BindingSource, OledbDataAdapter with a SelectCommand, and OledbCommandBuilder for the DataAdapter. Then use the OledbDataAdapter Update Method to reflect your changes back to the datatable in the MSACCESS database. If you fill the dataset using the dataAdapter and set the BindingSource's DataSource to the DataSet's Table then the DataGridView's DataSource to the BindingSource You will get your desired result. The DataTable of the MSACCESS database has to have a Primary Key or this will not work.

I have code for this using your toolstrip fill by TextBox criteria if you need it.

[Ruchi224]

I would greatly appreciate if you could help me with the code, please !!

Thank you so much !!

[Phasma]

This is quick and dirty, but it shows how to use all that i mentioned in my precious post. I have a table in the database of all the file extensions registered on my computer. That is what i am using for this example. The jpg attached to this post shows that you type a extension in the toolstrip textbox click the button on the toolstrip and it displays the result in the DataGridView. If you edit the result the changes are reflected to the table ijn the DataBase. Hope it helps

Imports System.Data
Imports System.Data.OleDb

Public Class Form1 Dim wrkDir As String = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.Location())
  Dim da As New OleDbDataAdapter
  Dim ds As New DataSet
  Dim bs As New BindingSource
  Dim edit As Boolean
  'Coupon_Tracker.mdb
  Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                  "Data Source=" & wrkDir & "\Coupon_Tracker.mdb")


  ''' <summary>
  ''' Update The DataBase
  ''' </summary>
  ''' <param name="sender"></param>
  ''' <param name="e"></param>
  ''' <remarks></remarks>
  Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
    If edit Then
      da.Update(ds, "EXTENSIONS")
      edit = False
    End If

  End Sub

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

    'Set the dataGridView's DataSource
    dgv1.DataSource = bs
  End Sub

  ''' <summary>
  ''' ToolStripButton
  ''' </summary>
  ''' <param name="sender"></param>
  ''' <param name="e"></param>
  ''' <remarks></remarks>
  Private Sub tsBtnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsBtnLoad.Click
    ds.Tables.Clear()

    If tsText.Text <> "" Then 'ToolStripTextBox
      Dim sql As String = "SELECT EXTENSIONS.EXTENSION, EXTENSIONS.DESCRIPTION, EXTENSIONS.EXECUTABLE_PATH " & _
                          "FROM(Extensions)" & _
                          "WHERE (((EXTENSIONS.EXTENSION)='" & tsText.Text & "'));"


      Dim cmd As New OleDbCommand(sql, conn) 'Used as the Select Command

      da.SelectCommand = cmd

      'Have to have this to be able to do Updates
      Dim cmdBuilder As New OleDbCommandBuilder(da)

      'Fille the dataSet
      da.Fill(ds, "EXTENSIONS")

      'Set tyhe BindingSource's DataSource
      bs.DataSource = ds.Tables(0)

    Else
      'Tell user they need to enter a search criteria
      Exit Sub
    End If

  End Sub

  Private Sub dgv1_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgv1.CellEndEdit
    edit = True
  End Sub

  ''' <summary>
  ''' Loads all the records to the DataGridView
  ''' </summary>
  ''' <param name="sender"></param>
  ''' <param name="e"></param>
  ''' <remarks></remarks>
  Private Sub btnLoadAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadAll.Click
    ds.Tables.Clear()

    Dim sql As String = "SELECT * FROM(Extensions);"

    Dim cmd As New OleDbCommand(sql, conn)

    da.SelectCommand = cmd
    Dim cmdBuilder As New OleDbCommandBuilder(da)
    da.Fill(ds, "EXTENSIONS")
    bs.DataSource = ds.Tables(0)

  End Sub