Solved

Update changes against db using axspreadsheet

Posted on 2011-03-01
7
470 Views
Last Modified: 2012-05-11
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
Comment
Question by:alsam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35021018
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
 
LVL 1

Author Comment

by:alsam
ID: 35021329
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
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 35021354
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
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!

 
LVL 1

Author Comment

by:alsam
ID: 35021647
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35021722
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
 
LVL 1

Author Closing Comment

by:alsam
ID: 35021744
Anyway,
thank you once more for your help...
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35021971
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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question