Member Avatar for anithasalavadi

can someone help me with the follwing

i can able display the ms access table in datagridview control of vb 2005 now i want to update the table values from using ms access 2007. and by using oledb connection statements i hv connected the access database


do u have any suggestions that how can i update the ms access table ..

  • 2 Contributors
  • 3 Replies
  • 109 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by Ramy Mahrous

Dani AI

Generated

Short answer: identify the record by its primary key, load that row's values into the textboxes, then run a parameterized UPDATE using that key. is correct that the key is the anchor; below are practical VB.NET patterns for selecting the row from a DataGridView and performing the update against an Access 2007 (.accdb) file.

Use the DataGridView CellClick (or SelectionChanged) event to pull the ID and fill the textboxes (handles bound and unbound grids):

Private Sub dgvRecords_CellClick(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs) Handles dgvRecords.CellClick
    If e.RowIndex < 0 Then Exit Sub
    Dim row As DataGridViewRow = dgvRecords.Rows(e.RowIndex)
    Dim idObj As Object = row.Cells("ID").Value
    If idObj Is Nothing OrElse Convert.IsDBNull(idObj) Then Exit Sub
    txtID.Text = idObj.ToString()

    Dim val As Object = row.Cells("Name").Value
    If val IsNot Nothing AndAlso Not Convert.IsDBNull(val) Then
        txtName.Text = val.ToString()
    Else
        txtName.Text = String.Empty
    End If

    ' repeat for other fields...
End Sub

When the edit is confirmed, run a parameterized UPDATE. OleDb for Access uses positional parameters (question marks), so parameter order matters:

Private Sub btnUpdate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnUpdate.Click
    Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\yourdb.accdb;Persist Security Info=False;"
    Using cn As New OleDbConnection(connString)
        Using cmd As New OleDbCommand("UPDATE MyTable SET Name = ?, Email = ? WHERE ID = ?", cn)
            cmd.Parameters.AddWithValue("Name", txtName.Text)
            cmd.Parameters.AddWithValue("Email", txtEmail.Text)
            Dim idVal As Integer
            If Integer.TryParse(txtID.Text, idVal) Then
                cmd.Parameters.AddWithValue("ID", idVal)
                cn.Open()
                cmd.ExecuteNonQuery()
            End If
        End Using
    End Using
    ' Refresh the DataGridView datasource (re-Fill the DataTable)
End Sub

Troubleshooting and notes: ensure the table has a primary key; if the grid is data-bound prefer changing the DataTable and calling DataAdapter.Update (or use OleDbCommandBuilder if appropriate). Watch parameter order with OleDb, watch data types (use explicit OleDbType if AddWithValue causes issues), and on 64-bit machines use the correct ACE provider or compile as x86.

Recommended Answers

All 3 Replies

Member Avatar for Ramy Mahrous

Like you insert or retrieve the data, just change the SQL command, read in OleCommand

Member Avatar for anithasalavadi

after populating the textboxes with d old values we can modify and wil do updation query, that is fine .. but before this how can i select the particular record to update and to populate the old values in the textboxes.. that is wat my pblm,

can sum1 help me out

Member Avatar for Ramy Mahrous

First you have ID for the row, get the data using the ID, then update the data using the ID... so simple!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.