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.

    <FONT size=2><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Private</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub</FONT></FONT><FONT size=2> LoadLocationData()


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> objData </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> AppConfig


     


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Me</FONT></FONT><FONT size=2>.fpLocations.DataMember = </FONT><FONT color=#a31515 size=2><FONT color=#a31515 size=2>"LocationsLU"

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Me</FONT></FONT><FONT size=2>.fpLocations.ActiveSheet.DataSource = objData.GetDataSetGrd(</FONT><FONT color=#a31515 size=2><FONT color=#a31515 size=2>"Select * from LocationsLU"</FONT></FONT><FONT size=2>)


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Me</FONT></FONT><FONT size=2>.fpLocations.ActiveSheet.BindDataColumn(0, </FONT><FONT color=#a31515 size=2><FONT color=#a31515 size=2>"LocationID"</FONT></FONT><FONT size=2>)


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Me</FONT></FONT><FONT size=2>.fpLocations.ActiveSheet.BindDataColumn(1, </FONT><FONT color=#a31515 size=2><FONT color=#a31515 size=2>"Location"</FONT></FONT><FONT size=2>)


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub</FONT></FONT>

    <FONT color=#0000ff size=2><FONT color=#0000ff size=2><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Public</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Function</FONT></FONT><FONT size=2> GetDataSetGrd(</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>ByVal</FONT></FONT><FONT size=2> strSQL </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>String</FONT></FONT><FONT size=2>) </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> DataSet


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> ds </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> DataSet


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Try

    </FONT></FONT><FONT size=2>

    ds = SqlHelper.ExecuteDataset(AppConfig.ConnectionString, _


    CommandType.Text, strSQL)


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Return</FONT></FONT><FONT size=2> ds


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Exit</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Try

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Catch</FONT></FONT><FONT size=2> ex </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> Exception


    MessageBox.Show(ex.Message)


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Return</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Nothing

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Exit</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Function

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Finally

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>If</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Not</FONT></FONT><FONT size=2> ds </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Is</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Nothing</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Then

    </FONT></FONT><FONT size=2>

    ds.Dispose()


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>If

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Try

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Function</FONT></FONT>


    <FONT color=#0000ff size=2><FONT color=#0000ff size=2>########################</FONT></FONT>


    <FONT color=#0000ff size=2><FONT color=#0000ff size=2>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!!!</FONT></FONT>


    <FONT color=#0000ff size=2><FONT color=#0000ff size=2>Below is the latest mess I've tried in order to bind the data.</FONT></FONT>


    <FONT color=#0000ff size=2><FONT color=#0000ff size=2><FONT size=2> 


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Public</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub</FONT></FONT><FONT size=2> BindGrdData(</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>ByVal</FONT></FONT><FONT size=2> fpGrd </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> FarPoint.Win.Spread.FpSpread)


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> ds </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> DataSet = fpGrd.ActiveSheet.DataSource


    </FONT><FONT color=#008000 size=2><FONT color=#008000 size=2>'Dim sda As New SqlDataAdapter

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> s </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>String

    </FONT></FONT><FONT size=2>

    s = fpGrd.ActiveSheet.DataSource.ToString


    s = ds.ToString


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Try

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#008000 size=2><FONT color=#008000 size=2>'ds = SqlHelper.UpdateDataset(ds, "LocationsLU")

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#008000 size=2><FONT color=#008000 size=2>'sda.Update(ds, "LocationsLU")

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#008000 size=2><FONT color=#008000 size=2>'SqlHelper.FillDataset(

    </FONT></FONT><FONT size=2>

    ds.GetChanges()


    ds.AcceptChanges()


     


    fpGrd.ActiveSheet.GetDataView(</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>True</FONT></FONT><FONT size=2>)


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Catch</FONT></FONT><FONT size=2> ex </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> Exception


    MessageBox.Show(ex.ToString)


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Try

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub</FONT></FONT>


    <FONT color=#0000ff size=2><FONT color=#0000ff size=2></FONT></FONT> 


    <FONT color=#0000ff size=2><FONT color=#0000ff size=2>###################################</FONT></FONT>


    <FONT color=#0000ff size=2><FONT color=#0000ff size=2>Your help would be greatly appreciated.</FONT></FONT>


    <FONT color=#0000ff size=2><FONT color=#0000ff size=2>Thank you,</FONT></FONT>


    <FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sheri

    </FONT></FONT></FONT></FONT>

    <FONT color=#0000ff size=2><FONT color=#0000ff size=2> 

    </FONT></FONT></FONT></FONT></FONT>
  • Replied 8 September 2017, 1:48 pm EST

    Hello Sheri,


    Here is one of the approach.

    <FONT color=#0000ff size=2><FONT color=#0000ff size=2><FONT size=2>

    </FONT><FONT color=#008000 size=2><FONT color=#008000 size=2>'declare global level data adapter.

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> da </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> SqlClient.SqlDataAdapter</FONT>

    <FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> ds </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> DataSet

    </FONT><FONT size=2><FONT size=2>

    </FONT><FONT color=#008000 size=2><FONT color=#008000 size=2>' In the form load bind the spread to dataset

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Private</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub</FONT></FONT><FONT size=2> Form1_Load(</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>ByVal</FONT></FONT><FONT size=2> sender </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> System.Object, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>ByVal</FONT></FONT><FONT size=2> e </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> System.EventArgs) </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Handles</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>MyBase</FONT></FONT><FONT size=2>.Load</FONT>

    <FONT size=2><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> ds </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> DataSet = CreateDataSet(1)


    FpSpread1.DataSource = ds.Tables(0)


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

    <FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub

    </FONT></FONT></FONT></FONT></FONT></FONT></FONT><FONT size=2><FONT size=2><FONT color=#0000ff size=2><FONT color=#0000ff size=2></FONT></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2><FONT size=2>

    </FONT><FONT color=#008000 size=2><FONT color=#008000 size=2>' Make call to your data base to generate the DataTable and attach the command builder to the Data Adapter

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Private</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub</FONT></FONT><FONT size=2> CreateDataSet()


    ds = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> Data.DataSet()


    da = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> SqlClient.SqlDataAdapter(</FONT><FONT color=#a31515 size=2><FONT color=#a31515 size=2>"Select * from Products"</FONT></FONT><FONT size=2>, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> SqlClient.SqlConnection(</FONT><FONT color=#a31515 size=2><FONT color=#a31515 size=2>"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"</FONT></FONT><FONT size=2>))


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> cmdBld </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> SqlClient.SqlCommandBuilder(da)


    da.Fill(ds)


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub</FONT></FONT>

    <FONT color=#0000ff size=2><FONT color=#0000ff size=2><FONT size=2>

    </FONT><FONT color=#008000 size=2><FONT color=#008000 size=2>' call the DataAdapter.Update method to save the changes in the database

    </FONT></FONT><FONT size=2>

    da.Update(ds)


    Regards,

    </FONT></FONT></FONT></FONT></FONT></FONT></FONT>
  • 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.

    <FONT color=#0000ff size=2><FONT color=#0000ff size=2>

    Public</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub</FONT></FONT><FONT size=2> AddGrdUnboundRow(</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>ByVal</FONT></FONT><FONT size=2> fGrd </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> FarPoint.Win.Spread.FpSpread)


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> r </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Integer

    </FONT></FONT><FONT size=2>

    r = fGrd.ActiveSheet.ActiveRowIndex


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


     


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Public</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub</FONT></FONT><FONT size=2> BindRowToDataSet(</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>ByVal</FONT></FONT><FONT size=2> fGrd </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> FarPoint.Win.Spread.FpSpread)


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> r </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Integer</FONT></FONT><FONT color=#008000 size=2><FONT color=#008000 size=2>

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> ac </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> AppConfig


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Try

    </FONT></FONT><FONT size=2>

    r = fGrd.ActiveSheet.ActiveRowIndex


    fGrd.ActiveSheet.AddRowToDataSource(r, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>True</FONT></FONT><FONT size=2>)


    ac.BindGrdData(fGrd)


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Catch</FONT></FONT><FONT size=2> ex </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> Exception


    MessageBox.Show(ex.Message)


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Exit</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Try

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub</FONT></FONT>


    <FONT color=#0000ff size=2><FONT color=#0000ff size=2> ############################</FONT></FONT>


    <FONT color=#0000ff></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>I know I am missing something that makes it the info save back to the database, just not sure what.</FONT></FONT>


    <FONT color=#0000ff size=2><FONT color=#0000ff size=2>Thanks,</FONT></FONT>


    <FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sheri

    </FONT></FONT>
  • 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