?
Solved

Update changes against db using axspreadsheet

Posted on 2011-03-01
7
Medium Priority
?
478 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 2000 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month8 days, 15 hours left to enroll

764 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