2 Replies - 6277 Views - Last Post: 25 October 2010 - 11:38 AM Rate Topic: -----

#1 shaunfish  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 23-October 10

How do I update access database with data from unbound datagridview

Posted 23 October 2010 - 12:13 AM

Hi all
I have a problem that is bugging the hell out of me.....

I need to know how to update an access database from an unbound datagridview using vb.net 2008 here is my code but it dont want to work Please, please help. I need the simplest way.

dim con As New OleDb.OleDbConnection
dim ds As New DataSet
dim da As New OleDb.OleDbDataAdapter
dim intCount as integer

con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=c:\marc\marc.mdb"

intCount = dgFam.Rows.Count
a = 0
Dim dgr1 As DataGridViewRow
For Each dgr1 In Me.dgFam.Rows

sql = "update FamMembers set callsign = '" & txtCallsign.Text.ToUpper & "', FamMember = '" & dgFam.Rows(a).Cells(0).Value & "', Relationship = '" & dgFam.Rows(a).Cells(1).Value & "', DOB = '" & dgFam.Rows(a).Cells(2).Value & "' where Callsign = '" & txtCallsign.Text & "'"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "Fam")
a = a + 1
Next
con.Close()

Thanks
Shaun

Is This A Good Question/Topic? 0
  • +

Replies To: How do I update access database with data from unbound datagridview

#2 shaunfish  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 23-October 10

Re: How do I update access database with data from unbound datagridview

Posted 23 October 2010 - 12:21 AM

Sorry I posted the wrong code here is the correct code
'Data Variables
dim con As New OleDb.OleDbConnection
dim ds As New DataSet
dim da As New OleDb.OleDbDataAdapter
dim intCount as integer
dim a as integer

intCount = dgAwards.Rows.Count
a = 0
While a < intCount
sql = "update Awards set callsign = '" & txtCallsign.Text.ToUpper & "', Award = '" & dgAwards.Rows(a).Cells(0).Value & "', AwDate = '" & dgAwards.Rows(a).Cells(1).Value & "' where callsign = '" & txtCallsign.Text & "'"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "Award")
a = a + 1
End While
con.Close()
Was This Post Helpful? 0
  • +
  • -

#3 demausdauth  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 177
  • View blog
  • Posts: 641
  • Joined: 03-February 10

Re: How do I update access database with data from unbound datagridview

Posted 25 October 2010 - 11:38 AM

Make a new class to hold datalayer access items -- in this case I am going to call it GlobalStuff.

Option Strict On

Imports System.Data
Imports System.Data.OleDb

Public Class GlobalStuff
    Public Shared Function ExecuteSql(ByVal sqlStatement As String, ByVal connectionString As String) As Boolean

        If Not String.IsNullOrEmpty(sqlStatement) Then

            Try

                Using oleConnection As New OleDbConnection(connectionString)
                    Using oleCommand As New OleDbCommand(sqlStatement, oleConnection)

                        'make sure the connection is open
                        If Not oleConnection.State = ConnectionState.Open Then oleConnection.Open()

                        Dim recordsAffected As Integer = -1

                        'The ExecuteNonQuery method will perform Update,Insert, and Delete statements. If 
                        ' the statements execute, then the number of records that were affected by the
                        ' statement is returned. 
                        recordsAffected = oleCommand.ExecuteNonQuery()

                        'statement has been executed and we can return that it executed successfully
                        Return True

                    End Using 'end of the command object
                End Using 'end of the connection object

            Catch ex As OleDbException
                MessageBox.Show(ex.Message, "OleDbException:ExecuteSql")

            Catch ex As Exception
                MessageBox.Show(ex.Message, "Exception:ExecuteSql")

            End Try
        Else
            MessageBox.Show("SQL Statement cannot be blank.", "SQLStatement:ExecuteSql")
        End If
        Return False
    End Function
End Class



So how do you use this function? In your code you where ever you want to execute a SQL statement other than select...for example an update button click event

Private Sub btnUpdate_Click(Byval sender As Object, Byval e As EventArgs)
     Dim updateStatement as String = "update Awards set callsign = '" & txtCallsign.Text.ToUpper & "', Award = '" & dgAwards.Rows(a).Cells(0).Value & "', AwDate = '" & dgAwards.Rows(a).Cells(1).Value & "' where callsign = '" & txtCallsign.Text & "'"
     
    
     If GlobalStuff.ExecuteSql(updateStatement, "YourConnectionString") Then
          'update executed just fine
     Else
          'error
     End If
    
End Sub



I can't go any farther because I don't think you should loop through the datagridview rows, I would loop through the DataSource instead and perform the updates. It will really depend on how you bound the data to the datagridview.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1