No resultset return problem

[MichaelaLee]

Hi All,
I'm having a slight problem trying to get a resultset back from an Access 2000 database. I have the following code:
SQL Query:
PARAMETERS [lStudentId] Long;
SELECT StudentCourses.CourseID, Courses.CourseTitle, StudentCourses.StudentID, StudentCourses.StartDate, StudentCourses.CompletionDate, Courses.CourseDescription, Courses.CourseCredits, Courses.Section
FROM StudentCourses INNER JOIN Courses ON StudentCourses.CourseID = Courses.CourseID
WHERE StudentCourses.StudentID=[lStudentId];

VB Code:
If rsAssigned.State <> adStateClosed Then
rsAssigned.Close
End If
Set mCommand = New ADODB.Command
mCommand.CommandType = adCmdUnknown
mCommand.CommandText = "GetAssignedByStudentId"
Set mParam = mCommand.CreateParameter("lStudentId", adInteger, adParamInput)
mParam.Value = StudentId
mCommand.Parameters.Append mParam
mCommand.ActiveConnection = ado
rsAssigned.CursorType = adOpenKeyset
rsAssigned.LockType = adLockOptimistic
Set rsAssigned = mCommand.Execute

A Note about the Execute command. When the last line is run, the CursorType and LockType change to:
CursorType = 0 (adOpenForwardOnly)
LockType = 1 (adLockPessimistic)

What do you all think. Thanks for any help.
Michael

 

[MichaelaLee]

Just a short note on above question above. I do have data in the database that should be returned with the studentid, and yet after the execute method is run, I get a recordcount of -1. Thanks again.
Michael

 

[ArtieChoke]

[green]CursorType = 0 (adOpenForwardOnly)[/green] will not return the record count. Use the EOF property to see if you have any data.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[fredericofonseca]

rsAssigned.open mCommand.Execute,,adOpenKeyset,adLockOptimistic

will allow you to set those properties on the recordset.

not sure if these two should be reversed
mParam.Value = StudentId
mCommand.Parameters.Append mParam

I do like
' mCommand.Parameters.Append mCommand.CreateParameter("lStudentId", adInteger, adParamInput, lenght_of_student_id_field)

also note that you did not refer to the size of the parameter field when you create the parameter.
ALWAYS do that, as it may not work if you do not specify that.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com