I've got a bit of a problem with an application I am writing at the moment. I know it has to be something small which I'm missing, but I can't figure out what it is.
Basically, I have a Query (or Stored Procedure if you prefer) in an Access2000 database called qryApptList which has one parameter (DateOfAppt). This parameter is Text (as is the Date field in the database table).
If I run the query in Access, it prompts for the Date. I enter it in and it returns my results as it should. If I then do the same thing in VB6 using ADODB, it brings back nothing.
My VB6 code...
Private m_oConnection As ADODB.Connection Private m_oCmdGetAppointments As ADODB.Command 'Open the database connection Public Sub OpenDatabaseConnection() On Error GoTo ErrorHandler If Len(m_sDatabasePath) = 0 Then m_sDatabasePath = m_oParentForm.FileSelection End If Set m_oConnection = New ADODB.Connection m_oConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & m_sDatabasePath If m_oConnection.State <> adStateOpen Then Set m_oConnection = Nothing MsgBox "Could not open connection to database", vbCritical + vbOKOnly, App.Title Exit Sub End If Exit Sub ErrorHandler: HandleError "clsDatabaseHandler:OpenDatabaseConnection Sub" End Sub 'Method to get the appointments from the database Public Function GetAppointments(ByVal sDate As String, ByRef oRsAppts As ADODB.Recordset) On Error GoTo ErrorHandler If m_oCmdGetAppointments Is Nothing Then Set m_oCmdGetAppointments = New ADODB.Command With m_oCmdGetAppointments Set .ActiveConnection = m_oConnection .CommandText = "qryApptList" .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter("DateOfAppt", adVarChar, adParamInput, 11) End With End If m_oCmdGetAppointments.Parameters("DateOfAppt") = sDate Set oRsAppts = New ADODB.Recordset oRsAppts.Open m_oCmdGetAppointments, , adOpenStatic, adLockReadOnly Exit Function ErrorHandler: HandleError "clsDatabaseHandler:GetAppointments Function" End Function
All this code runs within a separate class module which is called from a form.
And if it helps... my SQL query.
PARAMETERS DateOfAppt Text ( 255 ); SELECT tblAppointments.ApptId, tblAppointments.User, tblAppointments.ApptDate, tblAppointments.ApptTime, tblAppointments.ClientId, tblAppointments.Comments, tblClients.Title, tblClients.FirstName, tblClients.Surname, tblClients.ContactNumber1 FROM tblClients INNER JOIN tblAppointments ON tblClients.ClientId=tblAppointments.ClientId WHERE tblAppointments.ApptDate=[DateOfAppt];
I have had it working once and I had a recordcount of 1 in the recordset (I only have one record in the table by the way). I haven't knowingly changed anything but now I get 0 records every time.
Any help would be massively appreciated as I'm tearing out what little hair I have left!