binding to a recordset class?

Posted by: ginacresse on 8 September 2017, 1:07 pm EST

  • Posted 8 September 2017, 1:07 pm EST

    Hi,


    I'm trying my first databinding project but not having much success.  I want to bind a spread to the values in a class object (ado recordset) then have a child spread that displays only data from a child table relevent to the row the user selects in the parent spread.  I have not been able to get the parent spread populated.  The following code holds the settings for my recordset:

    <FONT size=2>

    </FONT><FONT color=#0000ff size=2>Function</FONT><FONT size=2> dbOpen(</FONT><FONT color=#0000ff size=2>ByVal</FONT><FONT size=2> sSql </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>String</FONT><FONT size=2>) </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>Object

    </FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2>Dim</FONT><FONT size=2> RS </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> ADODB.Recordset


    <BLOCKQUOTE dir=ltr>

    cnn.IsolationLevel = ADODB.IsolationLevelEnum.adXactCursorStability


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


    <BLOCKQUOTE dir=ltr>

    .let_ActiveConnection(cnn)


    .CursorLocation = ADODB.CursorLocationEnum.adUseClient


    .CursorType = ADODB.CursorTypeEnum.adOpenStatic


    .LockType = ADODB.LockTypeEnum.adLockReadOnly


    </FONT><FONT color=#0000ff size=2>If</FONT><FONT size=2> .State = ADODB.ObjectStateEnum.adStateOpen </FONT><FONT color=#0000ff size=2>Then

    </FONT><FONT size=2>
    <BLOCKQUOTE dir=ltr>

    .Close()

    </BLOCKQUOTE>

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

    </FONT><FONT size=2>

    .Open(sSql, , , , ADODB.CommandTypeEnum.adCmdText)


    dbOpen = RS


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

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

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

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

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


    <FONT color=#0000ff size=2><FONT color=#000000 size=3>The following code is supposed to fill my spread (I thought), but nothing at all happens.  What am I missing?:</FONT></FONT>

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

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


    </FONT><FONT color=#0000ff size=2>Dim</FONT><FONT size=2> tmpVineyardDetails </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> cVineyards


    <BLOCKQUOTE dir=ltr>

    tmpVineyardDetails = tmpVineyardDetails.SearchDetail(</FONT><FONT color=#0000ff size=2>Nothing</FONT><FONT size=2>, </FONT><FONT color=#0000ff size=2>Me</FONT><FONT size=2>.txtKey.Text, </FONT><FONT color=#0000ff size=2>Nothing</FONT><FONT size=2>)


    </FONT><FONT size=2>fpsParent.DataSource = tmpVineyardDetails


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

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

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


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

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

    Gina -

    You posted this in the COM section but it looks like you are using the .Net version.  I would suggest instead of using a RecordSet object(which I'm not sure is supported since spread needs to be bound to a .Net data object) that you use a SqlDataAdapter and a DataSet to set up your binding.
  • Replied 8 September 2017, 1:07 pm EST

    Sorry for posting in the wrong forum.  Yes, I'm using .Net, converting a rather large application that is heavily dependent on RecordSets created in a back-end BusinessObject dll.  In this conversion, I was hoping not to have to re-invent that BusinessObject, so it sounds like DataBinding is not an option for me at this time.  If I'm not using databinding, is it still possible to create parent/child spreads?
  • Replied 8 September 2017, 1:07 pm EST

    Here's an example from our samples that binds the spread to a recordset...

    Set adoconn1 = New ADODB.Connection
        adoconn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & str & ";Persist Security Info=False"
       
        Set rsMaster = New ADODB.Recordset
        rsMaster.Open "Select * from Orders", adoconn1, adOpenStatic, adLockOptimistic
        Set FpSpread1.DataSource = rsMaster
  • Replied 8 September 2017, 1:07 pm EST

    Do you see any text in the headers?  If you do (and no data), then the recordset needs to be bookmarkable.  If nothing shows at all, you could try binding this to the vb grid and see if it is blank as well (it might give you some sort of error code).
  • Replied 8 September 2017, 1:07 pm EST

    Hi Bob,


    Below is the entire project.  When I set the .DataSource, the RS.RecordCount is 146 but nothing (not even headers) show on my spread.  I must be missing something.

    <FONT color=#0000ff size=2>

    Imports</FONT><FONT size=2> System.Data.OleDb

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

    Public</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>Class</FONT><FONT size=2> Form1


    </FONT><FONT color=#0000ff size=2>Public</FONT><FONT size=2> cnn </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> ADODB.Connection


     


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


    </FONT><FONT color=#0000ff size=2>If</FONT><FONT size=2> Len(txtKey.Text) > 0 </FONT><FONT color=#0000ff size=2>Then

    </FONT><FONT size=2>

    FillParent()


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

    </FONT><FONT size=2>

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

    </FONT><FONT size=2>

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


    </FONT><FONT color=#0000ff size=2>Dim</FONT><FONT size=2> RS </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> ADODB.Recordset


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


    .let_ActiveConnection(cnn)


    .CursorLocation = ADODB.CursorLocationEnum.adUseClient


    .CursorType = ADODB.CursorTypeEnum.adOpenStatic


    .LockType = ADODB.LockTypeEnum.adLockOptimistic


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


     

    </FONT><FONT size=2>

    RS.Open(</FONT><FONT color=#a31515 size=2>"Select * from VineyardDetail where VidVineyardID = '"</FONT><FONT size=2> & </FONT><FONT color=#0000ff size=2>Me</FONT><FONT size=2>.txtKey.Text & </FONT><FONT color=#a31515 size=2>"'"</FONT><FONT size=2>)


     


    </FONT><FONT color=#0000ff size=2>Me</FONT><FONT size=2>.fpsParent.DataSource = RS


     


     


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


     

    </FONT><FONT size=2>

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


    dbLogin()


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

    </FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2>Function</FONT><FONT size=2> dbLogin() </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>Boolean

    </FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2>Dim</FONT><FONT size=2> dbServer </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>String</FONT><FONT size=2> = </FONT><FONT color=#a31515 size=2>"dv0s"

    </FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2>Dim</FONT><FONT size=2> dbms </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>String</FONT><FONT size=2> = </FONT><FONT color=#a31515 size=2>"dv_wrp_test"

    </FONT><FONT size=2>

    cnn = </FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> ADODB.Connection


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


    .ConnectionString = </FONT><FONT color=#a31515 size=2>"Provider=SQLOLEDB;Network Library=DBMSSOCN;Trusted_Connection=yes;"</FONT><FONT size=2> & </FONT><FONT color=#a31515 size=2>"server="</FONT><FONT size=2> & dbServer & </FONT><FONT color=#a31515 size=2>";database="</FONT><FONT size=2> & dbms & </FONT><FONT color=#a31515 size=2>""

    </FONT><FONT size=2>

    .ConnectionTimeout = 60


    .CommandTimeout = 0


    .IsolationLevel = ADODB.IsolationLevelEnum.adXactCursorStability


    .CursorLocation = ADODB.CursorLocationEnum.adUseClient


    .Open()


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

    </FONT><FONT size=2>

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


     

    </FONT><FONT size=2>

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


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

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

    Hello,


    What Spread product are you using exactly. The Spread COM version has the ability to bind to ADO Recordset object, but the .NET version (Spread for Windows Version does not). You would bind the .NET Spread control to a DataSet or DataTable object.

  • Replied 8 September 2017, 1:07 pm EST

    Sorry for the confusion.  I'm using the .Net version (Farpoint Spread 3.0) but I started this post in the wrong forum.
  • Replied 8 September 2017, 1:07 pm EST

    Hello,


    With Spread for Windows Forms, you can not bind to the same RecordSet object you would with the COM version of Spread. You would need to bind to a DataTable/DataSet object.

Need extra support?

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

Learn More

Forum Channels