ADO Data connections

[jfarrell]

If anyone can shed some light on this I would appreciate it.
I'm new to ADO.
I have an Access 97 Database with several tables.
Student table, test table, in a one to many
relationships.
When I click on a DataList control that is linked up to Adodc1
with the recordsource Student, I am try to sysnchronize this
with a data grid that is linked to the test table (Adodc7).
I can't get them to be in sync. Clicking on a
student will not show the students test data.
The field studentid is the common field.
Sample of my code:
Private Sub Adodc1_MoveComplete(....
Dim strSQL as string
strSQL = "Select * From Tests where Tests.Studentid = "Adodc1.Recordset.Studentid
Adodc7.RecordSource = strSQL
Adodc7.refresh
end sub

I'm doing something wrong but I haven't found the answer.
Any suggestions.

Thanks
Jack

 

[ToeShot]

Try the code below with the changes. What you needed to do was A)is Point to the field Index and not the name of the field, this way it takes your current selected record and uses the field that the index marked. B) you need to convert your criteria to a string and concactenate it with the sql string. One note I tested this with MySql but the results should be the same.
HTH

Private Sub Adodc1_MoveComplete(....
Dim ind
Dim strSQL as string

ind = datPrimaryRS.Recordset.Fields(0)
strSQL = "Select * From Tests where Tests.Studentid = " & CStr(ind)
Adodc7.RecordSource = strSQL
Adodc7.refresh
end sub

 

[parul8]

Hello there,
Instead of using the second recordset statement in the first
(tests.studentid=adodc1.studentid) wh don't you try creating a command object with the following code:
cmdstudent.commandtext=adcmdprocedure
//then in your procedure you cans specify the list item's //index no using the following:
create proc prc1(@stuid char(//whatever size))
as
select * from student where studentid=@stuid
return
//you can do this in your adodc properties, instead of using a table, you can use SQL statement.
//for the procedure to run, you can use the data environment to add a command object which will return the recordset to you, and you can bind your datagrid on yourvb form to your command object using adodc.
I hope you find it helpful.
bye
parul