Recordset with DataGrid problem

Kadenn

I'm working in VBA and I haven't been able to figure this one out.
The following code works fine until the form opens. The form does show all the rows involved because when I change the SQLString the number of rows displayed changes. BUT....there is no data in the cells. I've also tried with an Adodc but I might not have been doing it correctly.

Set pAdocon = New ADODB.Connection

strConn = "Provider=MSDAORA;Data source=datasource;" & _
"User ID=user;Password=password"

pAdocon.Open strConn

Set pAdoRS = New ADODB.Recordset
Set pAdoRS.CursorLocation = adUseClient
Set pAdoRS.CursorType = adOpenStatic
Set pAdoRS.LockType = adLockOptimistic

Dim pSQLString As String
pSQLString = "SELECT...STATEMENT"
pAdoRS.Open pSQLString, pAdocon, , , adCmdText

DataGrid1.Columns.Item(0).Caption = pAdoRS.Fields(0).Name

'MsgBox pAdoRS.RecordCount
'******count confirms that recordset contains records

'******Datagrid show the same number of rows as the record count
'******but displays no values
Set DataGrid1.DataSource = pAdoRS
DataGrid1.Refresh

GideonOmega

is their anywhere in your code that you are closing or setting your recordset to nothing? that will purdge all of the data in the data grid. if you want to make a disconected recordset all you need to do is set the [code] set rs.activeConnection = nothing[/code]
then you can still use it as the data source for your grid without having to worry about it being connected to your db.



: I'm working in VBA and I haven't been able to figure this one out.
: The following code works fine until the form opens. The form does show all the rows involved because when I change the SQLString the number of rows displayed changes. BUT....there is no data in the cells. I've also tried with an Adodc but I might not have been doing it correctly.
:
: Set pAdocon = New ADODB.Connection
:
: strConn = "Provider=MSDAORA;Data source=datasource;" & _
: "User ID=user;Password=password"
:
: pAdocon.Open strConn
:
: Set pAdoRS = New ADODB.Recordset
: Set pAdoRS.CursorLocation = adUseClient
: Set pAdoRS.CursorType = adOpenStatic
: Set pAdoRS.LockType = adLockOptimistic
:
: Dim pSQLString As String
: pSQLString = "SELECT...STATEMENT"
: pAdoRS.Open pSQLString, pAdocon, , , adCmdText
:
: DataGrid1.Columns.Item(0).Caption = pAdoRS.Fields(0).Name
:
: 'MsgBox pAdoRS.RecordCount
: '******count confirms that recordset contains records
:
: '******Datagrid show the same number of rows as the record count
: '******but displays no values
: Set DataGrid1.DataSource = pAdoRS
: DataGrid1.Refresh
:

[blue]
C:Dos
C:Dos Run
Run Dos Run
[/blue]

GideonOmega

One other thought I had -- you mentioned it works fine until the form opens... does that mean you are setting the code elsewhere?

you want to make sure your recordset and connection object arn't going out of scope otherwise they will automaticly be nothing. for instance you should set them up in a code modual as public if you want to use them across multiple forms.

[code]
'in code modual general declarations
Public myCon as ADODB.Connection
Public myRS as ADODB.RecordSet
[/code]

for form specific ado make sure you put them in the general declarations so that they don't lose scope when the form load is finished.

if you are only setting and calling values for the objects from the form in question then you can set them as private in the general declarations.
[code]
Public myCon as ADODB.Connection
Public myRS as ADODB.RecordSet
[/code]

other wise if you plan to set the values from another form before the load set them as public in the general declarations of the form so that you can access them from another form as follows:
[code]
'from frmA
with frmB
.myConn.connectionstring ="code here"
.myConn.Open
'code for recordset here
...
...
.show
end with
[/code]

trying to use a recordset/Connection object outside of scope will produce the same results as closing the objects.
[blue]
C:Dos
C:Dos Run
Run Dos Run
[/blue]

Kadenn

Thanks.....both suggestions worked. I wasn't passing the variable and I was closing the connection. Thanks again!!