Binding a DataGrid to a ADO.RecordSet 2

[ktrang]

Hi, I'm having some problems binding a DataGrid control to a ADODB.RecordSet.

Here's my code:

Private Sub Form_Load()

Dim dbConnection As New ADODB.Connection

Set rsResults = New ADODB.Recordset

strQuery = "SELECT * FROM tblCategory INNER JOIN tblInventory ON tblCategory.categoryId = tblInventory.categoryId"

dbConnection.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Documents and Settings\k\My Documents\CrystalTest2\inventory.mdb"

dbConnection.Open

Set rsResults = dbConnection.Execute(strQuery)

Set DataGrid1.DataSource = rsResults
DataGrid1.Refresh

End Sub

I keep on getting a "rowset is not bookmarkable" error

Have no idea why this is happening. You should be able to bind the DataGrid to a Recordset and have it automatically filled? Right?

Thanks, Kiet

 

[Imhoteb]

Hi,

You can't bind a datagrid to a recordset that was made in code. If you want to bind this datagrid, you must put an ADO datacontrol on your form and bind your datagrid to it. This you can do in code.

Private myCon As New ADODB.Connection
Private myRS As New ADODB.Recordset

myRS.CursorLocation = adUseClient
myRS.CursorType = adOpenStatic
myCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\myDB.mdb"
myRS.Open "SELECT * From myTable", myCon, adOpenStatic, adLockPessimistic
myRS.Requery
Set adoDatagrid.Recordset = myRS 'adoDatagrid is the ADO datacontrol

adoDatagrid.CursorLocation = adUseClient
adoDatagrid.CursorType = adOpenStatic
adoDatagrid.Refresh
myRS.Close
myCon.Close

Hope this helps

 

[ktrang]

Thanks, I thought that you could bind the datagrid directly to a recordset created in code. The advice you gave worked.

Many Thanks.

 

[Trigeek]

Hi -

I used this sample for my own datagrid. I have both a data control and datagrid on my form - neither shows any records. The recordset is populated and bound to the data control (as far as I can tell).

Are there other steps that aren't listed here that I need, i.e. I haven't done any formatting at all - was hoping binding would handle that automatically.

Any suggestions are greatly appreciated!

Cheers-
Sheri

 

[CCLINT]

>"...You can't bind a datagrid to a recordset that was made in code.."

Imhoteb: "You forgot to say "I think you cannot.."

Under DAO you couldn't. You needed a datacontrol, (but you could still set the recordset property of the DC to a recordset object, like you did)

Setting the DataSource of the DataGrid directly to a ADO recordset is possible by simply coding:

Set DataGrid1.DataSource = rsAdo

RsAdo is a recordset object that has been opened with a client side cursor. Once doing this the Cursor type will be set to Static no matter what you previously set it to.

rsAdo.CursorLocation = adUseClient

This is Bookmarkable, so there shouldn't be any more problems.





[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!