• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

VB.net Excel Add-in Editing adding records to Access/SQL in Excel sheet

Hi.
In my VB.net add-in I use the following code to edit a table in Access or SQL using a DataGridView. I need to do something similar where I load the data


    Public oAccessAdapter_Accounts As OleDbDataAdapter
    Public oAccessTable_Accounts As New DataTable
    Public oSQLAdapter_Accounts As SqlDataAdapter
    Public oSQLTable_Accounts As New DataTable

    Private Sub Tasks_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Call Fill_DGV()
    End Sub

    Sub Fill_DGV()
        Try
            Dim sSQL As String

            sSQL = "Select * From Accounting"
            If My.Settings.DBType = "Access" Then
                Dim connection As New OleDbConnection(My.Settings.CS_Setting)
                oAccessAdapter_Accounts = New OleDbDataAdapter(sSQL, connection)
                oAccessAdapter_Accounts.Fill(oAccessTable_Accounts)
                Me.DataGridView1.DataSource = oAccessTable_Accounts
            ElseIf My.Settings.DBType = "SQL" Then
                Dim connection As New SqlConnection(My.Settings.CS_Setting)
                oSQLAdapter_Accounts = New SqlDataAdapter(sSQL, connection)
                oSQLAdapter_Accounts.Fill(oSQLTable_Accounts)
                Me.DataGridView1.DataSource = oSQLTable_Accounts
            End If
            Me.DataGridView1.Columns(0).Width = 0 'hide ID Column as this musn't be opened
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

  Private Sub Button_Save_Click(sender As System.Object, e As System.EventArgs) Handles Button_Save.Click
        Try
            If My.Settings.DBType = "Access" Then
                Dim builder As New OleDbCommandBuilder(oAccessAdapter_Accounts)
                builder.QuotePrefix = "["
                builder.QuoteSuffix = "]"
                oAccessAdapter_Accounts.Update(oAccessTable_Accounts)
                oAccessAdapter_Accounts.UpdateCommand = builder.GetUpdateCommand()
            ElseIf My.Settings.DBType = "SQL" Then
                Dim builder As New SqlCommandBuilder(oSQLAdapter_Accounts)
                builder.QuotePrefix = "["
                builder.QuoteSuffix = "]"
                oSQLAdapter_Accounts.Update(oSQLTable_Accounts)
                oSQLAdapter_Accounts.UpdateCommand = builder.GetUpdateCommand()
            End If
        Catch ex As Exception
            MsgBox(Err.Description)
        End Try
    End Sub

End Class
0
Murray Brown
Asked:
Murray Brown
  • 3
  • 3
1 Solution
 
CodeCruiserCommented:
>I need to do something similar where I load the data

Not sure what the question is.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi

Put simply. I am trying to use an Excel sheet  to dynbamically update data in a SQL database.
I use the code above to pull a table into a DataGridView. The user can change any cell value and click "Save" and the table will be updated with these changes.
Doing this in Excel seems more challenging. I want to pull a table into a Sheet - allow the user to change any cells and then save the changes, Is there similar code for this as above or do I use techniques?
0
 
CodeCruiserCommented:
How far have you got with the above code? Is it working?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
The above code works fine for a DataGridView - I want code that will achieve this using a spreadsheet instead
0
 
CodeCruiserCommented:
Ah ok.You would need to fill a datatable same as you are doing. Then loop through it and copy it to cells in active sheet. When saving, you would need to do the reverse and shift from sheet cells to datatable and then call the update on adapter as you are doing.


>Then loop through it and copy it to cells in active sheet.

That would be easier as you just loop through all rows in datatable and copy to sheet by keep current row index.

>When saving, you would need to do the reverse and shift from sheet cells to datatable

For this, you would need to either loop through rows in sheet and try and match on a primary key field to see if the row is added or modified or you would just clear the datatable and copy all rows from sheet. This bit will be complex as if you do the later approach, all rows in datatable will be new and will be inserted into database.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Great. Really appreciate the help on this
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now