none
Update Access Database from datagridview vb 2010 RRS feed

  • Question

  • Hi,
    I have data in Access Database which I am filling in the DataGridView to QA/QC raw data downloaded from a climate station. I am having trouble updating the database using update command. I am using following steps:
    1. Locating access database:
    Private Sub AccessDataFileButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AccessDataFileButton.Click
    OpenFileDialog2.Filter = "Microsoft Access File(*.mdb)|*.mdb|Microsoft Access File(*.accdb)|*.accdb|All files (*.*)|*.*"
    OpenFileDialog2.Title = "Input Data File with Real-time Water Quality Time Series"
    If OpenFileDialog2.ShowDialog = DialogResult.OK Then
    accessDataFile = OpenFileDialog2.FileName
    'MsgBox("accessFile= " & OpenFileDialog2.FileName)
    Me.AccessFileTextBox.Text = accessDataFile

    End If

    End Sub

    2. Filling DataGridView:
    Sub fillDataGrid1()
    ' Read Access Table to find date and time of the last data
    Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & accessDataFile
    Dim myConnection As OleDbConnection = New OleDbConnection
    myConnection.ConnectionString = connString
    myConnection.Open()

    Dim inpTable As String = "rawClimateData" 'Access database table
    Dim cmd As String = "Select * from " & inpTable '& " ORDER BY mdateTime"
    Dim dbCmd As OleDbCommand = New OleDbCommand(cmd, myConnection)

    ' create a data adapter 
    Try
    da = New OleDbDataAdapter(cmd, myConnection)
    Catch ex As Exception ' Catch the error.
    MsgBox(ex.ToString) ' Show friendly error message.
    End Try

    ' create a new dataset 
    Dim table As New DataTable
    myDataset = New DataSet()
    Try
    Me.da.Fill(table)
    da.Fill(myDataset, inpTable)
    Me.DataGridView1.DataSource = table
    Catch ex As Exception
    MsgBox(ex.ToString)
    End Try
    myConnection.Close()

    End Sub
    3. Updating database (update button)
    Private Sub TryTry_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles upDate.Click

    Try

    Me.da.Update(Me.myDataset.Tables("rawClimateData"))
    Me.myDataset.AcceptChanges()

    Catch ex As Exception
    MsgBox(ex.Message)
    End Try
    MsgBox("upDateComplete")
    End Sub

    In some examples I have seen connecting database file while Form is loading; however, I have an option of browsing the access database file.

    I know it should be simple, however, I am not a pro. Therefore, any tips/help would be greatly appreciated.

    Cheers,

    Amanjot
    Thursday, February 2, 2012 12:51 AM

Answers

All replies