Binding so DB updates

Posted by: sheritt on 8 September 2017, 1:48 pm EST

  • Posted 8 September 2017, 1:48 pm EST

    Hi,


    I have a spread vs.3.2005.2010 and use VB 2008 as my development environment.  I am also using sqlclient and the Microsoft.ApplicationBlocks.Data (sqlhelper) to access my data.


    I have managed to bind the columns to the spread, but I can't get it to automatically update when a new entry is made.


    I use the following to bind to a record source.


    Private Sub LoadLocationData()


    Dim objData As New AppConfig


     


    Me.fpLocations.DataMember = "LocationsLU"


    Me.fpLocations.ActiveSheet.DataSource = objData.GetDataSetGrd("Select * from LocationsLU")


    Me.fpLocations.ActiveSheet.BindDataColumn(0, "LocationID")


    Me.fpLocations.ActiveSheet.BindDataColumn(1, "Location")


    End Sub


    Public Function GetDataSetGrd(ByVal strSQL As String) As DataSet


    Dim ds As New DataSet


    Try


    ds = SqlHelper.ExecuteDataset(AppConfig.ConnectionString, _


    CommandType.Text, strSQL)


    Return ds


    Exit Try


    Catch ex As Exception


    MessageBox.Show(ex.Message)


    Return Nothing


    Exit Function


    Finally


    If Not ds Is Nothing Then


    ds.Dispose()


    End If


    End Try


    End Function


    ########################


    I have been trying to figure it out myself, but being so new to .Net, I haven't had any luck.  I have found a lot of examples using the datagrid, but of course I wouldn't use any other type of grid except the spread... It is the BEST!!!


    Below is the latest mess I've tried in order to bind the data.


     


    Public Sub BindGrdData(ByVal fpGrd As FarPoint.Win.Spread.FpSpread)


    Dim ds As DataSet = fpGrd.ActiveSheet.DataSource


    'Dim sda As New SqlDataAdapter


    Dim s As String


    s = fpGrd.ActiveSheet.DataSource.ToString


    s = ds.ToString


    Try


    'ds = SqlHelper.UpdateDataset(ds, "LocationsLU")


    'sda.Update(ds, "LocationsLU")


    'SqlHelper.FillDataset(


    ds.GetChanges()


    ds.AcceptChanges()


     


    fpGrd.ActiveSheet.GetDataView(True)


    Catch ex As Exception


    MessageBox.Show(ex.ToString)


    End Try


    End Sub


     


    ###################################


    Your help would be greatly appreciated.


    Thank you,


    Sheri


     

  • Replied 8 September 2017, 1:48 pm EST

    Hello Sheri,


    Here is one of the approach.


    'declare global level data adapter.


    Dim da As SqlClient.SqlDataAdapter


    Dim ds As DataSet


    ' In the form load bind the spread to dataset


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


    Dim ds As DataSet = CreateDataSet(1)


    FpSpread1.DataSource = ds.Tables(0)


    FpSpread1.DataMember = ds.Tables(0).TableName


    End Sub


    ' Make call to your data base to generate the DataTable and attach the command builder to the Data Adapter


    Private Sub CreateDataSet()


    ds = New Data.DataSet()


    da = New SqlClient.SqlDataAdapter("Select * from Products", New SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"))


    Dim cmdBld As New SqlClient.SqlCommandBuilder(da)


    da.Fill(ds)


    End Sub


    ' call the DataAdapter.Update method to save the changes in the database


    da.Update(ds)


    Regards,

  • Replied 8 September 2017, 1:48 pm EST

    Hi Harish,


    I do have a dataset assigned to the datasource of the grid.  I also have the datamember and columns assigned.


    The data fills just fine, it's just when a new record is added it doesn't save.


    After the grid fills, I add an unbound row and then in the advance event, I generate a Guid, place it in a column and then bind the unbound row back to the datasource.


    Public Sub AddGrdUnboundRow(ByVal fGrd As FarPoint.Win.Spread.FpSpread)


    Dim r As Integer


    r = fGrd.ActiveSheet.ActiveRowIndex


    fGrd.ActiveSheet.AddUnboundRows(r + 1, 1)


     


    End Sub


    Public Sub BindRowToDataSet(ByVal fGrd As FarPoint.Win.Spread.FpSpread)


    Dim r As Integer


    Dim ac As New AppConfig


    Try


    r = fGrd.ActiveSheet.ActiveRowIndex


    fGrd.ActiveSheet.AddRowToDataSource(r, True)


    ac.BindGrdData(fGrd)


    Catch ex As Exception


    MessageBox.Show(ex.Message)


    Exit Sub


    End Try


    End Sub


     ############################


    I know I am missing something that makes it the info save back to the database, just not sure what.


    Thanks,


    Sheri

  • Replied 8 September 2017, 1:48 pm EST

    Do you specify the UpdateCommand? You may use the CommandBuilder object and attach it to the DataAdapter and then call the Update method of your DataAdapter to save the changes to your database. Please refer the modified code.


    Regads,

  • Replied 8 September 2017, 1:48 pm EST

    Well I give up on trying to bind data.  I've spent almost two weeks trying to figure out how to bind data and even changed my db from sql server to MS Access because there were more examples.


    It seems no matter what I have to write the insert, update etc statements.  I was hoping that it would be smart and do it for me.


    Oh well, still love the spread and will always keep using it.


    Thanks for trying,


    Sheri

  • Replied 8 September 2017, 1:48 pm EST

    Hello Sheri,


    Attached is the sample windows application showing how to implement the changes.


    I hope it helps.


    2009/03/FP_SaveChanges_AccessDB.rar
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels