[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

Update changes against db using axspreadsheet

Hi,
Im trying to provide end users to be able to manipuate with data using axspreadsheet.
I successfully loaded data from db into axspreadsheet but when I tried to update changes made by end user against db then nothing happened.
Can someone help me to find solution for the problem...
Code below....
Thanks in advance
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        AxSpreadsheet1.Worksheets(1).Cells.Clear()
        AxSpreadsheet1.Worksheets(1).Name = "MyWorksheet"
        AxSpreadsheet1.Worksheets(1).StandardWidth = 25
        Dim connect As New Data.SqlClient.SqlConnection(My.Settings.Connection)
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim k As Integer
        cmd.CommandText = "SELECT * FROM TreeViewItems"
        cmd.Connection = connect
        If connect.State = ConnectionState.Closed Then connect.Open()


        da = New Data.SqlClient.SqlDataAdapter(cmd)
        Dim commandBuilder As New Data.SqlClient.SqlCommandBuilder(da)
        da.InsertCommand = commandBuilder.GetInsertCommand()
        da.UpdateCommand = commandBuilder.GetUpdateCommand()
        da.DeleteCommand = commandBuilder.GetDeleteCommand()



        da.Fill(ds)

        ProgressBar1.Value = 0
        ProgressBar1.Maximum = ds.Tables(0).Rows.Count
        k = 1

        For ro As Integer = 0 To ds.Tables(0).Rows.Count - 1
            k += 1
            For cl As Integer = 0 To ds.Tables(0).Columns.Count - 1
                AxSpreadsheet1.Worksheets(1).Cells(k, cl + 1) =
                ds.Tables(0).Rows(ro).Item(cl).ToString
            Next
            ProgressBar1.Value += 1
        Next


    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.da.Update(ds.Tables(0))
        MsgBox("OK")
    End Sub
End Class

Open in new window

0
alsam
Asked:
alsam
  • 4
  • 3
1 Solution
 
CodeCruiserCommented:
Its because you are populating the speadsheet from the dataset but you are not writing it back to the dataset. The dataset is in same condition as it was when filled.
0
 
alsamAuthor Commented:
Hi,
Thank you for your reply....
Could you please help me to reorganize my code in order to make this working....
This writing back to dataset .... I'm not sure how to make this correctly as you suggested...
Please help and thank you once more...
0
 
CodeCruiserCommented:
You would need to reverese this loop

  For ro As Integer = 0 To ds.Tables(0).Rows.Count - 1
            k += 1
            For cl As Integer = 0 To ds.Tables(0).Columns.Count - 1
                   ds.Tables(0).Rows(ro).Item(cl) =      AxSpreadsheet1.Worksheets(1).Cells(k, cl + 1)
            Next
            ProgressBar1.Value += 1
        Next



so that the new values from spreadsheet are added to the dataset.

Can you not use a DataGridView?
0
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.

 
alsamAuthor Commented:
Hi,
Yes I can use DataGridView but for several reasons I have decided to use axspreadsheet. Why?
First, I'm trying to make application for controlling budgeting which is currently done in excel...
Somehow, I wanted to stay close to excel that users which make budget are very close to...
Secondly, axspreadsheet (as far as I'm concerned) gives nice possibility to make easy sum of data, data aligement, filter data etc which also can be done with DGV but in harder way for me to make working...

What is your opinion considering above...what would you prefer more in this case....
Maybe i'm wrong when I have decided to use axspreadsheet?!:)

0
 
CodeCruiserCommented:
I can not comment on your choice really. Depends on how resistent to change your users are. DataGridView would make your life easier though.
0
 
alsamAuthor Commented:
Anyway,
thank you once more for your help...
0
 
CodeCruiserCommented:
Glad to help :-)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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