• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 489
  • 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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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