Solved

Update changes against db using axspreadsheet

Posted on 2011-03-01
7
460 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
  • 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now