ADO.net
-
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 Fo
-
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 Fo